Resloving openrowset error in sql server

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.

Hemantgiri Goswami

Throughout my extensive 24-year tenure in the IT industry, I have honed my expertise in SQL Server and cloud technologies. My qualifications include certifications in ITIL, Azure, and Google Cloud, and my professional journey boasts a consistent record of delivering top-notch, dependable, and efficient solutions across diverse clients and domains. In recognition of my dedication and impact, I am honored to have received the Microsoft MVP award for SQL Server on six occasions. Additionally, I actively contribute to various online forums and blogs, acting as a moderator and facilitator of meaningful discussions. My ultimate mission revolves around empowering organizations to enhance the reliability and efficiency of their SQL Server implementations while fostering a culture of continuous learning and growth within the SQL Server community.