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.

One thought on “Why am I getting an NT AUTHORITY\ANONYMOUS error when creating or altering a stored procedure?

  1. […] Kenneth Fisher takes us through a security issue: […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,528 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: