SQL Server 2008 connectivity issue : cannot generate SSPI context

Article
01/13/2011

Problem description: We were not able to make a connection on SQL Server 2008 from a remote server with Windows account. We got the error message: cannot generate SSPI context:

9649 A security (SSPI) error occurred when connecting to another service broker: . Check the Windows Event Log for more information.
...
11248 A corrupted message has been received. The SSPI login header is invalid.
...
17806 SSPI handshake failed with error code X, state %d while establishing a connection with integrated security; the connection has been closed.
...
Connection handshake failed.

Work done: We followed the troubleshooting step below:

Step 1: made a TELNET on machine port and confirmed that the port of SQL Server instance was open

Step 2: We checked if the SPN for my instance SQL server exist with command below:

Start >> Run >> CMD >> Setspn -L <YourSQLServiceAccount>

Registered ServicePrincipalNames for CN=svc-mssql-sgbd,OU=Users,OU=_Paris,DC=puteaux,DC=net:
MSSQLSvc/serverName:PORT
MSSQLSvc/serverName

Step 3: From my remote server, I used an UDL file to make a connection.

Click right on your desktop >> new file >> then rename your text file by myTest.UDL.

Double click on it. Click on the tab PROVIDER. And select SQL Server Native client 10.0

     I tried to make a connection with another protocol, like Name Pipe.
     Np: <ServerName>\<IntanceName>
          Works fine

     Then I tried with TCP protocol with SQL authentication
     tcp: <ServerName>\<IntanceName>
           Works fine

      Then I tried with TCP protocol with Windows authentication
     tcp: <ServerName>\<IntanceName>
          Failed with error: cannot generate SSPI context

     Then I tried with TCP protocol with Windows authentication and the port
     tcp: <ServerName>\<IntanceName>,PortNumberOfYourSQLInstance
          Failed with error: cannot generate SSPI context

     Then I tried with TCP protocol with another account Windows authentication
     tcp: <ServerName>\<IntanceName>
           Works fine

Cause: we enabled the verbose logging to Kerberos following the article Microsoft KB: https://support.microsoft.com/kb/262177 .

And we found out inside Windows system event the error message:

The kerberos SSPI package generated an output token of size 12536 bytes, which was too large to fit in the token buffer of size 12535 bytes, provided by process id 4.
The output SSPI token being too large is probably the result of the user myUser@myDomain.com being a member of a large number of groups.
It is recommended to minimize the number of groups a user belongs to. If the problem can not be corrected by reduction of the group memberships of this user, please contact your system administrator to increase the maximum token size, which in term is configured machine-wide via the following registry value: HKLM\SYSTEM\CurrentControlSet\Control\Lsa\Kerberos\Parameters\MaxTokenSize.

Resolution: We increased the maximum token size following the article KB to get back in production : https://support.microsoft.com/kb/327825 , then customer investigates to see why this user being a member of a large number of groups.

SQL Server 2008 connectivity issue : cannot generate SSPI context

Additional resources