October 12, 2004 at 12:12 pm
Here is the situation. I have two SQL servers, one local(server A) and one remote(server B). I do not have any control over server B, but have full control over server A. Server B contains most of the information I need to make my application work. The application is an ASP web application. I have set up linked servers and can view all the tables on the linked server in EM. When logging in on the QA with windows authentication I can query the view and the linked servers without a problem. I have a login on server A which was created specifically for use with this application. On the properties for the linked server, I have linked chosen every option possible. I have mapped the login on Server A to the login for Server B since they are different. So I would assume that a query executed by the login on Server A via the QA does not work and give the following error:
Server: Msg 7416, Level 16, State 2, Procedure test, Line 2
Access to the remote server is denied because no login-mapping exists.
If I run the same query via ASP it gives a similar error:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
Access to the remote server is denied because no login-mapping exists.
Like I said I have chosen every option including Be made using this security context. Nothing seems to work. Any thoughts would be greatly appreciated.
Thanks,
Josh
As a side note I know the ASP is correct b/c any query on the Server A works fine.
October 12, 2004 at 12:40 pm
One other piece of information I left out. When I make the logon on server A part of the sysadmin Server Role the queries run fine. However, I do not want the logon to have full rights to the SQL Server A. So, this is not a solution.
Thank you in advance for any advice.
Josh
October 12, 2004 at 12:46 pm
I've had similar issues with linked servers. Are you using SQL accounts or windows accounts? If you're using SQL accounts, make sure that B has Mixed Mode turned on (Windows and SQL). If you are using SQL logins, then I usually get the link to work by specifying the specific Login in the linked server ("Be made using this security context"). That way you can use just that login for linked connections and grant access accordingly.
If you're using Windows logins, you should be able to use the "Be made using the login's current security context" option. Then you need to make sure the Login name has access to both servers and databases.
Hope this helps.
Darren
October 12, 2004 at 1:02 pm
Thanks for the insight Darren. I am using SQL Accounts. Server B I think is set to use SQL Authentication for the user I am logging in as. I have set the "Be made using this security context" and given it the username and password for Server B. The login that I am using on server B has permission to access only 1 database on the server. Thank you again for your thoughts.
Josh
October 12, 2004 at 1:04 pm
Hi,
I am working right now on getting data from my linked server (that works) so I tested your cases as well:
Settings: I provide a correct login and pasword for the remote server linking local SA to a read-only login on the remote server .
For a login not defined in the list above settings in the linked server properties I specify the following:
1. (Works) I specified Be Made Without using a security context. Then in Query Analyzer I connect as local SA to a local server and successfully run a remote query
2. (Gives an error IDBInitialize::Initialize returned 0x80004005: ].) I specified the same option as in # 1 But I connected to to the local server in Query Analyzer as MySecondUser who was not mapped.
3. (Gives an error Login failed for user 'gmsm'.) I specified "Be Made Using Current Security Context" I connected to the local server in Query Analyzer as MySecondUser who was not mapped.
4. Your case: (Gives your error Access to the remote server is denied because no login-mapping exists.) I specified "Not to be made" and connected to the local server in Query Analyzer as MySecondUser who was not mapped.
5. (Works again) I specified "Not to be made" and connected to the local server in Query Analyzer as sa who was mapped correctly to read-only user on the remote server. Works again.
So you may specify whatever option on the Security tab in Linked Server properties but make sure that a local login who is running your process was appropriately mapped with a correct login. Make sure you really know what user is running the process. Check credentials on every step starting from ASP page. Please, let me know if you need more help.
Yelena
Regards,Yelena Varsha
October 12, 2004 at 1:19 pm
I also used a read-only login on the local server and mapped it to a read-only login on the remote server. It still works. I am opening a new connection each time I make a change and even closed and re-opened Query Analyzer, so no old credentials persist. It is working.
Please, check on the local server in the Current Activity window in Processes who is connected to the local SQL Server when you run your application as sysadmin and a low-privileged login
Regards,Yelena Varsha
October 13, 2004 at 11:28 am
Our servers are set in Mixed-Mode Authentication and security in
Linked Server properties is set to 'Be made using the login's current security context'. Make sure your servr logins have enought rights.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply