Cannot generate SSPI context

I lost the count for how many times I resolved or helped someone to resolve the "Cannot generate SSPI context" error.

Even there is a lot of forums that talk about this error, it seems to me that some people still have difficulty for understand it. So I will give my try and be as simple as I can with useful links for the people that want to find more about this subject.

When this error appears?

This error usually appears when there is no SPN (Server Principal Name) registered to server name in active directory for SQL Server service.

Why it appears?

This error only occurs when SSPI (Security Support Provider Interface) uses kerberos authentication (that uses SPN) and can not complete the necessary operations to successfully delegate the user security token to the destination computer that is running SQL Server.

What is the Solution?

This problem can be avoid if the SQL Server service account have the "Read servicePrincipalName" permission and the "Write servicePrincipalName" permission in the Active Directory. Because the policy in many companies does not allow that, this problem can be solved by asking the systems administrators for adding SPN with SETSPN tool. Here is an example of how to use the command:

a) SETSPN -A MSSQLSvc/<ServerName>:<port> <SQL_Service_Account>

b) SETSPN -A MSSQLSvc/<ServerName>:<InstanceName> <SQL_Service_Account>

There is any Advantage for using kerberos?

The advantage for using kerberos with SQL Server is to provide a highly secure method to authenticate users and also a better performance, since the authentication protocol is different from NTLM.

And there is any Disadvantage?

The disadvantage is if you need to configure the SQL Server instance to listen to a different port, then you need to recreate the SPN with the new port number. It will be worse if the Instance is configure to use dynamic IP addresses. Then you will need to recreate the SPN each time the instance is started.

NOTES: 

• Dedicate Admin Connection (DAC) do not uses SPN, only uses NTLM. 
• If SQL Server instance is configured to listen to multiple IP addresses, the server will only automatically register the SPN with the first port that it identifies.