SQL Server – Resolving Linked Server Issues

linked-serversql server

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)

  • 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.

Related Question