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

Posted on

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_SERVERDEVELOPMENT

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

enter image description here

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

DECLARE @server_name sysname = @@servername

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

    EXEC sp_testlinkedserver @server_name

    EXEC sp_executesql @sql=@sql

    SELECT [error_number]=ERROR_NUMBER(), [error_message]=ERROR_MESSAGE();

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:

enter image description here

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 AUTHORITYANONYMOUS LOGON’.

enter image description here

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

Answer :

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

Leave a Reply

Your email address will not be published. Required fields are marked *