0

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!

squillman
  • 37,618
  • 10
  • 90
  • 145
dunno
  • 61
  • 1
  • 2

1 Answers1

0

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

Community
  • 1
Michael Eklöf
  • 519
  • 4
  • 6
  • Tried it, but no luck. SQL service account and user has enabled delegation, the computers has enabled delegation, SPN is registered. – dunno May 25 '11 at 16:26
  • Ok, I'm one step further. Now I can do double-hop with my domain account (tried with ssms), but I cannot do it with my domain service account which runs my web application. The option "account is sensitive and cannot be delegated" is NOT selected. What can I check? Thanks! – dunno May 25 '11 at 22:02