none
Receiving error when querying Linked Server

    Question

  • I am having an Issue with Linked Servers, perhaps someone can explain what is going on.

    I have three servers:  ServerA, ServerB and Server C.  Each has a Linked Server configured to the other two.  (All MS SQL Server 2008 SP1). 

    All the linked servers are configured the same:  "Be Made using the login's current security context" is selected, and "Data Access", "RPC", "RPC OUT" are all set to True.

    My Domain account is a sysamdin on all three servers.

    This is what I am experiencing:

    1)  Log In to Server A with Remote Desktop
    2)  Open SSMS, and Connect to ServerB
    3)  Execute Query ( SELECT Top 1 * from [LnkServerA].[Database].[dbo].[TableName] )
    4)  Receive error:  Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
    5)  Log On to ServerB with Remote Desktop, and Connect to ServerB With SSMS
    6)  Execute Query ( SELECT Top 1 * from [LnkServerA].[Database].[dbo].[TableName] )  Successfully
    7)  Go back to ServerA and Run same query from Step 3, this time it is successful.

    What is happening that I am not allowed to execute the query until I log on to ServerB and and execute it? From then on, I am able to run the Query from ServerA with no trouble. Is this a configuration problem? 

    Thanks



    Matt

    jeudi 3 mai 2012 17:07

Réponses

  • Hi Bortiquai,

    Regarding to the error message and your description, which might be related to mapping between logins on the local instance and remote logins on the linked server issue.

    You can try to map the clients on server A to a standard security login on server B, by using either the sp_addlinkedsrvlogin stored procedure or the Security tab of the Linked Server Properties dialog box in Enterprise Manager.

    For more information about sp_addlinkedsrvlogin, please refer to the answer of this thread with the same scenario as yours. http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/8ec97763-c978-48dd-a92c-4b47e6a959d7

    Alternatively you can supply a specific SQL Server login, means ANYONE with access to your instance can now execute a query against that linked server and would be running on that linked server under the security context of the account that you specified.

     


    Regards, Amber zhang

    mardi 8 mai 2012 06:39
  • Hi Bortiquai,

    This is happening because of the NTLM Authentication, when you have a double hop scenario, the middle server sends the request to the SQL/application with the Anonymoun Logon account. To resolve this issue you need to set SPNs on the Domain Controller for all the servers and you also need to enable delegation on servers.

    You can also use SQL Authentication to workaround this problem.

    Thanks,

    Santosh.

    mardi 8 mai 2012 11:35

Toutes les réponses

  • Have you tried to configure the Linked server with SQL Server logins? To solve this problem, i suspect you may need to set SPN for Linked Server A.

    http://support.microsoft.com/kb/929650

    vendredi 4 mai 2012 07:18
  • Hi Bortiquai,

    Regarding to the error message and your description, which might be related to mapping between logins on the local instance and remote logins on the linked server issue.

    You can try to map the clients on server A to a standard security login on server B, by using either the sp_addlinkedsrvlogin stored procedure or the Security tab of the Linked Server Properties dialog box in Enterprise Manager.

    For more information about sp_addlinkedsrvlogin, please refer to the answer of this thread with the same scenario as yours. http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/8ec97763-c978-48dd-a92c-4b47e6a959d7

    Alternatively you can supply a specific SQL Server login, means ANYONE with access to your instance can now execute a query against that linked server and would be running on that linked server under the security context of the account that you specified.

     


    Regards, Amber zhang

    mardi 8 mai 2012 06:39
  • Hi Bortiquai,

    This is happening because of the NTLM Authentication, when you have a double hop scenario, the middle server sends the request to the SQL/application with the Anonymoun Logon account. To resolve this issue you need to set SPNs on the Domain Controller for all the servers and you also need to enable delegation on servers.

    You can also use SQL Authentication to workaround this problem.

    Thanks,

    Santosh.

    mardi 8 mai 2012 11:35