A recurring theme in the world of SQL Server seems to be the battle with Kerberos, SPNs, and SSPI Context errors. It is common enough that many DBAs have gone bald along with their domain admin counterparts trying to fix the problems.
Once upon a time, I contributed an article showing a decent tool that can help figure out some of the problems related to SPNs, SSPI errors, and Kerberos in general – with regards to SQL Server. The tool I mentioned in that article is called “Kerberos Configuration Manager” (KCM).
Recently, I ran into an error with this tool that is a bit absurd and not very helpful at all. Given the usefulness of the error and absurdity of it, I thought to myself – why not share the problem and resolution in this, the first article in this year’s 12 Days of Christmas Series.
What A Wretched Little Error
On the surface this seems like a pretty chill error. It seems that there is useful information in the error screen that could help one go and figure out what to do next. Looking at the error text, it appears that I have an issue with permissions because I can’t access UserAccount information and the error text gives me a log to go check.
Let’s break this down a little bit. This error pops up with a user that happens to be a member of the local admins group on the server in question. The user also happens to be a domain administrator. And <grimace>, this user is also a sysadmin in SQL Server. So, seemingly permissions should not be an issue, right? I know, I know. This is not an ideal security setup. It just so happens to be a point of interest currently being discussed and worked on with this client. The security setup will get better. That said, I would eliminate permissions as a variable, and therefor permissions would not be a cause in this error.
Let’s take a look at the next given (sorry mathematical proof concept shining through there), aka “bit of information from the error text”. The error text tells me there is a log available and gives me the directory where it should exist, so it is time to look at that log.
If I proceed to open that file and look at the contents, I frequently see something like this ( 4 for 4 with this particular client).
Note here that the file is entirely empty. This is a problem! The place I am supposed to look to resolve this problem has nothing logged to the file. How can I possibly use that to troubleshoot? Well, keep reading.
The Cause and How to Fix this Kerberos Config Mgr Error
The error message is certainly misleading. Then again, maybe it isn’t. As it turns out, the cause of the message is due to the existence of a ghosted AD account in the local admins group. Here is an example of what I am talking about in the image to the right.
The local admins group on each of the affected systems had at least one of these detached SIDs. These are accounts that basically don’t exist any longer in Active Directory. These accounts should be cleaned out on a regular basis and it is a fairly risk free process.
Given this bit of insight, if you re-examine the error text, it now makes sense. There is an account for which the tool cannot gain access because the account does not truly exist – just some shards of it.
To fix the error, just delete these SIDs from the Local Admins group and then run the KCM tool again. After the ghost SIDs are removed, then an interesting thing happens (besides the KCM working properly). When you open the log file again, you will see something different. Here is an example.
Looking closer at the text of the log, this is the line of the greatest interest:
Error: Access of system information failed System.DirectoryServices.AccountManagement.PrincipalOperationException: An error (1332) occurred while enumerating the group membership. The member’s SID could not be resolved.
Clearly, if this message had populated before the problem was fixed, then I would have been able to fix the problem in a more direct path. This clearly states that there is a problem with an SID and that the SID could not be resolved. Why the tool needs to be able to resolve all SIDs escapes me, but it is what it is and we just roll with it for now.
Put a bow on it
This article showed a problem with one of my favorite tools – Kerberos Configuration Manager. This tool does provide a great deal of power in helping to resolve various SPN related problems with your SQL Server instances. Sadly, the error in this case is a bit of a pain to figure out because the log doesn’t populate properly when the error is thrown. Rather the log seems to populate after the error is resolved.
The solution provided in this article is an easy fix and is consistent across multiple versions of Windows and SQL Server. Save yourself some headache up front, just delete those phantom SIDs from the local admin group on a routine basis. They shouldn’t be there anyway.
Interested in more back to basics articles? Check these out!
Want to learn more about your security? Try this article on some of the many problems with lazy security in your environment (yes akin to a user being a Domain Admin, Sysadmin, and local admin). Here is another fantastic article discussing some of the persistent issues I have seen across the board at numerous clients for years and years.
And as a prelude to an upcoming article in the 12 Days of Christmas series, here is a refresher on a topic I wrote about just last month.
This is the first article in the 2020 “12 Days of Christmas” series. For the full list of articles, please visit this page.
The post How To Resolve User Error in Kerberos Configuration Manager first appeared on SQL RNNR.