Posted by: Colin Smith
SPN, SSPI, Troubleshooting
I recently had an issue with a site that was getting some users connected to a few new SQL Server 2005 instances just fine, via ODBC connections, while other users were getting SSPI errors. SSPI usually is indicative of a problem with SPN’s and Kerberous Authentication. What was even more strange is that the Techs at the site told me that they did have a workaround. I expected them to say they had set up a generic SQL account for everyone to use but that was not it. They said that adding and entry to the host file of the local machine that pointed to the SQL Servers was a resolution. I, personally, think that this is a very very bad idea and not something that I would ever use as a solution. Since I did not think this was a good solution I continued digging and took the steps to resolve the SSPI errors. I reverted the SQL Server back to run as a local serivce account and I saw the SPN change in AD. Then I moved back to the domain service account and saw those changes reflect as well. Everything appeared to be working properly. I was pretty stumped, but then I started thinking about what a Host file entry does. It is DNS, and has nothing to do with SQL Server at all. With that in mind I started looking into our DNS and I found that NS lookup worked just fine for the servers. I pulled up DNS and I found the entries were correct for the Servers. Then I found out that this site also has two of its own domain controllers both hosting DNS as well. These servers should get Dynamic Replications of DNS brought down to them and be the same as the source DNS Server. I looked into both and found that one was not replicating properly. I worked with another team member, who fixed the DNS Replication, and now everything is working.
Moral of the story is that SSPI is not always SPN’s in AD. It could be unable to authenticate to the server because the DNS is not set up properly.