A user needed to access one SQL server from another. No big deal. That’s just a linked server. Both servers were on the same domain and his domain account had the necessary permissions on both servers. so his authentication should pass right through. We created the linked server and told the user he should be good.
It was not good. When he expanded the Catalog on the linked server, there was nothing in it. When he tried to query the linked server, he received, “Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON'”. But he was not anonymous. He was connected to one server using his domain credentials. Why was that not passing through?
Additionally, if we opened SSMS on the server and tested the link to the other server, everything worked fine. The problem only occurred when connecting to the server remotely and then trying to access the linked server.
After way too much fruitless troubleshooting, we found an error in the event log of the user’s workstation; not the servers.
A Kerberos Error Message was received: on logon session Client Time: Server Time: 11:37:58.0000 11/23/2009 Z Error Code: 0x7 KDC_ERR_S_PRINCIPAL_UNKNOWN Extended Error: 0xc0000035 KLIN(0) Client Realm: Client Name: Server Realm: DOMAIN.COM Server Name: MSSQLSvc/server-01.domain.com Target Name: MSSQLSvcemail@example.com Error Text: File: 9 Line: e2d Error Data is in record data.
I opened the command prompt and ran:
It responded back showing that there were duplicates of MSSQLSvc/server-01.domain.com and MSSQLSvc/server-01.domain.com:1433. One was associated with the server and the other with the service account. As it should be associated with the service account, We deleted the other two.
setspn -D MSSQLSvc/server-01.domain.com server-01 setspn -D MSSQLSvc/server-01.domain.com:1433 server-01
Then, everything was good.