none
Linked Server error: Login Failed for user 'NT AUTHORITY\ANONYMOUS LOGON' RRS feed

  • Question

  • Good afternoon,

    I have the following issue that i can't seem to "fix":

    I have 2 instances: Instance A (primary databases for my application) and Instance B (relational databases for Reporting Services).
    Each instance is installed on a separate machine and both are included in the same domain.
    I can access Instance A from Machine B and i can access Instance B from Machine A (with both SQLServer Authentication and Windows Authentication)... no problem here. 
     
    I've created a Linked Server connection from Instance B to Instance A without a problem. I can access all my databases from instance A in this linked server.
    The problem is creating Linked Server from Instance A to Instance B. I get no error creating it, but i can't access any databases from Instance B.
    I get the following error: Login Failed for user 'NT AUTHORITY\ANONYMOUS LOGON' (Microsoft SQL Server, Error: 18456).

    I've been reading about this in some forums but no solutions seem to help...

    Any help please? 


    Thanks in advance for any help,
    SuperJB 

    JB
    Friday, February 18, 2011 2:57 PM

Answers

All replies

  • Friday, February 18, 2011 3:11 PM
    Moderator
  • I know this is old but I'm having the same problem and the SPNs appear to be correct. Client C1 (ssms) connects to server S1 and uses a linked server to server S2. The caveat here is that S1 and S2 are in different domains and forests. There is a two way trust between those domains.

    Any ideas?

    I've verified that all SPNS are registered correctly and there are no duplicates. 

    S1 is sql2012, S2 is 2008 R2. 

    C1 can connect to both servers using the same Windows authenticated account, and I've verified both conections get Kerberos authenrication. S1 is trusted for delegation in AD. 

    S1 is also a clustered instance and all nodes of the cluster that can own that instance are trusted for delegation.


    Chuck

    Monday, August 19, 2013 9:13 PM
  • What you describe is a "double hop" problem.  http://blogs.technet.com/b/askds/archive/2008/06/13/understanding-kerberos-double-hop.aspx

    You should setup a SQL login on the destination to make it work, it will be easier.


    Monday, August 19, 2013 9:48 PM
    Moderator
  • You should still be able to do double hop if all the stars are aligned correctly. I believe they are but its still failing. I'm wondering if its failing because the two servers are in different domains and forests. Ive got it to work before with windows authentication but only when the servers were in the same domain.

    Chuck

    Monday, August 19, 2013 11:45 PM
  • Found the problem. The SQL Server service account was not trusted for delegation in AD. We made it trusted and the double hop authentication started working. Even across domain boundaries.

    Chuck

    Wednesday, August 21, 2013 12:59 PM
  • Chuck, Can you please explain how you did this "We made it trusted"

    -Neel

    Wednesday, September 11, 2013 6:55 AM
    1. Find the service account used to start SQL Server by going into the "services" app on the server. Go to the "log on" tab to get the service account name.
    2. Open "active directory users and computers". That applet is only installed by default on domain controllers. You can download it and install it on any machine however by installing RSAT.
    3. Right click the domain and select "find". Search for the service account name.
    4. Double-click the service account and go to the "delegation" tab.
    5. Ensure that "Trust this user for delegation to any service (Kerberos only)" is selected.
    6. Do the same for the server's computer object in AD.
    7. For the user logging into SQL and  trying to do the double hop, find/open his AD account, go to the "account" tab and ensure that the "account is sensitive and cannot be delegated" option is not selected

    Remember too that changes made in AD can take some time to replicate to all DCs. I'd give it 30 minutes.

    HTH


    Chuck


    • Edited by chuckh1958 Wednesday, September 11, 2013 5:57 PM
    Wednesday, September 11, 2013 5:55 PM