none
Linked Server Queries using with only SQL Authentication - access to remote server is denied because no login-mapping exists

    Question

  • 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?
    Thursday, February 11, 2010 9:36 PM

All replies

  • Hi,

    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'

    Thanks.

     


    Microsoft Online Community Support Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Tuesday, February 16, 2010 1:55 AM
    Moderator
  • Hi Nai-dong,

    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? 
    Tuesday, February 16, 2010 1:22 PM
  • This is possible.  This is what mapping the login does.

    Right click on the linked server in SSMS, select properties, go to the Security screen.   The top of that screen allows you to map a user on Server1 to a user/password on Server2.

    Tuesday, February 16, 2010 2:06 PM
    Moderator
  • Hi Tom,

    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.

    Thanks,

    Jason
    Tuesday, February 16, 2010 9:55 PM
  • That option exists specifically for the situation you are describing.  It maps SQL logins on Server1 to SQL logins on Server2. 

    Are you sure you have the correct login entered in the "local login" and the "Impersonate" box checked?
    Wednesday, February 17, 2010 4:49 PM
    Moderator
  • You can also try using the command:

    EXEC sp_addlinkedsrvlogin 'LinkedServerName', 'FALSE', 'localloginname', 'remoteloginname', 'remotepassword'

    Wednesday, February 17, 2010 4:52 PM
    Moderator
  • Hi,

    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.

    Adam
    Wednesday, February 17, 2010 5:00 PM
  • @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?

    Thursday, February 18, 2010 1:26 PM
  • @Adam, 

    Unfortunately, I am not using ASP.Net but with the SQL credentials I can set up a connect from SSMS and my web app.  My web app is not using Windows Authentication. 
    Thursday, February 18, 2010 1:29 PM
  • Can anyone assist?
    Kalman Toth, SQL Server & BI Training, SSAS OLAP, SSIS, SSRS; http://www.SQLUSA.com
    Tuesday, February 23, 2010 6:06 PM
    Moderator
  • 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.
    Wednesday, February 24, 2010 1:53 PM
    Moderator