Why am I getting an NT AUTHORITY\ANONYMOUS error when creating or altering a stored procedure?1
May 8, 2019 by Kenneth Fisher
If you have to deal with linked servers then you probably have or will run into the following error:
Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’
But I’m not trying to use the linked server. I’m trying to create/alter a stored procedure.
Yea, but when you do that, if a linked server is referenced in the code, the parser(?) is going to go out and check and see if the objects you are referencing exist. In fact, you can run into any number of different linked server errors, not just this one.
So what do I do?
Simply put whatever account you are using to do the create/alter has to be able to check across the linked server. Note: this could be any type of code, it doesn’t have to be a stored procedure, it could be a function or whatever. The solution is going to depend somewhat on what position you are in.
- Kerberos is working, but you don’t have access to the linked server.
Get access either through your account or possibly a SQL Server Id, or have someone who has access do the work. I have on occasion set up a new SQL Id on both sides of the linked server and the logged in as that to create/modify the SP. Then I remove those IDs. But then again, I’m a sysadmin with a lot of experience scripting creating SQL Ids.
- Kerberos isn’t set up because you are using SQL Ids
See above and please reconsider your life choices
- Kerberos is set up but broken
Fix Kerberos. This will take longer but if Kerberos isn’t working you aren’t going to be able to run the stored procedure anyway.
- The linked server is broken for some reason other than Kerberos.
Again, see above.
[…] Kenneth Fisher takes us through a security issue: […]