Advertisements
Home > Security, SQL 2005 > SSPI Context Cannot be generated

SSPI Context Cannot be generated

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.

Advertisements
Categories: Security, SQL 2005
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: