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


SQL Server 2005: “Login failed for user ‘NT AUTHORITYANONYMOUS 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 AUTHORITYSYSTEM” or “NT AUTHORITYNETWORK 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 AUTHORITYSYSTEM”, 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


Comments (78)

  1. Matt Piskorz says:

    Nan,

    Thank you for the article.  Can you talk a little bit about when you have trusted domains.  Our cluster resides in a "Domainlet" and we are using trusted domains.  We are having some problems getting our linked server to use domain authentication.

    Matt

  2. In this post, I focus on how NTLM and Kerberos are applied when connecting to SQL Server 2005 and try

  3. Alon says:

    Hi,

    on the end of stage [Create Linked Server Object on Middle Server] – I got 0 and not 1, any idea of what should I do now?

  4. Radamante says:

    Hi Nan,

    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 and Dev (some of them are SQL 2000 and some SQL 2005) using double-hop.

    Between servers placed in Dev domain I don’t have any problem, but when I try to link servers from Dev to Prod or viceversa or between servers in Prod, I receive the next message for SQL Server 2000:

    OLE DB provider "SQLNCLI" for linked server "192.168.1.11" returned message "Communication link failure".

    Msg 10054, Level 16, State 1, Line 0

    TCP Provider: An existing connection was forcibly closed by the remote host.

    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:

    Msg 18456, Level 14, State 1, Line 1

    Login failed for user ‘NT AUTHORITYANONYMOUS 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,

    Radamante

  5. Matt Neerincx says:

    Note that this error:

    OLE DB provider "SQLNCLI" for linked server "192.168.1.11" returned message "Communication link failure".

    Msg 10054, Level 16, State 1, Line 0

    TCP Provider: An existing connection was forcibly closed by the remote host.

    means that the remote host RESET the socket from client to server.  This can happen for example if domain2 does not trust domain1 and IPSEC is enabled in your domain.  IPSEC can automatically RESET untrusted socket attempts.

    The other two errors are consistent with double hop failures.

    With these types of errors the basic rule of thumb is be systematic and eliminate one leg at a time.  Since you can get "TCP – KERBEROS" I am assuming this is from the first leg, so now you need to troubleshoot the second leg.

    Start on the middle SQL Server and use OSQL or SQLCMD to attempt to connect to the next SQL, does it work?  Log onto the middle SQL Server and connect to SQL locally, then try linked server query, does this work?  Etc…

  6. Michele Adams says:

    I am still experimenting, but it seems on clustered instances it’s necessary to have each physical node’s computer accounts in AD have the "trusted for delegation" as well as the service’s user account.  

  7. I have tried this query on sql 2005 (select net_transport, auth_scheme from sys.dm_exec_connections where session_id=@@spid)… it works.

    On SQL 2000, it fails not knowing the sys.dm_exec_connections

    Can you  give me a SQL2000 query to get the needed result?

  8. k says:

    What about Linked server login using SQL login?

  9. k says:

    OK I get it, I have to enter Remote login and password and not map anything

    I get the list of remote tables in Enterprise manager but select from "main" serevr to linked servers tables returns "The operation could not be performed because the OLD DB provider ‘SQLOLEDB’ was unable to begin a distributed transaction

  10. Wendy says:

    Hiya,

    I am doing a double-hop and I followed all the steps up to point 7 where I was to 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.

    All steps prior to this were set up correct.  But when I run this query I get this for both stages:

    net_transport   auth_scheme

    ————-   ———–

    Shared memory   NTLM

    Help!

    Ta

    Wendy

  11. Matt Neerincx (MSFT) says:

    Try running dbcc freesystemcache(‘all’) to free up pooled connections on the server side, then reconnect again.  I think what is happening is you are picking up a pooled connection that you made locally.

  12. Wendy says:

    Hiya,

    Tried this and am now getting:

    From Client to Server1:

    net_transport   auth_scheme

    —————– —————

    TCP                 KERBEROS

    From server1 to Server2:

    net_transport   auth_scheme

    —————– —————

    Shared memory   NTLM

    Any ideas?

    Thanks

    Wendy

  13. SQL Protocols says:

    Wendy,

      I think your case is a single hop setup, i.e. the two server is on the same box. On the same box, shared memory and NTLM are given priority and your linked server query should be ok.

  14. I have 10 servers.  

    8 are SQL 2000

    2 are sql2005 (A,B)

    2 are Clustered Sql 2000 (C,D).

    I have Query Analyzer on my desktop (Q).

    Single hop use of the linked servers works in all cases.

    Connecting through my desktop (Q):

    through server ‘A’ to double-hop to all servers works…except the clusters (C,D)

    ‘B’ can use linked servers to all servers including the clustered pair (C,D)

    Why would the clustered servers provide the dreaded Msg 18452 to Server ‘A’ and not to server ‘B’?

    I have carefully followed all the prescriptions in your fine article including checking the net transport.  I have carefully checked the Linked servers (and rebuilt them).

    Can you give me a pointer?

  15. SQL Protocols says:

    Hi, Steve

       The case is interesting. Can you provide more detail about the linked server deployment? Basically, how do you configure linked server from C,D to A,B? You client query running on Q, so it was double-hop, correct? and whether you double checked that Q->C, C->A or Q->D , D->A were using Kerberos over TCP? What is the configuration difference of A and B(SQL 2k5), whether they are same SKU? same default instance? Whether A and B are on the same box? With same OS?

    Thanks!

    Ming.

  16. Ming,

     First, a general observation, resulting from more testing on my part:

    2005 server StdEd sp1 (A) can’t seem to double hop to any named instance (either 2000 or 2005).  2005 server Ent Ed SP1 (B) can double hop (pass credentials) to any of my 40+ servers (default or named instance).

    To answer your questions:

    1.  Same script created linked servers on both ‘middle servers’ A & B.  Both have 4 linkedserver accounts authorized for ‘impersonation’…one of those is the ‘serviceaccount’.  All linkedservers are set this way.

    2.  Client was queryanalyzer or SSMS on my workstation, so yes double-hop was required.

    3.  Yes, from my workstation using q/a or SSMS, i can log directly to any of the servers…

    4.  I don’t know how to check that i’m using kerboros from my workstation out.

    5.  Both A, B are: WinSrv2003 SP1 Std Ed.

    6.  A is 2005 SP1 Std Edition

    7.  B is 2005 SP1 Ent Edition

    8.  A & B are on different boxes.

  17. Ming,

     Another point: I logged on to my workstation as the SQLService account.  (The same SQLservice account is used to run SQl server and the agent account.)  I opened an SSMS session query to Server A.  

    1. Select * [CI1].master.dbo.sysdatabases  FAILED (msg 18452)

    2. Executed a storedProc which has (different) distributed query to [CI1].  SUCCEEDED!

    3. Re-ran query 1…SUCCEEDED!

    4. Waited 10 minutes. Re-ran query 1…FAILED.

    Can you help me understand what is happening here?

  18. Matt Neerincx (MSFT) says:

    Hi Steve,

    This sounds like linked server connection pooling is making the hop succeed.  To prove if this is the case, try running:

    DBCC FREESYSTEMCACHE (‘ALL’)

    After step 2.  If this causes step 3 to fail, then you are just reusing the authenticated connection you created in step 2.  Pooling auto shuts down the connection in around 8 mins so this is why it fails later.

  19. WendyO says:

    This double-hop kerberos issue with linked servers in SQL2005 is an actual MS bug and is resolved with SP2:  http://support.microsoft.com/default.aspx?scid=kb;en-us;921896

    or hotfix 925843 (http://support.microsoft.com/kb/925843/)  

    Cheers

    Wendy

  20. stephend@coj.net says:

    Matt, Thanks for your comment.  That did help me understand the connection pooling issue.

    Ming, (or Matt)

    Could there be a difference in SQL server versions linked server functionality?  My 3 SQL 2005 Enterprise versions are all able to double hop to named instances.  The Standard Edition server is only able to double hop to default instances.

    Thanks for any input!

  21. stephend@coj.net says:

    Wendy,

     I didn’t see your comment until after i posted my last note.  To be more specific: my 2005 Enterprise versions can all double hop to 2000 named instances.  My one 2005 Std edition can only double hop to 2000 default instances.  The fixes you refer to relate to 2005 to 2005 double hopping.

    Thanks.

  22. Lyn Duong says:

    I am experiencing the same problem. Only the edition I have is Developers Edition. I applied service pack 2 and am still getting the error when I try to run a distributed query from my pc to a SQL server 2005 (developer’s ed) that has a linked server to a SQL server 2000 (standard edition).

    Does anyone know if it’s because of sql editions that this problem occurs.

    Thanks

    Lyn

  23. BANJI says:

    I AM CREATE USER ACCOUNT BUT GOT THE MESSAGE "CANNOT FIND LOGON CODE <USER NAME> ON THE DATABASE WHIL CONNECTING FROM THE USER PC.PLS WHAT DO I DO TO SOLVE THIS PROBLEM

  24. Mireya says:

    I have an "Authentication scheme" double hop.

    When I make my connection from the client to server "A" and then by linked server to server "B", it doesn´t work, when I run the query "“select net_transport, auth_scheme from sys.dm_exec_connections where session_id=@@spid” I have: NAME PIPES/NTLM.

    In the other hand when I make the connection from the client to server "B" and then by linked server to server "A", it works, and my net transport is TCP/KERBEROS.

    How can I change the net transport of my server "A" to TCP/KERBEROS?????

    Thanks….

  25. Artur says:

    Thank you for this useful post.

    Is there any special cases for 64bit Sql Servers?

    I have configured the system using this article. All the servers now looks good, and only x64 machine does not use kerberos while connecting to other servers.

    Any ideas?

    Thanks in advance

  26. Thet Su says:

    more specific answer for "Login fail NT AuthorityNetwork Service"

  27. Jason says:

    I finally figured it out.  Here is the simple answer:

    On server B, the server where you add the linked server, go into SQL Server configuration manager and disable Named Pipes under SQL Server 2005 Network Configuration > Protocols for MSSQLSERVER.

    You should only have Shared Memory and TCP enabled under this setting.  

    After I made this change, I was able to execute a linked query from server C to A.  

    Jason

  28. Jason says:

    Also, running

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

    from Server B should yield Shared Memory and NTLM.

    running it from Server B should yield TCP and NTLM.

    I hope this helps.

    Jason

  29. Jason says:

    Correction:

    running

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

    from Server B should yield Shared Memory and NTLM.

    running it from Server C should yield TCP and NTLM.

    I hope this helps.

    Jason

  30. zamo says:

    Double hopes are working fine in my environment using Domain Authentication. They connect as KERBEROS. The issue is that after 8 hours it seems that the Token expires and the connection falls back to NTML. At that point double hop does not longer work. I need to close the connection and reopen.

    The issue I am facing is that it is not possible for us to reset a database connection from a .NET application, and seems that some of those connections are lasting over 8 hours.

    Any advice?

  31. 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

  32. Wendy says:

    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

  33. Suman says:

    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 AUTHORITYANONYMOUS 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

  34. Zee says:

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

  35. Rashid says:

    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….

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

  37. Dave says:

    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.

  38. Matt Neerincx (MSFT) says:

    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.  

  39. Nimish says:

    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.

  40. Roland says:

    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

  41. Iain says:

    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!

  42. Brother Jay says:

    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

  43. Burt says:

    I had the same problem "Login failed for user ‘NT AUTHORITYANONYMOUS 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.

  44. Bob says:

    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?

  45. SQL Protocols says:

    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

  46. Douglas Taft says:

    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?

  47. Wolfgang says:

    Hi Nan Tu,

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

    thank you so much and many greatings from AUSTRIA !

  48. Manishankar says:

    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

  49. Mohit says:

    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

  50. Pawel says:

    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

  51. Chris says:

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

  52. Raju says:

    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 AuthorityAnonymous 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

  53. Brent says:

    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

  54. Andrew says:

    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.

  55. Srinivasa Moorthi says:

    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

  56. Brent says:

    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.

  57. Arburodi says:

    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?

  58. Gyne says:

    Hi,

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

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

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

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

    Состояние: 1

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

    What should I do?

  59. John says:

    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 AUTHORITYANONYMOUS 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?

  60. John says:

    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!

  61. Chris says:

    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 AUTHORITYANONYMOUS 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

  62. John says:

    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.

  63. Tinhvn83 says:

    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.

  64. Mark says:

    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 AUTHORITYANONYMOUS 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

  65. ajaymalloc@hotmail.com says:

    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!!!!!!!

  66. jp2code says:

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

  67. steve giergiel says:

    the links

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

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

    WHY?

  68. kevin says:

    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.

  69. Ahmed says:

    Thanks a lot for this.

    Your stored procedures help me fix my issues instantly.

  70. John Huang says:

    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

  71. V S says:

    Thanks for the article. Following step by step helped me solve Linked Server issue for double hop setting.

  72. Bill says:

    Sorry, but this is just all too damn complicated.

  73. Milind says:

    Thank you  for the article….it's really very very help full….