Linked server only works if I am on the machine itself. I can't use it from my local computer. what is missing?

Question

I have a few queries that I use to monitor log shipping. Sometimes I want to run those queries on a different server. I usually do that through a linked server.

I have a server called SQLDEV4-TS that has a linked server to MY_SERVER\DEVELOPMENT

ON SQLDEV4-TS I have setup Kerberos as per the picture below:

I have this query below that I use to test the connectivity to the linked server.

DECLARE @server_name sysname = @@servername
SELECT @server_name=N'MY_SERVER\DEVELOPMENT'


DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM OPENQUERY([' + @server_name +'], 
      ''SELECT ''''Radhe'''' AS Radhe'');'

BEGIN TRY
    EXEC sp_testlinkedserver @server_name

    EXEC sp_executesql @sql=@sql

END TRY
BEGIN CATCH
    SELECT [error_number]=ERROR_NUMBER(), [error_message]=ERROR_MESSAGE();
END CATCH;

PRINT 'We got past the Catch block!';

When I am on the server itself - SQLDEV4-TS - and I run the above script I get the following result:

All good - it works as expected - no problem.

However, when I connect to SQLDEV4-TS from my local machine, and run the script (on my machine) I get the following result:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

What is missing in this linked server setting, that I have to be on the machine itself for it to work?

Answer 1

This is an issue with Kerberos Authentication.

You are connecting to SQL Server on your own laptop, but that service runs under a different process & account than your SSMS session. Then, that SQL Server instance needs to connect across the linked server. That's one hop from SSMS to SQL Server, then a second hop to the second SQL Server instance.

Doing that "second hop" will require that you are using Kerberos. By default you are using NTLM for your Windows/AD Authentication. Kerberos is more secure, and has better domain-level controls. When you make the second hop with NTLM, your SQL Server service is not able to pass your credentials along any further, and instead passes NT AUTHORITY\ANONYMOUS LOGON. If Kerberos is correctly configured, you will get a different error.

There are a few possible fixes:

1. Configure Kerberos. This is certainly the correct answer for when you are configuring servers. You should be doing it for every server you build, but you would need to do it if you are going to be using linked servers with AD Authentication. You can query sys.dm_exec_connections for your session_id to confirm if Kerberos is correctly configured or if you are using NTLM.

   SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id=@@spid;

2. Create login mappings for your linked server. This might be sufficient for your local dev machine, but when you are storing credentials in any scenario, keep in mind the security implications, and any security policies that apply for your environment/employer.