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
Best Answer
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)
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
Verify that you can connect from server1 to server2/3 using Kerberos authentication:
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.
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.