Post reply

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

  • March 30, 2006 at 8:16 am

    #112607

    Afternoon All,

       Ah! I hear you all sigh, that old chestnut. Just go and check the permissions to solve the problem.

       Well. I have run out of things to check! The situation is as follows.

       I have a user on server 3 with a login  of domainName/username. He also has exactly the same login on server 1.

       Server 3 has a linked server of server 1 and when I look at the linked server properties I see that the same login is shown, with the impersonate box ticked.

      The user is listed as dbo on the server 1 Database that is to be interrogated.

       The following query is run "select all from server1.database.dbo.tableName" and this produces the error in the heading.

       As the person in question has a login on both servers, is listed in the linked server and is the dbo of the table being queried I cannot see why SQL returns "Anonymous login" and the query fails.

      Clearly I am missing some vital stage, but no amount of googling produces any help. I therefore throw myself on the tender mercy of this group, which is the best I have dealt with!

    Thank you

    Colin

  • March 30, 2006 at 8:51 am

    #629889

    Hi,

    I always make the following error:

    I do have good user mappings. Say, mylogin to mylogin, or mylogin - impersonate etc. in the Linked Server Properties. BUT... I have a server (server3 in your case) registered in Enterprise Manager as login2. So I open my EM with a focus on Server3 ->Tools->Query Analyzer and run a query. And it will fail because the query window after all my actions opens as Login2 as a security context. And this Login2 does not have any mappings. The resolution is not to skip steps and in the Query Analyzer use File->Connect and connect to the Server3 as a login that has valid mappings in Linked Server properties for Linked Server1. Or you may change Server3 registration properties to be registered in the same security context as the person who has mappings in Linked Server Properties

     

    Regards,Yelena Varsha

  • March 30, 2006 at 9:15 am

    #629899

       Thanks for the quick response. However, this does not solve the problem. The top margin of QA always shows the server name that it is logged into and iit clearly shows server 3. All logins are Windows authentication.

        Is there a way to find the login that is being used by QA? That would assist in testing your point.

    Colin

  • March 30, 2006 at 9:20 am

    #629901

      Have just spotted the Window drop down menu which has an option WINDOWS. This confirms that the user login is the same as that shown on both servers and in "linked server". Looks like the issue lies elsewhere.

    Colin

  • March 30, 2006 at 9:59 am

    #629919

    This is what I found so far except for Impersonate advice:

    this is from

    http://www.talkaboutsoftware.com/group/microsoft.public.sqlserver.connect/messages/41047.html

    who is replying to her own question:

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

    by "michelle" <michelle@ > Feb 9, 2005 at 08:27 AM

    "..........

    There was a kerberos service on the domain controller that was not started.Starting this service resolved the issue. I believe that there is a KBarticle on this but unfortunately, I was unable to locate it myself."

    Regards,Yelena Varsha

  • March 30, 2006 at 10:44 am

    #629933

    This sounds like a "double-hop" issue. When a client goes from the workstation to the first

    SQL Server, that's the first hop. When you go from the first SQL Server to the second SQL

    Server, that's the second hop. Under NT4.0, the only authentication method allowed is

    NTLM. NTLM did NOT allow double-hops. We used to see these issues all the time before folks

    started converting to Active Directory.

    Active Directory attempts to use Kerberos as the authentication method first. Failing that

    it drops back to NTLM to support legacy clients. However, unless you specifically make

    some configuration settings, Kerberos does NOT allow double-hops, either. Kerberos does do

    so through delegation, but you have to do some setup. Here are the basic steps, but

    before going through them, make sure everyone involved has read the appropriate documentation

    on Kerberos delegation. Kerberos delegation adds risk to your security posture and should

    not be done haphazardly.

    1) You have to configure the first SQL Server as being allowed to delegate. This needs to

    be done by your directory (domain) admin. In Windows 2000 Active Directory it's simply a

    checkbox. In Windows 2003 Active Directory there's the concept of constrained delegation

    and so there a whole lot more settings.

    2) You have to configure the service account on the first SQL Server to be allowed to

    delegate. You also need this done by your directory (domain) admin. There is an option

    under the Account tab saying "Account is trusted for delegation" that must be checked.

    3) The SPNs on the SQL Servers must be set properly. Here's a knowledgebase article that

    details how to do it. Again, directory (domain) admin help needed.

    How to use Kerberos authentication in SQL Server (319723)

    There are additional articles that may provide some help:

    How to troubleshoot the "Cannot generate SSPI context" error message (811889)

    How to make sure that you are using Kerberos authentication when you create a remote connection to an instance of SQL Server 2005 (909801)

    Oh, and one more thing... kerbtray is your friend when troubleshooting.

    K. Brian Kelley
    @kbriankelley

  • August 1, 2006 at 1:14 pm

    #652633

    Just an FYI...I currently have a support case in with Microsoft about this issue.  Although it may not be the same scenario, we started experiencing this problem when we updated our "middle-hop" SQL server to a SQL 2005 x64 machine.  I think it has something to do with the 64-bit part, but I can't be sure.  We've almost exhausted all available troubleshooting options. The only odd thing is that the double-hops will work for a period of time (from 10-minutes to several weeks) after the SQL service is restarted.  The case is still open, so I'll post any solutions...


    -Dan

  • September 8, 2006 at 12:06 pm

    #659427

    dj meier I'm currently experiencing the same issue with SQL 2005.  Although, it's not the 64 bit version.  I have a linked server set up on Server A (2005) that points to Server B (2000). 

    I have sql 2005 installed locally and Server A is a registered server.  When I execute a query locally on Server A that references my Server B I get the error.  But if I execute the query directly on Server A (logged in to the physical machine) I don't have any problems. 

    I'm not sure if this is a double hop issue or not.  I've never had this problem when all my machines were running SQL 2000.  Any word from Microsoft?   Any help is much appreciate.

  • February 23, 2007 at 8:40 am

    #691425

    dj & Jason,

      We also are experiencing this issue.  How did you resolve it?

     

    i.e. consider:

    A. Workstation w/Query Analyzer

    B. Server2005 with a linked server pointing to...

    C. Server2000 with data in a table

    Scenario1:  Sitting at A, query thru B to C fails

    Scenario2: Sitting at B, query thru B linked server to C succeeds

    Scenario3: (after scenario2 is executed); Sitting at A, query thru B to C succeeds.

    Scenario4: (restart sesssion/server) Sitting at A, query thru B to C fails

     

  • February 23, 2007 at 9:04 am

    #691439

    Hi,

    This issue had to do with a double hop scenario, where windows can't pass

    along the windows based authentication.  I found the following link

    very helpful in understanding this issue (nice diagrams with detailed

    explanations).

    http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerbdel.mspx

    Solution:

    First, setup a SQL Login with the appropriate permissions on the "linked server". Then use the following extended proc on the server that connects to the linked server via query:

    EXEC sp_addlinkedsrvlogin 'linkedservername', 'false', 'Domain\Jay',

    'sqllogin', 'sqlpassword'

    This will configure the server to use the sql login and password to

    connect to the linked server when user Domain\Jay is logged on.  Hope

    this helps.  Let me know if you have any trouble with this, I'll gladly help out.

    Jay

  • February 23, 2007 at 9:27 am

    #691457

    Thanks, Data Demon for the link and the idea.  However, we are very much hoping to work this Double Hop issue out using Windows Authentication.  I could also have pointed out that we are introducing SQL 2005 into a group of 40+ SQL 2000 boxes.  We are hoping to use theSQL 2005 box(s) to link to all the 2000 boxes for management/reporting via linked servers.

    We have gone thru the ringer getting account delegation set up and want it to work on the 2005 boxes.

     

    There is a nice summary of how to do this here:  http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=59204

    But, there seems to be some additional trick on Win2003/SQL2005 boxes.

    Steve 

  • October 2, 2009 at 11:20 am

    #1061088

    K. Brian Kelley, thanks a lot. That was the issue for me.

  • March 18, 2011 at 3:17 pm

    #1300828

    WOW, this is interesting I though would be good to share it since it is related.

    SQL 2005 SP3, Server A has link server to Server B (be made using current Security context)

    running a link server query within Management studio (with My Domain account) against Server B like this.

    (1) My workstation-->server A-->Link server B---failed

    (2) on Server A--> Link Server B--> success.

    until now it is OK, I know it is double hub and ..........

    if I do step (1) again after step (2) now I am getting success!!!

    if I try step (1) after few minutes getting fail again!!!

    so step (1) only succeed if I do step (2) first???/

    what the ........

  • March 21, 2011 at 6:36 am

    #1301235

    shaneat,

    When you log on in step 2, you are on Server A and logging on to Server B. In doing this, Server A caches the credentials to Server B (typically for around 10 minutes).

    When you then (remotely) login to Server A and execute a linked server to B, Server A is using the cached credentials from the earlier connection to B. You can only do this for a limited amount of time. The Kerberos set up is still not accurate.

    Steve

  • April 12, 2012 at 9:07 am

    #1472658

    Can you please explain in detail how to implement your 3 options you mentioned ?

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply