Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Windows Integrated Authentication Failures to SQL Server


By:   |   Updated: 2008-08-21   |   Comments   |   Related: More > 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.

Default setting for Windows Integrated Authentication in Internet Explorer

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:

Default setting for User-Friendly messages in Internet Explorer

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:

Default setting for User-Friendly messages in Internet Explorer

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:

IIS version Registry location
IIS 5.0 HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\w3svc\parameters
IIS 6.0 HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\HTTP\Parameters

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.


Last Updated: 2008-08-21


get scripts

next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools