Error: Cannot login to SQL Server or "Cannot generate SSPI context" (4224940)

Error: Cannot login to SQL Server or Cannot generate SSPI context error message. You may receive this error message when attempting to connect to SQL via LiteSpeed. On occasion, the connection may be successful after running a LiteSpeed xp from commandline or running Test Connection

From the Event Log:

Resolution by Microsoft is detailed in the SPNs for SQL Server:

From the Microsoft article:
Kerberos uses an identifier named Service Principal Name (SPN). Consider an SPN as a domain or forest unique identifier of some instance in a network server resource. You can have an SPN for a Web service, for an SQL service, or for an SMTP service. You can also have multiple Web service instances on the same physical computer that has a unique SPN.

An SPN for SQL Server is composed of the following elements:
ServiceClass: This identifies the general class of service. This is always MSSQLSvc for SQL Server.
Host: This is the fully qualified domain name DNS of the computer that is running SQL Server.
Port: This is the port number that the service is listening on.
For example, a typical SPN for a computer that is running SQL Server is:
MSSQLSvc/SQLSERVER1.northamerica.corp.mycompany.com:1433

The format of an SPN for a default instance and the format of an SPN for a named instance are not different. The port number is what ties the SPN to a particular instance.

When the SQL Server driver on a client uses integrated security to connect to SQL Server, the driver code on the client tries to resolve the fully qualified DNS of the computer that is running SQL Server by using the WinSock networking APIs. To perform this operation, the driver code calls the gethostbyname and gethostbyaddr WinSock APIs. Even if an IP address or host name is passed as the name of the computer that is running SQL Server, the SQL Server driver tries to resolve the fully qualified DNS of the computer if the computer is using integrated security.

When the SQL Server driver on the client resolves the fully qualified DNS of the computer that is running SQL Server, the corresponding DNS is used to form the SPN for this computer. Therefore, any issues pertaining to how the IP address or host name is resolved to the fully qualified DNS by WinSock may cause the SQL Server driver to create an invalid SPN for the computer that is running SQL Server.

For example, the invalid SPNs that the client-side SQL Server driver can form as resolved fully qualified DNS are:
MSSQLSvc/SQLSERVER1:1433
MSSQLSvc/123.123.123.123:1433
MSSQLSvc/SQLSERVER1.antartica.corp.mycompany.com:1433
MSSQLSvc/SQLSERVER1.dns.northamerica.corp.mycompany.com:1433
When the SQL Server driver forms an SPN that is not valid, authentication still works because the SSPI interface tries to look up the SPN in the Active Directory directory service, and it does not find the SPN. If the SSPI interface does not find the SPN, Kerberos authentication is not performed. At that point, the SSPI layer switches to an NTLM authentication mode and the logon uses NTLM authentication and typically succeeds. If the SQL Server driver forms an SPN that is valid but is not assigned to the appropriate container, it tries to use the SPN but cannot, causing a Cannot generate SSPI context error message. If the SQL Server startup account is a local system account, the appropriate container is the computer name. For any other account, the appropriate container is the SQL Server startup account. Because authentication will try to use the first SPN that it finds, make sure that there are no SPNs assigned to inappropriate containers. In other words, each SPN must be assigned to one and only one container.

The key factor that makes Kerberos authentication successful is the valid DNS functionality on the network. You can verify this functionality on the client and the server by using the Ping command-line utility. On the client computer, run the following command to obtain the IP address of the server that is running SQL Server (where the name of the computer that is running SQL Server is SQLServer1):
ping sqlserver1
To see if the Ping command-line utility resolves the fully qualified DNS of SQLServer1, run the following command:
ping -a IPAddress

When the command ping -a IPAddress resolves to the correct fully qualified DNS of the computer that is running SQL Server, the client side resolution is also successful.

Additional Information:

If you run the SQL Server service under the LocalSystem account, the SPN is automatically registered and Kerberos interacts successfully with the computer that is running SQL Server. However, if you run the SQL Server service under a domain account or under a local account, the attempt to create the SPN will fail in most cases because the domain account and the local account do not have the right to set their own SPNs. When the SPN creation is not successful, this means that no SPN is set up for the computer that is running SQL Server. If you test using a domain administrator account as the SQL Server service account, the SPN is successfully created because the domain administrator-level credentials that you must have to create an SPN are present.

Because you might not use a domain administrator account to run the SQL Server service (to prevent security risk), the computer that is running SQL Server cannot create its own SPN. Therefore, you must manually create an SPN for your computer that is running SQL Server if you want to use Kerberos when you connect to a computer that is running SQL Server. This is true if you are running SQL Server under a domain user account or under a local user account. The SPN you create must be assigned to the service account of the SQL Server service on that particular computer. The SPN cannot be assigned to the computer container unless the computer that is running SQL Server starts with local system. There must be one and only one SPN, and it must be assigned to the appropriate container. Typically, this is the current SQL Server service account. However, this is the computer account with local system.

From Microsoft: How to make sure that you are using Kerberos authentication when you create a remote connection to an instance of SQL Server 2005

Details about NTLM/Kerberos authentication errors: