locked
Linked Server connection fails when accessing it from different source (same user) RRS feed

  • Question

  • I am having troubles with my linked server configuration.

    I have two servers: server1 and server2. On server 1 I have created a Linked Server object that refers to server2, using the following statement (using SSMS on server1).

    EXEC sp_addlinkedserver @server = 'INSQL', @srvproduct = '', @provider = 'SQLNCLI11', @datasrc = 'server2'

    go

    EXEC sp_serveroption 'INSQL','collation compatible',true

    go

    EXEC sp_addlinkedsrvlogin 'INSQL','TRUE',NULL,NULL,NULL

    Go

    I can right-click the server object and click Test Connection with the result that the test succeeds.

    The issue that I am facing is the following: when opening SSMS on server2 (instead of server1), but still connecting to the Database Engine on server1, the Test Connection for the Linked Server fails. Giving the error: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

    This is very strange for me, since I am connecting to the same database engine, but apparently it matters on which server I open SSMS and do the Test Connection on the Linked Server object.

    The servers are both in the same domain. In SSMS I log in using AD Windows credentials (same for both servers). I have checked the firewall on both servers, which allows traffic on TCP1433 and UDP1434. I have configured and set the DTC settings on both servers.

    Any thoughts on what the issue could be?

    Thanks.

    Tuesday, August 7, 2018 4:02 PM

Answers

  • This the classic double-hop problem.

    When server1 connects to server2 it needs to impersonate you, and if you have logged in on server1, this is not a problem. But if you logged in on a different machine, server1 must be trusted to vouch for you. This is more of a Windows issue and an SQL Server issue.

    A starting requirement for this to work is that you have Kerberos in place. With only NTLM this is never going to work. Then you also need to get Kerberos right, but I'm a Windows guy or an admin guy, so I am not really able to help with that part.

    • Proposed as answer by Teige Gao Wednesday, August 8, 2018 6:05 AM
    • Marked as answer by Martin_T- Wednesday, August 8, 2018 11:16 AM
    Tuesday, August 7, 2018 9:42 PM

All replies

  • This the classic double-hop problem.

    When server1 connects to server2 it needs to impersonate you, and if you have logged in on server1, this is not a problem. But if you logged in on a different machine, server1 must be trusted to vouch for you. This is more of a Windows issue and an SQL Server issue.

    A starting requirement for this to work is that you have Kerberos in place. With only NTLM this is never going to work. Then you also need to get Kerberos right, but I'm a Windows guy or an admin guy, so I am not really able to help with that part.

    • Proposed as answer by Teige Gao Wednesday, August 8, 2018 6:05 AM
    • Marked as answer by Martin_T- Wednesday, August 8, 2018 11:16 AM
    Tuesday, August 7, 2018 9:42 PM
  • Hi Martin_T-,

    As mentioned by Erland, this is a double hop problem. Here is a blog which describes about this: https://blogs.msdn.microsoft.com/sqlupdates/2014/12/05/sql-server-kerberos-and-spn-quick-reference/

    We will need to use Kerberos Protocol rather than NTLM Protocol for Windows Authentication, only Kerberos Protocol can work in this scenario. Then we will need to create Delegation for the service. For more information, please refer to the steps mentioned in that blog.

    Best Regards,
    Teige



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 8, 2018 6:13 AM
  • Hi Erland and Teige,

    Thanks both of you for the quick feedback. The provided link on Kerberos and SPN provides a very good and concise overview.

    I checked the SQL DMV query, and it indeed returned the auth_scheme being NTLM. I downloaded and ran the Tool called Kerberos Configuration Manager for SQL Server. It indicated a Mismatch, but the suggested script to fix it did not work.

    It is stating to remove SPN MSSQLSvc/server2.dev.com from account dev\server2$ and add SPN MSSQLSvc/server2.dev.com to account dev0\server2$.

    Our userdnsdomain is dev.com, but the userdomain is dev0.

    But when running the script from the tool as domain admin it can't delete because the account does not exsit, and it doesn't add because the entry already exists.

    Anyway, for now I have created a SQL user that has the db_datareader role on the db that I need to access through the Linked Server Object, and configured that as the remote login to be used.

    Wednesday, August 8, 2018 11:08 AM