Why am I getting an NT AUTHORITYANONYMOUS error when creating or altering a stored procedure?

,

If you have to deal with linked servers then you probably have or will run into the following error:

Login failed for user ‘NT AUTHORITYANONYMOUS 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.

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

Share

Share

Rate