1

I have 3 servers all running Windows Server 2016 Datacenter:

Server1 is a domain controller and has ssms installed for accessing SQL Server Instances on Server2 and Server3

Server2 & Server3 are running SQL Server Microsoft SQL Server 2016 (SP1-CU3)

Server2 has a linked server link to Server3

Server3 has a linked server link to Server2

If I log onto server2 and run a select query which uses the link to server3

Select top 5 * from [Server3].[DB3].[dbo].[TableName]

It runs fine and results are returned.

If I log onto server3 and run a select query which uses the link to server2

Select top 5 * from [Server2].[DB2].[dbo].[TableName]

It runs fine and results are returned.

But If I log onto Server1 and open ssms, connect to Server2 and run query

Select top 5 * from [Server3].[DB3].[dbo].[TableName]

I receive error 'Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.'

And the same happens if I log onto Server1, open ssms, connect to server3 and run query

Select top 5 * from [Server2].[DB2].[dbo].[TableName]

I am logging onto all 3 servers using the same domain credentials, but it feels to me like if im running the query from Server1, then its not passing my credentials through causing the query to be ran against the target sql instance as NT AUTHORITY\ANONYMOUS LOGON

Improve this question
14
  • If you right click the linked server & script it out to a new window, do they look different?
    – James
    Feb 7, 2018 at 15:41
  • 2
    Check out A brief explanation and solution for the Double Hop problem
    – Scott Hodgin - Retired
    Feb 7, 2018 at 15:41
  • Most likely Server1 has not registered SPN
    – sepupic
    Feb 7, 2018 at 15:42
  • 1
    @Jeffrey - You'll probably have to do some Google searches on the double hop problem to see if any of the solutions will work for you. I don't think I can provide any additional information past the original link I commented on.
    – Scott Hodgin - Retired
    Feb 7, 2018 at 15:56
  • 1
    The quick-and-dirty solution is to use SQL Server logins for your linked server connections instead of using domain accounts
    – Scott Hodgin - Retired
    Feb 7, 2018 at 16:18

1 Answer 1

Reset to default
3

This is the classic "Double Hop" problem. Several pieces need to be in place for this to work. For server1 (SSMS) -> server2 (SQL) -> server3 (sql)

  • server2 must have a SPN
  • server3 must have a SPN
  • server2 must be allowed to delegate permissions to server3

A SPN (Server Principal Name) is set in Active Directory on an account and basically ties a service to that account. e.g. MSSQL Server on SERVER2 is running as domain\sql_server. When you connect with Kerberos, You request a ticket from the DC to connect to "MSSQL Server on SERVER2". The DC replies with a ticket is can only be un-encrypted by domain\sql_server.

SQL Server will attempt to self-register an SPN when it starts. This is logged in the ERRORLOG

The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/MYLAPTOP:1433 ] for the SQL Server service.

The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/MYLATOP ] for the SQL Server service.

SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.

Verify that you can connect from server1 to server2/3 using Kerberos authentication:

  1. remote into server1
  2. connect using SSMS to server2
  3. Run this:

select auth_scheme from sys.dm_exec_connections where session_id = @@spid

  1. If it returns NTLM, then you are either missing the SPN on server2 or you have a duplicate SPN. If it returns "Kerberos" then congratulations, you're partially there.

  2. Repeat this test by trying to connect from server1 to server3.

Once you can successfully connect to both server2 and server3 using Kerberos, you need to ensure that the accounts that SQL is running as have permissions to delegate. This is an active directory setting that is on the service account SQL is running as.

Improve this answer
2
  • This is a great response for someone at my level, it took me several hours to start to understand that the issue may be SPN, when I run the query, it does tell me that im using NTLM. One of the issues I have is that the servers are running as local users, not domain users. These local users are administrators. I cant seem to find any material indicating that I can get this to work under my scenario, if you are able to help with this any further it would be great.
    – Jeffrey
    Feb 7, 2018 at 21:25
  • For Kerberos to work, the DC must know about the account that MSSQL is running as. If SQL is running under a local SERVER\user, then the DC won't know about that local account and Kerberos can't work. It doesn't matter whether the users are local admin or not. If it's running under NETWORK SERVICE, then that is the SERVER$ account. I recommend NETWORK SERVICE.
    – StrayCatDBA
    Feb 7, 2018 at 23:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.