Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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!

share|improve this question

1 Answer 1

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

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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