We have two SQL servers and we've setup a them as linked servers. The queries works just fine if I connect remote desktop to one of them and execute query. If I try to execute query from third machine (in same domain as first two) which uses linked servers I get the following error :(
Msg 18456, Level 14, State 1, Line 1 Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
This is my current setup:
SELECT @@servername as [SQL Instance]
, s.name as [Linked Server]
, s.data_source as [Remote Server]
, CASE l.uses_self_credential
WHEN 1 THEN 'PASS-THRU'
ELSE ISNULL(p.name, 'UNMAPPED LOGINS')
END AS [Local Login]
, CASE l.uses_self_credential
WHEN 1 THEN 'PASS-THRU'
ELSE l.remote_name
END AS [Remote Login]
FROM sys.linked_logins l
JOIN sys.servers s ON s.server_id = l.server_id AND is_data_access_enabled = 1
LEFT OUTER JOIN sys.server_principals p ON p.principal_id = l.local_principal_id
ORDER BY [Linked Server], [Local Login]
Result:
SQL Instance Linked Server Remote Server Local Login Remote Login T-CLSQL-01 T-CLSQL-02\R T-BN-CLSQL-02\R PASS-THRU PASS-THRU
Thanks in advance!
This looks like a Kerberos problem, most probably the SPN is not correctly registered so you can't send the credentials through to the remote server. Check out evilhomers excellent post here: SQL Server to sql server linked server setup