Home / user-82588

dunno's questions

Martin Hope
dunno
Asked: 2011-05-27 01:20:50 +0800 CST
  • 6

I'm trying to set up the following scenario. I have 3 servers, they are all in same domain.

  • Server A has web application which runs under (domain) service account, dom\web.
  • Server B has instance of SQL Server 2008 R2 which runs under (domain) service account, dom\sql.
  • Server C has instance of SQL Server 2008 R2 which runs under (domain) service account, dom\sql.

Servers B and C are in SQL Cluster. Instances of B and C are linked servers.

When I run SQL Server Management Studio from A and connect to B using my domain account (dom\usr) I try to execute the query which selects data from B and C and it works.

When I try web application which tries the same I get the error:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

I see that connection in SQL has auth_scheme KERBEROS for user dom\web so it's not NTLM.

Also, dom\web domain account doesn't have selected "account is sensitive and cannot be delegated" option in AD.

I also think that SPN is correctly set up because double hop wouldn't work in the first case.

This is error on server C:

Source Logon

Message Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 10.65.10.53]

Source Logon

Message Error: 18456, Severity: 14, State: 11.

sql-server sql-server-2008 linked-server
Martin Hope
dunno
Asked: 2011-05-26 05:33:59 +0800 CST
  • 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!

sql-server-2008 sql