SQL Server Linked Server Security Context

5 pts.
Tags:
SQL Server 2005
SQL Server 2005 connectivity
SQL Server Linked Server
SQL Server security
Hey everyone, I'm working on some scripts that will run on a monitoring server and connect to several data servers to return information such as job status, etc. We also use linked servers for replication, etc. We use mixed authentication, however, I would like to change our linked servers to use the current security context based on AD authentication instead of connecting with the 'sa' account. When I set a linked server to use the current security context, I get: Login failed for user 'NT AUTHORITYANONYMOUS LOGON'. (.Net SqlClient Data Provider) We manage our Windows security through AD groups rather than adding individual AD logins to SQL. So if I'm connected to a server through Enterprise Manager using Windows Authentication, it sees me as a part of the DBA group rather than an individual AD user. Since my individual Windows account hasn't been added under the remote server's logins, could that be causing this? I'm pretty well just grasping at straws here, but I want to move away from using the 'sa' account for our remote connections. Any advise is most welcome. Thanks! Lee
ASKED: March 27, 2009  1:56 PM
UPDATED: March 28, 2009  3:28 AM

Answer Wiki

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

This is normal. This is caused by what is called the NTLM double hop issue. Basically a computer can not forward an NTLM authenticaion token to another computer and have it be accepted if it has not created the token.

In your case your workstation is Computer1, the monitoring server is Computer2 and the remove SQL Server is Computer3.

(This is the way simplified version)
Computer 1 generates an NTLM token when you log in.
You connect to the SQL Instance on Computer 2 using your Windows Auth. Computer 1 passes the token to Computer 2. Computer 2 accepts the token because Computer 1 created it.
Computer 2 then attempts to connect to Computer 3 via the linked server passing Computer 3 your NTLM token.
Computer 3 rejects this token because it was created by Computer 1 not Computer 2 and Computer 1 did not request the acces.

There are two workarounds.

1. Setup Kerberos on the domain and use that instead of NTLM. This will require the domain admins making several fchanges to the domain and possible breaking software which requires NTLM and doesn’t support Kerberos.

2. Use SQL Authentication to access the remote SQL Servers. Preferably an account other than SA.

Discuss This Question:  

 
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