Login failure for user 'NT AUTHORITY\ANONYMOUS LOGON'

Question

Login failure for user 'NT AUTHORITY\ANONYMOUS LOGON'

rjoseph

Ten Centuries

Points: 1087
More actions
December 16, 2013 at 6:05 am

#395992

Would someone help with error message that a developer kept getting when editing some custom made stored procedures and trying to execute them.
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
I googled it and some sugest that to create user 'NT AUTHORITY\ANONYMOUS LOGON' and give execute access to it on these stored procedures and I did and still does not work. Some suggest to also modify the registry by entering a key in the Lsa "TurnOffAnonymousBlock" with a value of 1 and reboot the server. I did that and still does not work.
I don't know what else to do. Your help will be greatly appreciated.
Regards

Viewing 8 posts - 1 through 7 (of 7 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

look at the stored procedure in question...i'd bet it is referencing a linked server. the login is failing at the linked server because whoever is calling it doesn't have mapped credentials set up.

accessing a linked server is probably being blocked by how it is set up; it's probably set up witht eh radio button below that says "be made without a security context"

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rjoseph Ten Centuries Points: 1087 More actions · Answer 2

Thanks very much for replying. I have used the sa account by logging as the sa and try to execute the stored procedure and still does not work. I have realized, it does not matter what user is used, it is problematic and that is what puzzled me about this.

Lowell SSC Guru Points: 323518 More actions · Answer 3

whoops i cross posted from anotehr post;

can you show us /script out your linked server?

that would help us diagnose the issue for you.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SumLasty SSCommitted Points: 1923 More actions · Answer 4

Check the Login failure error from sql error log and identify the status there after.

This may help

http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx

http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx

rjoseph Ten Centuries Points: 1087 More actions · Answer 5

Hi Lowell,

You were right on the money. It was the linked server remote userd on the other side (in Europe) that did not have the right access. That user was mapped as "public". After I called the DBA in Europe to map that remote linked server user with read access to the proper database, all was fine.

Thanks very much for your help, it was very much appreciated. Kudos to you.

Regards,

Reynold

Pravinkumar Newbie Points: 1 More actions · Answer 6

Hi,

I am getting same Login failure for user 'NT AUTHORITY\ANONYMOUS LOGON' while trying to access SQL database from my laptop. (I connect to Server A where there is link server query written and it's trying to access server B. So, t's double hop from my laptop).

But Strange thing is when I remote log in to Server A and run that SP, it runs without an error. Immediately after running SP manually on Server A, If I try to run an application from my laptop it works fine. But it starts giving an error after 20-30 minutes. This is work around I am using for now. But I already confirm that SQL service has administrator rights on both Server A and Server B.

Thanks in Advance.

Pravin.

Ant-Green SSC Guru Points: 113507 More actions · Answer 7

You have a Kerberos issue, ensure all the SPN's are setup, delegation is setup and all the user mappings are setup correctly.