Tuesday, September 15, 2009

SQL Authentication issues and MaxTokenSize

Saved my bacon yesterday so selflessly reposted the link below:

DBA's and web developers at our company are experiencing issues with connecting to SQL instances using SQL Server Management Studio and other SQL tools using Windows Integrated Authentication. Our company is large, with well over 70,000 users and groups in Active Directory. When we look in the NT event log on the SQL Server we see both MSSQL and Kerberos errors. What could be causing this?

The issue occurs when a user has membership in many Active Directory groups, usually over 100 (this number includes explicit membership as well as inherited membership from other groups). The default value for the MaxTokenSize is 12000 decimal, although there is no registry entry for the default value. In the great majority of organizations this size is more than adequate; however, in large organizations users' tokens may be larger than the default. Since Kerberos won't accept broken tokens, authentication fails. If you are encountering this issue you will see two errors indicating that the default MaxTokenSize is not sufficient:

NT error log entry from the Kerberos service

Kerberos message in the NT event log

NT error log entry from the MSSQL service

MSSQL message in the NT event log

Microsoft has a utility called TokenSZ that can be used to determine the MaxTokenSize for a user. There are a number of switches that can be used with the utility, but the general syntax to calculate the max token size is:

Sample TokenSZ Syntax

E:\>tokensz /compute_tokensize /user:Administrator /domain:CULLENSOLUTIONS.com /password:OU812

Results of executing TOKENSZ

Output after executing TokenSZ

At the top of the window above you can see that the default token size is 12000. The text outlined in red is the actual Max Token size for the Administrator of the domain...well below the MaxToken default. The company I am currently assigned to is encountering MaxTokenSizes between 12000 to 15000 so a modification was required.

A registry entry modification or addition is required to modify the default MaxTokenSize allowed by the server. The location of the registry change is in the HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Lsa\Kerberos\Parameters section. Note that the server on which TOKENSZ was executed does not have an entry for MaxTokenSize:

Parameter Values in the HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Lsa\Kerberos\Parameters section

To create the registry entry, open Registry Editor by clicking Start -> Run, then typing regedit. Once Registry Editor is open navigate to the HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Lsa\Kerberos\Parameters section. Right-click the white area on the right side and choose New -> DWORD value:

Creating a new DWORD value

Rename the entry to "MaxTokenSize", double-click to edit it, choose Decimal, and enter 65535:

Editing the value of the Registry entry

Any server workstation or server that interacts with SQL Server will require the registry entry. Also, the machine will require a reboot for the change to take effect. Once the reboot has occurred you can execute TokenSZ again to see if the MaxTokenSize value has changed:

Result of TokenSZ execution after modifying the MaxTokenSize value

1 comment:

Search Brian Hehir's sites