Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

Question

Post reply

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

Adam Bean

One Orange Chip

Points: 26631
More actions
February 26, 2008 at 11:26 am

#187026

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

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

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

Adam Bean One Orange Chip Points: 26631 More actions · Answer 1

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

Adam Bean One Orange Chip Points: 26631 More actions · Answer 2

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

Adam Bean One Orange Chip Points: 26631 More actions · Answer 3

Anyone have any additional input on this topic?

Thanks

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 4

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]

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 5

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

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 6

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

Adam Bean One Orange Chip Points: 26631 More actions · Answer 7

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.

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 8

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

Adam Bean One Orange Chip Points: 26631 More actions · Answer 9

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.

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 10

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

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

Adam Bean One Orange Chip Points: 26631 More actions · Answer 11

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.

James Horsley Hall of Fame Points: 3196 More actions · Answer 12

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

James Horsley
Workflow Consulting Limited

Adam Bean One Orange Chip Points: 26631 More actions · Answer 13

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.

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 14

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