This is more about how to find something than finding something if that makes sense or doesn’t. Anyway a bit of back story is probably worthwhile at this point
In the beginning
There was once a server that wasn’t working properly, the poor little SQL server when started would write the following out to the SQL event log ( but not in Italic)
The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x80090350, state: 4. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
Every time it started, it would moan and whinge, this however was a lowly test server and didn’t get the time it deserved. Until someone broke ( accidentally) some integration that was setup with another server. This was rebuilt and Kerberos was actually needed now, ( how it wasn’t before is another question for another day)
Anyway all the blogs on the internet were googled as is the way and there are lots out there regarding SPN’s and the setup and configuration and the fixing of, these had all been followed. Its at this point that I mention there was another server, a test server just like out poor little server, only it didn’t have this same problem.
Hallelujah, I hear people exclaim, just look at our good server and our poorly servers configuration and settings , which was exactly what the DBA had done and was starting to pull out his hair, when he asked for another opinion and a another set of eyes on the issue. We tried a couple of things to eliminate the issue without success! He exclaimed both service accounts are setup the same. But are they ? I considered, have you seen how many setting can be made against users and computers in AD wow
Quick google for the syntax and a couple lines of PowerShell for both the poorly server and good server
Get-ADUser -filter {name -like "svc_Poorly_SQL"} -Properties | Export-Csv -Path "C:\Temp\MSTEST.csv" -Delimiter ","
Allowed me to compare the setting and yep there was ones that weren’t the same, taking usernames, SID and things out of the equation , some digging was done on the setting that were different and on the poorly server the UseDESKeyOnly option had been set to TRUE, which was visible in the GUI . There are other blogs out there which describe what this is and what this does, in this case all you need to know this was the cause of our issues.
The moral of the story
You can stare at something for ages and never see something, don’t ever be afraid to ask for someone else opinion, as you will without doubt be in the same position at some point. You don’t necessarily have to deep dive into things if you’ve got a baseline ( working server in this case) and the broken one ( not working one in this case), then its a matter of logic, actually what is different and a method of finding it out.