Linked server error message on a SQL Server 2005 machine

Tags:
Linked servers
SQL Server 2005
SQL Server errors
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?
ASKED: September 16, 2008  3:25 PM
UPDATED: September 16, 2008  11:57 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following