Error: “Cannot generate SSPI context” when connect to a SQL Server instance after service account change

I’ve recently encountered this problem because to update our security standards, all SQL Server service accounts have to be changed to normal domain user accounts. And after changing them from LocalSystem to new accounts, this error starts to pop up. The cause is fairly simple and the following text is from page 113 of Microsoft’s ITP training book for exam 70-443, which I happen to be reading at the moment of writing this post:

To use Kerberos mutual authentication, you must ensure that the SQL Server 2005 instances have an SPN configured in Active Directory. When you configure SQL Server to run under the local system account, this SPN is automatically created. However, if you configure SQL Server to run under a service account, you should create the SPN manually. To configure an SPN for SQL Server, use the SETSPN utility available on the Windows Server 2003 CD.

Everytime the SQL Server service is started, the underlying service account tries to register SPN in active directory and the domain user account used as the service account might lack the permission to do so.  Click here or here to refer to Microsoft’s official guides to manually register SPN for SQL Server service. Also another article shows how to assign permissions to SQL Server service accounts to still automatically register SPN themselves. And finally, Microsoft official KB article targeted especially at the error message in the title is here.