Windows Integrated Authentication Failures to SQL Server

By: Tim Cullen   |   Updated: 2008-08-21   |   Comments   |   Related: > SQL Server Configurations

Problem

In a previous tip you discussed overriding the MaxTokenSize to accommodate larger Kerberos tokens due to membership of a large number of groups in Active Directory. Right around the same we noticed that the MaxTokenSize needed to be adjusted, issues with web applications that use Windows Integrated Authentication also started failing, one of which was SQL Server Reporting Services. The company I work for made the necessary changes and rebooted the SQL and Web servers. After the reboot we still had issues with our web applications, although the issue with SQL Server Management Studio was resolved. What additional changes need to be made?

Solution

The previous tip on Kerberos Token sizes dealt with changing the MaxTokenSize in the HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Lsa\Kerberos\Parameters section of the registry. Unfortunately, that is not the only place where there are default sizes when using Windows Integrated Authentication to SQL Server.  Two additional values are MaxFieldLength and MaxRequestBytes. Unlike the MaxTokenSize issue, when the issue affects web applications it presents a little differently. By default, Windows Integrated Authentication is enabled in Internet Explorer as shown below in the Internet Options | Advanced interface.

When a user attempts to access a website using an alias or DNS name, DNS resolution is attempted. Because the larger Kerberos token sizes exceeds the MaxFieldLength default you get an immediate error. This is different from when a web page cannot be found in that there is a time gap between entering the address and receiving the "Page Cannot Be Found" error message. The interesting part of this dilemma is that if a user attempts to access the web site using either the host name or alias it will not succeed.  However, the web site can be accessed using the IP address.  Unfortunately, any SQL queries executed during page load will fail.

Internet Explorer's default behavior when encountering an error with a website is to issue user-friendly messages. You can turn off this behavior in the Internet Explorer options as shown below:

These user-friendly messages will provide the information required to troubleshoot the issue. Once you turn the user-friendly messages off you will see the following message:

As mentioned above, additional registry entries are required to override the defaults. The location of the registry entries depend on the version of Information Information Services (IIS) being used:

Internet Information Services 5.0

The registry entry required for IIS 5.0 is the MaxClientRequestBuffer DWORD entry. The recommended DWORD value from Microsoft is 32768.

Internet Information Services 6.0

There are two registry entries required for IIS 6.0. The first is the MaxFieldLength DWORD entry. According to Microsoft the recommended DWORD value is 65534. The second is the MaxRequestBytes DWORD entry. The recommended DWORD value from Microsoft is 500000.

Once the registry entries have been made, the IIS Admin and dependent services need to be restarted, but the server does not have to be rebooted. The original issue was detected during Outlook Web Access sessions and the fix was first described in a Knowledge Base article dealing with Outlook Web Access. The fix for web applications is the same regardless of the technology where Kerberos authentication is used.

Next Steps

• This situation will occur anytime the web application and SQL server are on different boxes and happens where Windows Integrated Authentication is used and Active Directory users are members of a large number of groups.
• The benefits of increasing the MaxFieldLength and MaxRequestBytes should be weighed against the risk of hacking. Careful evaluation and calculation of the actual sizes required should be done to mitigate the likelihood of web site hacking.
• Read about the Http.sys registry entries for IIS.
• Review the steps required to make the additional registry entries. Remember that even though the article is related to Outlook Web Access, the fix applied relates to Kerberos authentication in web applications.
• Get more information on the Windows Registry.

About the author

Tim Cullen has been working in the IT industry since 2003 and currently works as a SQL Server Reports Developer.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips