This week, in this blog post let me quickly show you resolving openrowset error in SQL Server. Last week a friend of mine call me and asked for a help, he was using an openrowset since long, however he got the error first time. The openrowset was complaining about the login failed error.
Brief about an issue:
He shared his screen and showed me what he was doing, the commands etc. which was good, no issues with command per se. However it was throwing the login failed error 18452. And then, we ran a check on the permissions, which was properly assigned based on what access was needed. After a little research what it turned out to be an issue with Server Principal Name aka SPN. The server is newly configured machine, and probably a part to configure SPN was missed.
Error: 18456, Severity: 14, State: 11. Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’
Resolving OPENROWSET error in SQL Server:
Server Principal Name aka SPN is helpful to get SQL Server service account to authenticate using Kerberos protocol over the domain. There is a command SetSPN which is a windows command that will help you query existing SPN and let’s you create or delete one. There is a small utility from Microsoft that I have downloaded for him, and it generates and registered the SPN for him.
There are commands like SetSPN -L which will list all the registered SPN and using -A will allow you to add the new SPN, you can explore more about switches and command here in this link.
Once He has registered a proper SPN the error doesn’t exists anymore and he was able to work without any issues.
