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:

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 AUTHORITY\ANONYMOUS 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?

  • In addition to AMTwo's answer you may find some useful information in the answers to my similar question in this DBA.StackExchange post.
    – J.D.
    Commented Jun 8, 2021 at 18:19
  • If you are using a gMSA you are having a double hop problem. This blog was helpfull for me.
    – user189695
    Commented Jun 14, 2021 at 15:07

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

Your Answer

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

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