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 1 and 1 and type the answer here:
  • Post
  • I managed to get double hops to work... the key thing is that the SQL Server service account must be set to "trusted for delegation" and this is an AD setting so you need a domain admin to do this for you.

  • Hi,

    Could you tell me if SQL Server 2008 has double-hop issues? I mean, if we update the sql server from 2000/2005 to 2008, can double-hop issues disappear? Or are there any alternative method(rather than modifying the NTLM to Kerberos) to resolve double-hop issues in Sql server 2008?

  • Hi,

    Login failed for user '******'. (.Net SqlClient Data Provider)

    Имя сервера: **********

    Номер ошибки: 18456

    Серьезность: 14

    Состояние: 1

    Номер строки: 65536

    What should I do?

  • Here's my scenario...

    I have verified steps 1-9.  In scenario below, logging on to all boxes with my domain login which is local administrator and sysadmin.

    Server 2 (SQL 2008) linked to Server 1 (SQL 2005) and Server 3 (SQL 2000).  Using SSMS on S2, can query sysdatabases on S1 and S3.  Then, using SSMS on desktop, can query sysdatabases on S1 and S3.  Wait ten minutes, query from destop fails (Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. to S1 and Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. to S3.  Now, rerun query on S2, success.  Now, rerun query on desktop, success.  Wait 10 more minutes and desktop query will fail again.  Is there a token that is expiring somewhere?

  • Correction to last post...

    On step 5, my AD admin only trusted delegation for SQL services running on Server 2.  He changed it to trust for all services on my service account and it worked.  Then we narrowed it down to trusting delegation for all SQL services on Server 1, Server 2 and Server 3 and it is working like a charm.  Great article Nan.  Thanks!

  • Client - WinXP (SQL 2005 Mgmt Studio)

    Server B - Win2003 32 bit - SQL 2005 sp2

    Server C - Win2008 64 bit - SQL 2005 sp3

    Error Message:

    Msg 18456, Level 14, State 1, Line 1

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

    OLE DB provider "SQLNCLI" for linked server "GHPADW" returned message "Invalid connection string attribute".

    (1) sp_addlinkedserver result: Command(s) completed successfully.

    (2) sp_addlinkedsrvlogin result: Command(s) completed successfully.

    (2b) delegation = 1

    (3) connection to B and C successful

    (4) select * from LinkedServer result: returns list of databases

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

    net_transport                            auth_scheme

    ---------------------------------------- ----------------------------------------

    TCP                                      KERBEROS

  • Update to last two posts from me...

    The last two posts were related to our dev environment.  Our test environment is segmented from dev from prod at the network layer.  We were unable to get it working by delegating just the MSSQL services per server.  So, we went back to delegating at the domain account runing SQL server for all services.  It is now working in our test environment and expect to have no issues in prod environmant.

  • Hi ,

    I have a solution : you should install "mdac" (Microsoft Data Access Components, I am using version 2.0.) ,then you enable account "sa" .

    that may be help your problem.

  • Nice guide, followed the steps but still having problems.

    Steps 1, 2 & 3 work ok.

    When I run step 4 I get this error

    Msg 18456, Level 14, State 1, Line 1

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

    OLE DB provider "SQLNCLI10" for linked server "**SERVERNAME**" returned message "Invalid connection string attribute".

    I can connect from A to B ok with the linked server but not from C to B through A (double hop)

    I can connect ok to A or B from my workstation

    C to A connects using Kerberos

    A to B connects using kerberos

    Ping and reverse lookup from A to B reveals FQDN in place for both

    I have tried logging out and back in but im still having problems.

    Im running windows 7 as my client, and Sql 2008 with 2008 R2 OS

  • We have windows authentication problem on some VM desktops with XP Professional sp3. A set of desktop is not able to execute linked server queries while other set do.

    Have gone through all of your suggested steps and that uncovered the fact that instead of KERBEROS that those problem desktops are using TCP, NTLM*.

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

    ------------------------------------------

    {net transport, auth_scheme} C to A A to B

    1 TCP, NTLM* TCP KERBEROS

    2. SQL Server Versions:

    --------------------------------------

    A SQL Server 2008 R2

    B SQL Server 2000

    3. Domain Info

    --------------------------------------------

    i. All A, B, and C are running under the same domain

    ii. SQL Server service domain account is common on both A and B

    4. Error reported while trying to execute linked server query:

    ------------------------------------------------

    Msg 18452, Level 14, State 1, Line 0

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

    5. After I enabled KERBEROS logging, I found below in the system event log of SQL Server machine (Computer B in your example) :

    i. STATUS_ACCESS_DENIED

    ii. KERB_ERROR_RESPONSE_TOO_BIG

    6. Apart from you suggested steps I have already tried to remove/re-add the problem desktop from domain, including unregistered the SPN of desktop, but no cluck

    7. Script of Link Server

    -------------------------------------

    /****** Object:  LinkedServer [PROD2K]    Script Date: 02/27/2012 10:48:19 ******/

    EXEC master.dbo.sp_addlinkedserver @server = N'PROD2K', @srvproduct=N'SQLServer', @provider=N'SQLNCLI', @datasrc=N'kew.mondrian.mipl.com'

    /* For security reasons the linked server remote logins password is changed with ######## */

    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'PROD2K',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'PROD2K',@useself=N'True',@locallogin=N'Interface',@rmtuser=NULL,@rmtpassword=NULL

    GO

    EXEC master.dbo.sp_serveroption @server=N'PROD2K', @optname=N'collation compatible', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'PROD2K', @optname=N'data access', @optvalue=N'true'

    GO

    EXEC master.dbo.sp_serveroption @server=N'PROD2K', @optname=N'dist', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'PROD2K', @optname=N'pub', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'PROD2K', @optname=N'rpc', @optvalue=N'true'

    GO

    EXEC master.dbo.sp_serveroption @server=N'PROD2K', @optname=N'rpc out', @optvalue=N'true'

    GO

    EXEC master.dbo.sp_serveroption @server=N'PROD2K', @optname=N'sub', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'PROD2K', @optname=N'connect timeout', @optvalue=N'0'

    GO

    EXEC master.dbo.sp_serveroption @server=N'PROD2K', @optname=N'collation name', @optvalue=null

    GO

    EXEC master.dbo.sp_serveroption @server=N'PROD2K', @optname=N'lazy schema validation', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'PROD2K', @optname=N'query timeout', @optvalue=N'0'

    GO

    EXEC master.dbo.sp_serveroption @server=N'PROD2K', @optname=N'use remote collation', @optvalue=N'true'

    GO

    EXEC master.dbo.sp_serveroption @server=N'PROD2K', @optname=N'remote proc transaction promotion', @optvalue=N'true'

    GO

    !!!!!!!!!!!Any help would be highly appreciated!!!!!!!

  • Thank you very much, Nan Tu. This got my code to working. (I just don't really understand it)

  • the links

    [1] Troubleshooting Kerberos Delegation. www.microsoft.com/.../tkerbdel.mspx

    takes you to Windows Server 2012 home page technet.microsoft.com/.../default.aspx

    WHY?

  • Want to run sql queries from my desktop PC (has SQL 2008 R2 Developer Edition) via ServerA (has SQL 2008 R2  Enterprise Edition) on ServerB (has SQL 2008 R2 Enterprise Edition).

    -- From Server A run the following:

    EXEC sp_addlinkedserver @server='LinkedServerB', @srvproduct='SQL Server'

    EXEC sp_addlinkedsrvlogin 'LinkedServerB', 'true'

    -- Lists Linked Server properties

    select * from sys.servers where name='LinkedServerB';

    -- DELEGATION = 1

    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'LinkedServerB'

    -- Test LinkedServerB configuration:

    select * from LinkedServerB.master.dbo.sysdatabases

    -- verify user domain account has permission to access SQL on ServerB

    Open Command Prompt, C:\> osql -E -S NEWCRMCLUSTER

    1> select * from sys.servers where name = 'LinkedServerB'

    2> go

    exit

    -- verify user domain account has permission to access SQL on ServerA

    Open Command Prompt, C:\> osql -E -S NEWCRMCLUSTER

    1> select * from sys.servers where name = 'LinkedServerA'

    2> go

    exit

    Done above steps, unfortunately not done the setspn part yet.

    Server A is on one domain, Server B on another domain.

  • Thanks a lot for this.

    Your stored procedures help me fix my issues instantly.

  • My SQL server 2012 is setup as mixed mode. (Window auth and SQL Auth). I created a sql login 'John-Admin' with cc sa login. and when I try to login with 'John-Admin', it send me error msg "Login failed for user 'John-Admin'. (Microsoft SQL Server, Error 18456)

    1. Need to know what is the code 18456, and where can I find such info.

    2. Need to find the solution for the error.

    Please advise

    John

    Please send me the reply to huangjohn888@gmail.com

Page 5 of 5 (75 items) 12345