Linked server error message on a SQL Server 2005 machine
We are installing a vendor-developed application that requires linked servers between the vendor database’s server and many of our other servers. It has been set up and tested effectively, but it should be noted that it is a double hop to the servers. Server A (which houses the application) queries the linked server defined on Server B, which then queries Server C. The queries work at first, but eventually we receive this error message from Server A:
"Msg 18456, Level 14, State 1, Line 1 Login failed for user 'NT AUTHORITYANONYMOUS LOGON'."
If we run the same query through Server B, it works, but Server A returns the same error message 10 minutes later. All our machines run SQL Server 2005 on Windows Server 2003. Do you have any idea why this might be happening?
Software/Hardware used:
Software/Hardware used:
ASKED:
September 16, 2008 3:25 PM
UPDATED:
September 16, 2008 11:57 PM
Answer Wiki:
This is because you are using NTLM authentication and Windows authentication between the servers.
NTLM only allows you to connect to one server, and does not allow that server to forward your authentication along to the third server.
Your options to fix this are:
1. Enable Kerberos authentication for the servers (check with your network admin on how to get this setup).
2. Setup the linked servers to log into the remote SQL servers using SQL Authentication instead of Windows Authentiction.
This error will come up regardless of OS or SQL Server version.
To see all answers submitted to the Answer Wiki: View Answer History.
Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.