Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

  • I'm at a loss here ...

    I can use an OPENDATASOURCE query with integrated security from my local instance with my SQL Service running either as Local System, my credentials, or my SQL Service Account ... all of them work fine.

    Yet when I run my query from my control SQL Server, I get Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. The server and agent are both running under my domain sql server user, which is a local admin on the machine.

    The crazy part is that if I put my query into an agent job and run it, works perfect. The jobs are owned by sa, meaning it would be using the account running SQL ... which is the same account that is running SQL and the Agent on this server.

    That and If I run SSMS as the service account in question and attempt to run my query, same result ... failed.

    Any thoughts?

    Thanks

  • Whoops, someone else posted the same topic title before me ... my apologies. It won't let me edit it ...

  • Think I found my issue: http://support.microsoft.com/kb/q238477/

    If I run the query from the server, it works (wasn't before, I changed something).

    Anyways, I enabled named pipes without restart SQL and it worked for a few minutes ... now it does not. The hell is going on here? I'm sure I need to restart SQL ... but is there something else I'm missing here?

    Thanks

  • Anyone have any additional input on this topic?

    Thanks

  • I've had the same error message using Reporting Services:

    see Post #449612 (the 2nd post),

    http://www.sqlservercentral.com/Forums/Topic449604-147-1.aspx

    I haven't been able to resolve this issue yet.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • Using a linked server and configuring it to use alternate credentials might work; use OPENQUERY instead of OPENDATASOURCE in that case.

    The "NT Authority\ANONYMOUS" failed login looks like an authentication issue. Check if the connection is using NTLM instead of Kerberos. To find a connections authentication type query the sys.dm_exec_connections DMV and look at the auth_scheme column.

    If it's authentication related these articles might help:, Details on 18456 messages

  • THis is a HOP problem. You need to run your sql services in a domain account to access the network resources. Else this will fail as the local system account can access only the server resources and not the network resources. also check if the resources havee necessary permisssions.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • Sugesh Kumar (2/28/2008)


    THis is a HOP problem. You need to run your sql services in a domain account to access the network resources. Else this will fail as the local system account can access only the server resources and not the network resources. also check if the resources havee necessary permisssions.

    Yep, as stated above, they are running under domain users. Also, I am able to successfully do the openquery with running SQL as local system as well.

    The problem seems to only present itself on a particular server, which does have it's service running under a domain user, which is a local admin to all my SQL Servers. The only time it worked was when I enabled named pipes, but only worked momentarily.

  • OK you mean to say there is a server which runs on local adminstrator account and yes exactly these cause the problem. you need to cahnge them to run in domain account or atleast have them use the same user name and password as the domain account has so that youu can avoid the problem.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • Sugesh Kumar (2/28/2008)


    OK you mean to say there is a server which runs on local adminstrator account and yes exactly these cause the problem. you need to cahnge them to run in domain account or atleast have them use the same user name and password as the domain account has so that youu can avoid the problem.

    No no, ALL the servers are running the SAME domain user as the service account, which is a local admin on each of the machines.

    I can run my openquery from my local server, which is running under local system. But I can not run my openquery from any other location. The servers run SQL as the domain user. So this made me think that it had something to do with the service account ...

    I changed my local server to run under the same service account, works just fine ...

    So next, I remoted into servers, ran my query from the local server, and they worked just fine. That lead me to find the article about the double hop thing.

  • So You have sorted this problem out with changing to a domain account.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • Sugesh Kumar (2/28/2008)


    So You have sorted this problem out with changing to a domain account.

    Unfortunately no ... nothing has changed. My openquery does not work against a destination server, unless I'm running it locally from the server. This double hop thing is the issue. I just don't understand why it works locally and not remotely.

  • Is one (or both) of the servers Win 2000 Server or are both Win 2003?

  • James Horsley (2/29/2008)


    Is one (or both) of the servers Win 2000 Server or are both Win 2003?

    All the servers are 2k3, my local is XP.

  • Do you have distributed transaction coordinator security turned on the server. Check that might cause issues with Win 2003 and also check if the domain user has rights to access the file in your local XP machine meaning NTFS permissions.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

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

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