The Microsoft SQL Server System Views Map shows the key system views included in SQL Server, and the relationships between them :
- SQL Server 2005 System Views Map - SQL Server 2008 System Views Map- SQL Server 2008 R2 System Views Map - Mapping System Tables to System Views (Transact-SQL)
Michel Degremont | Premier Field Engineer - SQL Server Core Engineer |
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: http://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 : http://support.microsoft.com/kb/327825 , then customer investigates to see why this user being a member of a large number of groups.
The SQL Network Interface library was unable to register SPN
How to troubleshoot the "Cannot generate SSPI context" error message
“Cannot Generate SSPI Context” error message, more comments for SQL Server