I am sure everyone has seen this error there are many articles on MSDN, newsgroups, etc.; but I ran into very interesting problem with these that none of the normal articles helped. Articles like [1], [2], [3], and [4] talk about various settings that can generated that error, from being SPN (Service Principal Name), to SQL Protocol Issues, to TDS packet issue, but none of these helped me resolve the issue. As I was getting this issue with one user only; unable to solve it I kept looking around and ran across article [5]; which indicated this is an issue with Kerberos authentication. Which was indicated by previous articles also, but what was interesting it stated that the Token generated by the user account in question was larger then the default size of 12,000 bytes thus the issue.
I had no way to check how many groups the user was in, so working with Active Directory guy we created copy of account and removed one group at a time until the user was able to login successfully. So in a sense we verified that number of groups the user belonged to was causing us the issue. But I did no have a work around for this, as article [5] suggested I was thinking of changing MaxTokenSize to FFFF (65535). I needed to verify that was the issue for sure; as it was only one user who was reported the issue. I had access to far more groups and resources but did not have any issues. After researching more I found Microsoft Utility, TokenSz [6] & [7] that lets you compute the token size generated for Kerberos authentication.
Running TokenSz utility on users account and my account we got interesting information …
Users account
Name: Kerberos Comment: Microsoft Kerberos V1.0
Current PackageInfo->MaxToken: 65535
Using user to user
QueryKeyInfo:
Signature algorithm =
Encrypt algorithm = RSADSI RC4-HMAC
KeySize = 128
Flags = 2083e
Signature Algorithm = -138
Encrypt Algorithm = 23
Start:7/12/2009 7:47:09
Expiry:7/12/2009 17:47:09
Current Time: 7/12/2009 7:47:09
MaxToken (complete context) 13383
If you notice users max token was above the default allowed of 12,000; so SQL Server failed in the authentication. I checked my token size it was only 7479. Looking at how the token is generated in [5]; it includes information from nested groups. So this had major impact on the number of groups she was really in.
Solution? We decided to change the MaxTokenSize on our SQL Server.
This caused another interesting (another word for annoying :S) issue, Link. But after all issues were resolved user was able to log on successfully.
Reference Links:
[1] How to troubleshoot the “Cannot generate SSPI context” error message, Link.
[2] PRB: Error Message: Cannot Generate SSPI Context, Link.
[3] SQL Protocols: “Cannot generate SSPI Context” Error message, when connecting to local SQL Server, Link & Link.
[4] How to configure an SPN for SQL Server Databases Servers, Link.
[5] New Resolution for Problems with Kerberos Authentication when users belong to many groups, Link.
[6] How do Token Size Affect SQL Server, Link.
[7] TokenSz Utility Download, Link.
SQL Canada (SQLCAN) 