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 2 and 3 and type the answer here:
  • Post
  • For some reason I am now getting the "Login failed for user..." error message, even after I had it working by disabling Named Pipes on Server B.  Does this have something to do with tokens expiring?  If so, how do I keep tokens from expiring or how can I renew tokens automatically?  Any assistance is much appreciated.  I thought I finally had this issue worked out but apparently not.

    Thanks.

    Jason McGuire

  • Hiya,

    My previous entry about the SP2 fix worked for all our Standard edition 2005 servers connecting to standard and enterprise 2000 servers via linked servers.

    BUT now we are getting the same issue occuring with our new Enterprise edition 2005 cluster connecting to a 2000 standard edition.

    looks like the SP2 didnt resolve the whole issue!

    anyone any ideas or updated from microsoft??

    cheers

    wendy

  • Thank you very much for your article, it's really  interesting and it has helped me to understand a lot of things about linked servers. Anyway, I'm still having problems.

    I'm trying to link different servers in different domains: Prod all of them are SQL  SQL 2005) using double-hop.

    Msg 18452, Level 14, State 1, Line 0

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

    and this one for SQL Server 2005:

    error 18466 Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

    When I execute the query: "select net_transport, auth_scheme from sys.dm_exec_connections where session_id=@@spid"

    the response is: TCP - KERBEROS

    I don't have a clue what's happening, could you help me, please?

    Thanks a lot,

    suman

  • what to do when sql server connection failed if sql server does not allow remote connection

  • Hi i am having same problem of linked server, through profiler i came to know the error is

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

    I guess i have double hop im my envoiroment. But the intresting thing is that every thing is working fine (distributed queries) form backend, but when i login through application i get the error mentioned above.

    Let me explain my Enviormnet.

    Code server: windows server 2003 (ASP application)

    DBServer A =windows server 2003 SP1 and sql 2005 Ent E with Sp2.

    DBSever B=windows server 2003 SP1 and sql 2000 Ent E with Sp4.

    Now first application calls the one or 2 storeprocedure from sql 2005 (server A) and then it calls store procedure that invloves data  from sql 2000 (Server B). Here the problem lies and i get the error mentioend above.

    But again if i run quires from (backend)sql 2005 every things work fine and if i login from apllication it will work, but after few mins like 15 to 20 mins it doesnot work.

    Apllication doest not login because it verfies the logins from sql 2000 (server B) database.

    But again if you run queries from sql 2005 (server A),those store procedure that involves sql 2000 (server B) data, it will work from backend and from front end as well. (mean you can login application)

    but again after certian period of time application will not work.

    My requirment is to create linked server with service account (windows account).

    Account has sysadmin rights on both server

    i have followd you 9 steps.....as per my network administrator every thing is fine at his end like active directory, domain controller as per your steps.

    Infcat i followed ur steps and created linked server as you mentioned but when i ran the query on serve A (Sql 2005)

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

    i get this result

    "Shared memory" "NTLM"

    and i tried to ran this query on server B (sql 2000)

    i get the error that invalied object "sys.dm_exec_connections".

    Please help me before i get crazy, the deadline is already extended.

    What i am suppose to do......seems to me MS own bug, abnormal behaviour......huh....

  • Не первый раз встречаюсь с тем, что настройка делегирования для связанных серверов (так в русском BOL

  • I am having this problem, but my linked server is an Access DB.  That is,

    1) I have Management Studio open on my workstation

    2) I connect to server (SQLA) which is a SQL 2005 server with a link to an Access DB on a file server

    3) I run the query:

    select * from AccessLinkedServer...Table1

    and I get the error:

    Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LinkedAccessDB".

    When I run the query directly from SQLA, there is no problem.

  • You need to setup the linked server to use a specific domain or local NT account that has read+write access to the folder where the Access database resides.  This is the easiest way to manage this.  

  • Msg 18456, Level 14, State 1, Line 1

    Login failed for user 'sa'.

    I am able to view data using OPENROWSET with the same credentials. But when I try to use linked server query it fails with the above exception.

  • Thanks for your detailed description of linked server authentication.

    We're having problems combining single hop authentication (with kerberos available) with changing a user context on server A using "execute as login". Also the used domain account to which we're switching the user context has permissions on both servers, we get a connection timeout, when trying to switch the context and query server B via linked server. Both servers are in the same domain and have logins for the used domain user. Is this scenario an impossible design and failed to doom or should we keep trying?

    Thank you for any hint or tip!

    Roland

  • THANK YOU SO MUCH!  I've been bothered for ages by a non-functioning double hop setup, most searches on the web return info on people trying to resolve trivial single hop setup issues.  Your article explains clearly and comprehensively the components required for double-hop setups and I now understand fully why I’ve been having trouble for so long.

    Again thank you!

  • http://blog.sqlauthority.com/2008/05/09/sql-server-fix-error-7311-you-may-receive-an-error-message-when-you-try-to-run-distributed-queries-from-a-64-bit-sql-server-2005-client-to-a-linked-32-bit-sql-server-2000-server-or-to-a-linked-s/

  • Hi Nan,

    I have the same issue with one of my clients, the scenario is complicated.  Clients access the Middle tier servers through a hardware load balancer (HLB).  The HLB used to distribute the load to the 3 middle tier servers (load balanced) serving the clients.  The 2 backend servers are clustered and utilizes a default instance of the SQL 2005 Failover CLuster DB.  I am just damn confuse on what to set for the SPNs.  hope you can help me.  Thanks.  But I'm trying to go through your article.

    More power.

    Jay

  • I had the same problem "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'". I had a linked server connection using sp_addlinkedserver that was working fine then all of a sudden it stopped working.

    I had another computer that still worked so after checking the connection using the query

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

    I discovered the working computer was connecting through Shared Memory using NTLM. The computer that failed was connecting through TCP/IP using Kerberos. So I ran SQL Server Management Studio, went to connect to my local database, selected the Advanced tab and changed the Network Protocol to Shared Memory then connected to the database. I then ran the SELECT query and problem resolved. Hope this helps.

  • I tried to create a linked server on server B to connect to server A which is not in the domain and I can access the linked server succesfully  from server B. However, I cannot run query to the linked server from my ASP code and I cannot run query from my Query Analyzer on my machine either.

    Note: I have Enterprise Manager installed on my machine and I can I register both server A and B.

    When I am trying to test the linked server that connects to Server A from Server B on my Query Analyzer, it fails.

    It says:

    OLE DB provider 'MSDASQL' reported an error. Authentication failed.

    [OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.]

    OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80040e4d:  Authentication failed.].

    Could you help me to find ou what's wrong?

Page 3 of 5 (75 items) 12345