This is an error that I’ve seen on various occasions when connecting to SQL Server using integrated security.
I recently came across this problem when attempting to connect to SQL Server 2005 from Management Studio. Connections to the server had been working fine until applying several Windows updates on the server so one of those must have been the cause of the problem.
The following knowledgebase article provides a full explanation to the cause of this error, in particular the ‘Simplified Explanation’ is very helpful.
In my case, here are the steps that I took to resolve the issue
1. Modify the SQL Server service to run under the Domain Administrator account.
2. Start SQL Server.
3. Stop SQL Server.
4. Restore the SQL Server service to run under the account that it was set up with initially (in my case the Local System Account.)
Since the service initially ran under the LocalSystem account, the explanation given in the KB article doesn’t completely make sense as this account should have the necessary permissions to register the SPN. Presumably, there was something else that required elevated domain permissions and briefly starting SQL Server under the domain administrator account provided a resolution.
A similar but unrelated problem that I’ve seen is the error “The trust relationship between this workstation and primary domain failed” when logging a Windows 7 machine onto a domain. The easiest fix for this is to remove and rejoin the machine to the domain.