Linked Server Queries using with only SQL Authentication - access to remote server is denied because no login-mapping exists
I am having a strange problem with querying linked servers. Currently I am receiving an error upon invoking a query that uses the linked server.
I will refer to my local server as server 1 and the remote server as server 2. I have correctly set up a linked server on server 1 using SQL Authorization to connect to server 2. On the security menu for the linked server on server 1, I have the "Be made using this security context" and provided a correct valid ID and password. I can query server 2 successfully from SQL Management Studio on server 1. Now I have a web app on server 1 that connects to the SQL server on server 1 using a different SQL login and password from that of the login used for the linked server. This different SQL login for server 1 has execute and connect permissions to a database on server 1. The database leverages the linked server by calling SP's that use views on server 1 to get data from server 2.
However, when I call one of the SP's from the web app I get the "access to remote server is denied because no login-mapping exists." If I change the permissions on the SQL login used for server 1 to sysadmin, the query to server 2 is successful. I have been looking for several hours to find out how to map a SQL login to a SQL login. As far I can see, to map, you need to use a Windows account as the remote user or the SQL Login accounts need to be the same between servers (although I tried this and it still didn't work).
Is this type of query even possible by using two different SQL Logins? I figured since I am using stored SQL login security context, it shouldn't be an issue. Especially because if I move the permissions to sysadmin the query works.
Do I need to set any specific permissions?
When you choose "Be made using this security context", it maps any logins not explicitly defined to one specific SQL account on the remote. And the syntax could be:
EXEC sp_addlinkedsrvlogin 'TEST1', 'false', 'userLocal', 'userRemote', 'remotePassword'
And then, pay attention on the remote user, try to check if the user has the right previlige.
Besides, I'm not sure if you have tried to use windows authentication, for logins not defined explictly, you can change to:
Be made using the login's current security context, and add your current windows account to both servers with right previlige. The syntax could be:
EXEC sp_addlinkedsrvlogin 'TEST1', 'true'
Microsoft Online Community Support Please remember to mark the replies as answers if they help and unmark them if they provide no help.
My problem is that I will not be able to leverage windows authentication in production. I would like to have SQL Authentication to Server 1 then SQL Authentication using a different account to Server 2. I think this is a fair requirement but I believe it isn't possible with SQL 2005. Is this correct?
When I add the SQL account there get a Access to remote server is denied because no login-mapping exists. I believe this box is used for mapping a SQL account on server 1 to a windows authentication account on server 2. If in deed you can used this for SQL to SQL mapping, my current settings won't allow it and keeps stating I don't have a mapping.
Any ideas? I really don't like the idea of give the DB account sysadmin access but at the present there is no way around this SQL authentication hicup.
Could this be an issue with SQL Express? My local test environment is SQL Express.
Are you able to successfully query Server 2 using the same login/password used in the ASP.net app?
Another issue that you may be authenticating with SQL Server from the ASP.net using Windows Authentication instead of SQL Authentication. In that case, you would have to use ASP.net impersonation.
@Tom - Again thanks for the time. I see that the mappings can be used for SQL Auth to SQL Auth but I have tried setting the mapping and it doesn't work. I believe impersonate is used for trusted delegation of window authentication. But I have set both the local and remote logins to correct values. I have also tried the EXEC sp_addlinkedsrvlogin 'LinkedServerName', 'FALSE', 'localloginname', 'remoteloginname', 'remotepassword' already and I get the same message.
I noticed a subtle line from http://msdn.microsoft.com/en-us/library/ms189811.aspx "
- [ @rmtuser = ] ' rmtuser '
Is the remote login used to connect to rmtsrvname when @useself is FALSE. When the remote server is an instance of SQL Server that does not use Windows Authentication, rmtuser is a SQL Server login. rmtuser is sysname , with a default of NULL.
It says that when the remote server is an instance of SQL Server that does not use Windows Authentication. Does this mean that server 2 cannot be in mixed mode?
I have also tried adding a valid Windows Auth to the mapping of the linked server and I get the same error "No-Mapping exist".
Are there permissions associated with the SQL login that needs to be set for mapping logins?
You must be mapping the wrong login name from the local server. Remove all mapping. Set the security page on the linked server to "be made with the login's local security context". Then try using the LinkedServer. It will fail.
Now check the log on the other SQL Server for the failure and see what login is being used.
You are correct, you do not want to check the "impersonate" since you want the new login passed.
The error "No-Mapping exists" is caused by the security setting being set to "Not be made".
The other thing to try is set the security page to "Be made using this context", for a test, and enter the login information there. See if that works.
- Proposed as answer by Kalman TothModerator Monday, March 01, 2010 5:07 PM