This error is probably one of the most frustrating ones for the SQL Server database folks. MSFT has an excellent KB article that covers the details on how to fix this issue – KB 811889. It goes into the details of Kerberos and NTLM, registering and de-registering SPNs etc..
At one of the client sites, despite following the instructions as specified in the KB, we were not able to resolve the issue and that was largely due to the fact that we did not know what had transpired on that server which was being used by the client DBA – the logs had been cleared out as well thus making troubleshooting even more difficult. After talking to the staff, we came to know that initially the service was being run under the local system account and the system was shutdown accidentally while making other changes in the server room. When it was brought up again, the DBA changed the service to run under a domain account which was also an administrator on the DB Server. It was then that they were hitting the “Cannot Generate SSPI Context” error whenever they tried to use Windows Authentication and tried to connect from a client using TCP/IP protocol. After talking to MSFT support, it came to light that when SQL Serfver service was running under the local system account, it had successfully registered the SPN for the service and this was kept in the Active Directory. In a normal shutdown process, this SPN would have been de-registered, however since the server was shutdown accidentally, SQL Server thus failed to de-register the SPN. Now, when the DBA changed the account that was used to run the service to be a domain account, when the client connected to the server using TCP/IP, it tried to find the SPN in the Active Directory and tried to use Kerberos to perform the delegation of security – however, since this new account was not in the SPN, the SSPI authentication failed.
This client was even contemplating a complete re-install of SQL Server on that server in order to fix the issue – however, since the issue is in the SPN in the Active Directory, that would not have fixed the issue anyways. Setspn.exe (it is mentioned in the KB article above and can be downloaded from here) can be used to register/de-register SPNs. The KB article has the SPN commands listed in it. The de-registration will remove the SPN from Active Directory. The easiest fix under such circumstances for “Cannot Generate SSPI Context” is to run the SQL server Service under the Local System account and gracefully shut it down. You can then change your service account to the domain account that you want to run it under and then re-start the service again. SPN will not be registered and clients will fallback to use NTLM. Another thing to note is that if you made any change related to SPN or service account on the server, the cached information on the clients will need a couple of minutes to go away.
Through the DMV sys.dm_exec_connections (SQL 2005) you can check which authentication method is being used -> SQL, NTLM or Kerberos:
select * from sys.dm_exec_connections
Look under the auth_scheme column.