SQL Linked Server Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

Sometimes when trying to access a linked server, you’d get an error saying “Login failed for user NT AUTHORITY\ANONYMOUS LOGON”. 

This happens because you’re connected using Windows authentication, and SQL Server fails to “forward” your credentials to the linked server.

To determine the authentication method of a connection, execute the following query:

SELECT net_transport, auth_scheme
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

Validating Authentication Properties Used by Connections

USE [master]
GO
SELECT COUNT(auth_scheme) as sessions_count, net_transport, auth_scheme
FROM sys.dm_exec_connections
GROUP BY net_transport, auth_scheme

 

To resolve this:

• Both SQL Servers and the Client must be in the same Domain
• Both SQL Servers must be able to register SPNs (Service Principal names)