Login failed for user ''NT AUTHORITY\ANONYMOUS LOGON''. [SQLSTATE 28000] (Error 18456)

Question

Login failed for user ''NT AUTHORITY\ANONYMOUS LOGON''. [SQLSTATE 28000] (Error 18456)

veera-289275

SSC Veteran

Points: 251
More actions
December 28, 2005 at 12:52 am

#110508

Hi All,
I have an Sp which consists of Linked Server used to connect remote system. If I run this SP
from QueryAnalyzer its working fine. I kept this SP as SchdulerJob and I m started to execute that Job.
It is reporting the following error.
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [SQLSTATE 28000] (Error 18456)
I checked authenticatin modes also(both source and destinatins) all are fine. I gave the owner of the job as also sa. In addition with this I gave the job with Self as Run as User Option
Still its not working
Can any body help me to resolve the solutions

Waiting for immediate reply
veera

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

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

ChrisGorila Mr or Mrs. 500 Points: 561 More actions · Answer 1

Check this

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q237/6/04.ASP&NoWebContent=1

vadba SSChampion Points: 11133 More actions · Answer 2

If you use full-text searching, you must grant the NT Authority\System sysadmin rights to SQL Server if the Builtin/Administrators role is removed:

EXEC sp_grantlogin [NT Authority\System]

EXEC sp_addsrvrolemember @loginame = [NT Authority\System], @rolename = 'sysadmin'

See http://support.microsoft.com/?id=263712 for the full article.

Kuljeet Valued Member Points: 69 More actions · Answer 3

Judging by the timestamp of your post, chances are that you would have aleady found a solution to your issue. If not, here is what you can try.

I was facing a similar issue where in I had a SP which created a linked server, then executed some remote queries and finally dropped the linked server. This SP was working fine when executed from within Studio Manager (I'm using SQL 2005) how ever as soon as I tried putting it in a job it failed with the exact same error message.

I discoverd the issue to be this: The SQL Server Agent on the host machine (my desktop) was configured to run under the "Local System" account. Thus the job was getting executed under the "NT AUTHORITY\SYSTEM" account which ofcourse can not be used to connect to another SQL Server on the domain as it is a 'local' account.

The resolution is to configure the SQL Server Agent services to run under a service account which has the appropriate rights on the remote (linked) server.

Hope this helps.

Regards,

Kuljeet

Ashok Kumar-464661 SSC Enthusiast Points: 103 More actions · Answer 4

Hi All,

I have a SP which consists of Linked Server used to connect remote system. I am calling a SP present in remote system thru linked server and when i execute that i get the following error Login failed for user ''''NT AUTHORITY\ANONYMOUS LOGON''. Both the servers a present in same domain and are running under Network Service Accounts.

Can any one help me in resolving this issue.

Thanks in advance..

Kumar

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

Change the network service account to a domain account in both the servers. This is because of hop issue.

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

Hamilton Fong-245421 Valued Member Points: 73 More actions · Answer 6

Hi Sugesh,

Can you elaborate on how to change the account?

Thanks,

Ham.

subhad SSC Enthusiast Points: 158 More actions · Answer 7

I fixed my issue with LOGON failure for NT AUTHORITY\SYSTEM... which was an error when I scheduled a SSIS . My SQL Server Agent is using NT Authority account

I added NT AUTHORITY as a user to SQL Server 2005

Created one credential with my userid and password

used that credential to create a proxy account

added NT AUTHORITY to the "Prinicpal" window( not needed if the user is already has sysadmin)

On the Step I used this Proxy account to "Run as"

Make sure the account you are using as credential is added to the other server as administrator

Hopefully it works for you

peace2007 SSCrazy Points: 2154 More actions · Answer 8

I'm having the same problem subhad; could you clear me about followings?

"I added NT AUTHORITY as a user to SQL Server 2005"> I have NT AUTHORITY\LOCAL SERVICE, and NT AUTHORITY\NETWORK, NT AUTHORITY\NETWORK SERVICE, and NT AUTHORITY\SYSTEM is that enough or I should create NT AUTHORITY by itself. Besides, how I can configure which user my sqlagent is running under?

" Created one credential with my userid and password" > how?

Thanks in advance

prasanna.gawade-969214 Valued Member Points: 69 More actions · Answer 9

i m also facing the same issue...

please letme know if solution works....

pureaqua SSC Enthusiast Points: 184 More actions · Answer 10

Check this out:

http://blogs.msdn.com/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 11

just had this happen to me

i'm setting up backup monitoring like in the article from a few weeks ago. not using a powershell script because i have some old clients. just setting up SQL jobs with the raw code. tried the code on my laptop and got the error. RDP'd into a server and tried it from there and it worked.

looked it up and it's the double kerberos jump like someone said earlier in the thread.

mcTDBA Default port Points: 1458 More actions · Answer 12

I know this thread is old, but I am going to share what worked for me.

Disable all network Protocols execpt TCP/IP on the instance you are trying to make a linked server connection to. Bounce the service and make the linked server connection using current security context.

I make it a best pratice to on all installs to:

1. Disable the browser service.

2. disable all network protocols, but TCP/IP.

3. Change Defalut Port. (Setup Alias)

James Zimmerman SSC Veteran Points: 297 More actions · Answer 13

James Zimmerman

SSC Veteran

Points: 297

March 2, 2010 at 1:55 pm

#1127407

I have overcome this problem by restarting my PC.

kmdeepika 44412 SSC Rookie Points: 34 More actions · Answer 14

Hi,

solution for this issue is give remote login user id and password in linked server properties.

1.Add linked server using sp_addlinkedserver 'server name'

2.Right Click -->Properties --> Security -->Add --> Give Remote User & password for Local Login

3.Below that u can find a text 'For a login not defined in the list above,Connection will' with four radio button options

Select last option - Be made using this security context Remote Login with password.

This simple solution worked for me.

Thanks

Deepika