SQL Linked Server Query failed with “Login failed for user …”

SQL Linked Server Query failed with “Login failed for user …”

Rate This
  • Comments 75

SQL Server 2005: “Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'”.

SQL Server 2000: "Login failed for user "(null)". Reason: Not associated with a trusted SQL Server connection".

 

The errors listed above is very typical when deploy linked server with delegation. They actually are thrown by the linked server and pass by middle server to the client application. In this post, I will discuss how to properly configure SQL instances and Windows environment in most common scenario and try to make configuration steps as explicit as possible.

 

By using delegation in distributed query, such as linked server query, the SQL instance obtains impersonated token of the user logon credential to gain access to resources of another SQL instance, the linked server. In delegation setting, the client connection and linked server object are configured to use integrated authentication in SQL Server’s term as opposed to SQL login. Some time integrated authentication also referred as trusted connection or Windows authentication. Linked server login can also use SQL login, but it is not discussed here.

 

To simplify the discussion, let’s assume two SQL Server instances are installed on machine A and B respectively. Also, let’s assume A is the middle server that has a linked server object configured which points to a SQL instance on machine B. If the client is on machine C different from A, we call it double-hop setting; if the client is collocated with middle server machine A, we call it single-hop setting. In single-hop setting, it is relatively straightforward to configure linked server to work. Believe or not, double-hop setting requires more careful configurations as you will see. This is because in single-hop setting, windows NTLM authentication, which is available in most common setting if all machines are windows, is sufficient for delegation; while in double-hop setting, Kerberos authentication is mandate for flowing user’s credential through machine boundaries from the client to the linked server. It requires windows domain, correct DNS name resolution, proper account setting in both Active Directory and SQL Server.  To make sure Kerberos delegation [1] is correct becomes vital to operate distributed query with delegation. The authentication scheme required by delegation in different setting is illustrated by the following table.

 

Authentication scheme

C to A

A to B

Single hop

NTLM or Kerberos

(C is on the same box as A)

NTLM or Kerberos

Double hops

Kerberos

Kerberos

 

[Create Linked Server Object on Middle Server]

Before getting into details on how to configure other components, since I am talking about delegation in the context of SQL Server distributed query, let’s first give an example on how to configure a linked server object on A and set up its login to use delegation.  To do so, you need the following two steps.

 

(1) Use sp_addlinkedserver to create a linked server object and name it “LinkedServer” which points to the SQL instance on machine B, SQLB.

 

“EXEC sp_addlinkedserver @server=’LinkedServer’,

                                                 @srvproduct=''”,

                                                 @provider='SQLNCLI',

                                                 @datasrc=’SQLB’,--the data source

                                                 @provstr="Integrated Security=SSPI; "

To verify if the command is executed correctly, run query

select * from sys.servers where name='LinkedServer'”;

 

(2) Use sp_addlinkedsrvlogin to configure login to use self-mapping as following

exec sp_addlinkedsrvlogin ‘LinkedServer’, 'true'”

Step (2) makes middle server A try to use impersonated token of user to authenticate to server B. To verify that the linked server is setup for “self-mapping”, run query

 

select uses_self_credential as delegation

from sys.linked_logins as L, sys.servers as S

where S.server_id=L.server_id

            and S.name=N'LinkedServer'

 

The resulting table should show the delegation column is “1”.

 

[Test Linked Server Query in Single-hop Settting]

Before test-drive a link server query in single-hop setting, you need also make sure that the client user can make direct query to the SQL instances on both A and B. This means that the user account, either windows domain account or a machine account, must have permission to access both SQL instances.

 

(3) To verify the user domain account has permission to access both SQL instances, use your favorite client tool, for example,“osql –E –S SQLA” and “osql –E –S SQLB”. If you are failing for whatever reason, please refer to [5][6].

 

(4) To test linked server query, run query at SQLA,

“select * from LinkedServer.master.dbo.sysdatabases”.

 

 

[Configure and Test Double-hop Setting]

To deploy delegation based linked server in double-hop setting, the followings need to be configured correctly.

(3)   Kerberos in Windows mandates Windows domain. Therefore the user account needs to be a domain account and middle server and linked server need to join a domain. All machines involved in the delegation, including client machine, middle server and linked server, must have good TCP/IP connectivity between each other and to the domain controller and Active Directory. To not complicate thing further, we assume that A, B and C are in same Windows domain D and the user account is a domain account in D.

(4)   The user’s domain account must NOT select “Account is sensitive and cannot be delegated” in its Active Directory properties of domain D. Please refer to [1] on how to configure this on Active Directory machine.

(5)   The service account under which the SQL instance is running must be “trusted for delegation”, configured in Active Directory. If the service is running as “NT AUTHORITY\SYSTEM” or “NT AUTHORITY\NETWORK SERVICE”, the computer must be “trusted for delegation”. Please refer to [1] on how to configure this on Active Directory Machine. You need to have domain admin privilege to do/verify so.

(6)   The user domain account must have permission to access both SQL instances from C. To verify, use your favorite client tool, for example,“osql –E –S SQLA” and “osql –E –S SQLB”. If you are failing for whatever reason, please refer to [5][6].

(7)   If the SQL connections are to use TCP/IP connectivity, configure and verify that SQL connections from C to A and A to B are using Kerberos authentication. Please refer to [2] on how to configure Kerberos for SQL. In a nutshell, both services on machine A and B need to have a SPN created in Active Directory for SQL service. If the service running account is not “NT AUTHORITY\SYSTEM”, you need to configure the SPN on the Active Directory Machine with domain admin privileges. To verify that every hop is using Kerberos and TCP connectivity, run query

 

“select net_transport, auth_scheme from sys.dm_exec_connections where session_id=@@spid”

 

when (a) connect to A from C (b) connect to B from A. To make Kerberos work for both hops is crucial and some time it might not be very straightforward on what goes wrong. If fail to get Kerberos to work, please (a) verify If the SPNs are configured and well-formed according to [4]; use “setspn –L acccoutname” to verify the SPN (b) verify if DNS reverse lookups of both machine A and B return well-formed FQDNs. Use “ping –a machinename” on machine A, B and C to verify DNS works as expected, i.e. returning FQDN; (c) make sure that there is no cached Kerberos ticket on machine A and C. Use “klist purge” to purge all tickets. There might be delay before Windows local security authority (LSA) requests a new ticket from Active Directory. Sometime, you need to log out and log back in again before a new Kerberos ticket can take effect. For more Kerberos troubleshooting techniques, please refer to [3].

(8)   If the SQL connections are to use Named Pipe connectivity, SQL level Kerberos is not required as opposed to TCP connectivity. This is because Windows named pipe protocol can use Kerberos to authenticate logon user under the cover. You need to verify that both machine SPNs “HOST/machinename” and “HOST/ machineFQDN” is well-formed use “setspn –L machinename”.

 

(9)   Since double-hop can have combination of Named pipe connectivity on one hop and TCP on the other, the following table is valid configuration for delegation. Run query

 

“select net_transport, auth_scheme from sys.dm_exec_connections where session_id=@@spid”

 

when (a) connect to A from C (b) connect to B from A.

 

{net transport, auth_scheme}

C to A

A to B

1

NP, NTLM*

NP, NTLM*

2

TCP, Kerberos

NP, NTLM*

3

NP, NTLM*

TCP, Kerberos

4

TCP, Kerberos

TCP, Kerberos

* Note that even SQL shows NP is using NTLM at SQL level, Windows named pipe protocol can still use Kerberos under the cover ( the SMB/CIFS windows file system, upon which the named pipe is implemented, can use Kerberos) that allows Kerberos delegation.

 

After verifying the above 9 steps, finally we come to the point to run a double-hop linked query. If you still encounter issues after follow these steps, please send me the error message, your configuration, the output of the queries listed for every step and information required by [6].

 

In discussion above, we assume the A,B and C are in the same domain. If they are in different domains, apart from verifying 9 steps listed above, you also need to make the domains involved trust each other. 

 

Note that setspn.exe, klist.exe referred in this post can be downloaded as part of Windows resource kit. Kerberos delegation is only supported on windows 2000 and onward.

 

[Reference]

[1] Troubleshooting Kerberos Delegation. http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerbdel.mspx

[2] Using Kerberos with SQL Server. http://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspx

[3] Troubleshooting Kerberos Errors. http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerberr.mspx

[4] http://support.microsoft.com/kb/889615/en-us

[5] http://support.microsoft.com/?id=840219

[6] http://blogs.msdn.com/sql_protocols/archive/2006/04/21/581035.aspx

 

 

Nan Tu

Software Design Engineer, SQL Server Protocols

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

 

Leave a Comment
  • Please add 8 and 8 and type the answer here:
  • Post
  • Hi Bob,

     Making a successful Windows Authentication connection across the boundary between a domain and a workgroup is a bit of a hack as is, and isn't recommended.  I'm guessing you're using a workaround like is described here: http://blogs.msdn.com/sql_protocols/archive/2007/05/12/connecting-to-sql-server-from-a-workgroup-using-windows-authentication.aspx.

    When using Windows Auth for a linked server query which is initially coming from a remote client, as in the situation you describe, it requires Kerberos authentication with these special delegation settings enabled, and Kerberos is not available in a workgroup.  So, I think for the scenario you have, the solution that will give you the fewest headaches and which will definitely work will be to use SQL Authentication for your linked server queries, rather than Windows Authentication.  As it says in that blog post I pointed to above, we recommend SQL Authentication for connection in workgroups anyways, and your extra requirements mean that it really doesn't make sense to try to get Windows Authentication.

    Hope this helps,

    Dan

  • I have many SQL Servers, all configured the same way, but a few will not use Kerberos authentication.

    SQL Server 2005 SP2, Standard Edition x64, W2K3 SP2

    All SQL services run under the same domain account, the account and all SQL servers are set for trusted delegation (Kerberos Only).

    The service has the ability to register its own SPN’s, and I have also done so manually just in case.

    On most of the servers I get TCP – Kerberos, and then on three of them I get TCP – NTLM.  

    Things I have tried to get those servers to authenticate with Kerberos.

    1. Removed and added to the servers to the domain.

    2. Changed the service account, and changed it back.

    3. Restarted the servers. (Both those that work and those that do not.)

    4. dnsflush

    5. klist purge

    6. Manually deleted and added spn’s.

    Do you have any other suggestions?

  • Hi Nan Tu,

    this is one of the most helpful posts i've ever read !

    thank you so much and many greatings from AUSTRIA !

  • HI,

    Well i faced this issue with double hoping in SQL server 2008.

    There is one more change in 2008 for linked server setting

    PROMOTION OF DISTRIBUTED TRANSACTIONS. That needs to be enabled if we intend to establish Double hop connections.

    Thanks,

    Mani

  • Hi,

        I am recieving the same error what is specified in this article. I checked my connexn for single hop stting and it returned delegation as 1.

    Then I checked it for double hop setting and got

    TCP NTLM

    for both of the servers. Please help me out further to get rid of this eror.

    Regards

    Mohit

    Email: Mohit_Tiwari@syntelinc.com

  • PingBack from http://www.hilpers.it/2759918-sql-2005-problema-linked-server

  • PingBack from http://www.keyongtech.com/2242394-link-server-not-working-thru

  • Hi,

    Thanks for great article

    I have a question for this query:

    “select net_transport, auth_scheme from sys.dm_exec_connections where session_id=@@spid”

    Anyone know it's version for SQL 2000 Srv?

    Thanks in advance

    Pawel

  • Spot on this - delegation on the SQL account and some reverse lookup tweaks later it's working a treat :-)

  • I have a SQL one node cluster and  another SQL one node cluster .. and i want both of them to be connected trough the linked server . I am using Windows auth and i fail with the Login Failed for user Nt Authority\Anonymous logon...

    Interestingly When i am connecting from any other SQL Server instance to one of the cluster instance the linked server runs just fine...

    This seems t be onehop as we have 2 cluster node talking to each other...right?

    I even created SPNS for the virtual server name on both the clusters and while taking netmon ..i see that NTLM is being used(i read NTLM string in the big Junk). I am also seeing that the error message coming from the target SQL server cluster.

    Now i have 3 questions

    1. Why would it will still fails in one hop?

    2 > Is there something cluster specific i need to do on Active directory.

    3. while i am using window authentication ..the SQL server service account should pass ..why would it change to NT Authority \Anonymous logon...

    Any help will be great to understand and resolve the issue

    Thanks ,

    Raju

  • I've followed the instructions here and made the changes necessary but cannot get the double-hop to work.

    Here's the situation...

    From my desktop using SQL2008 SSMS I connect to server A which is SQL2008 on Windows2008 which has a linked server configured for server B which is SQL2000 on windows2003.

    Linked server connection is fine from server A to B. this is the sticky part.

    From my desktop (C) I connect to server A successfully but fail when making the linked server call to server B. If I RDP onto server A and make the linked server call to B then go back to my SSMS on my desktop I can now double hop. It's like my credentials are being cached.

    AD Settings - we enabled delegation on service account for A for all traffic cos when trying to enable delegation for server B it doesn't show the mssqlservice in the list of services to trust for delegation. Does this have something to do with this?

    Also, the service account on Server A is not a local admin so the SPN was set manually for the service account. The service account on server B is a local admin so it would dynamically set.

    Any ideas please on what is stopping the double hop?

    I can tell you that my connection from C to A is TCP/Kerberos.

    Regards, Brent

  • It also seems like Integrated Security=SSPI is not a valid option on the connection string, at least in the case of a SQL Server 2005 instance adding a linked server to a SQL 2008 instance.

  • I have 3 PC (A,B,C)

    in this A is the server for workgroup1 and B is the server for workgroup2, and C is a node in Workgroup2

    Here i have Sql server 2000 in A and B and My application is running in C

    i have 2 Databases one is in A and another is in B I need to access the tables in A by using linked queries from B. when i try to run my query in my application from Server B it running correctly but when i try to run my application from node C i got the error message is below

    Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection.

    How to solve this problem?

    i'm not using the service pack in my Sql Server,

    My Sql Server is set to Mixed mode Authentication.

    Plz give me a sugession to rectify the problem.

    moorthi.vina@gmail.com

    Srinivasa Moorthi V

  • PingBack from http://workfromhomecareer.info/story.php?id=9422

  • PingBack from http://fixmycrediteasily.info/story.php?id=15234

Page 4 of 5 (75 items) 12345