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.