MS SQL Server 2000, IIS5 and Kerberos Delegation
By Brian Heath, MS SQL Server DBA/Enterprise Architect
In this article I will attempt to break down this process and help you to avoid the pitfalls and learn from my mistakes and hours of clicking and policy refresh commands.
The need for Kerberos Authentication in the enterprise was a big issue. The objective was to totally integrate the Active directory as the main source for all authentication. The main goals were:
1. Eliminate the 50 or more databases that would copy that which the AD already had in it from form based authentication.
2. To bring the security context all the way down to the row level on SQL server. To be able to identify at every transaction who was accessing what etc.
3. Remove the user name and passwords from the ADO and ADO.NET connection string.
There are three distinct entities to configure for this to work. These all must be at least Windows 2000 servers.
1. The Domain Controller, PDC.
2. The IIS Server
3. The SQL Server
Enterprise requirements and specifications:
1. All computers must be running Windows 2000 or later.
2. All computers must be in the same domain, or domains that trust one another.
3. The domain(s) must be using Active Directory.
4. Synchronized clocks: Kerberos depends on synchronized timing between computers. Out-of-sync computer clocks may cause Kerberos Authentication to fail.
Active Directory Settings:
1. User accounts not "sensitive": Users who will be connecting through IIS to SQL Server must not have their domain user accounts marked as "sensitive and cannot be delegated" in the Active Directory Users and Computers tool.
2. Web server "trusted for delegation": The IIS server -- the computer that will be delegating -- must be "trusted for delegation" in the Active Directory Users and Computers tool.
3. Web application process "trusted for delegation": The IWAM account, or the account used in its place -- the account of the process that will be delegating -- must be "trusted for delegation" in the Active Directory Users and Computers tool.
4. SQL server SPN? It may be necessary to register a "Service Principal Name" (SPN) for SQL Server using the Windows 2000 Resource Kit SetSPN tool if SQL Server is not running under the LocalSystem account.
5. Web server SPN? It may be necessary to register a "Service Principal Name" (SPN) for the IIS server using the Windows 2000 Resource Kit SetSPN tool if the web site name does not match the NetBIOS name of the server. (Q294382 Authentication May Fail with "401.3" Error If Web Site's "Host Header"...)
6. Cannot have duplicate SPNs
Client Computers
1. Browser: Client computers must be running Internet Explorer 5.0 or later (for native Kerberos authentication), or a browser capable of Basic Authentication
2. Browser setting: In IE, it may be necessary to turn on "Enable Windows Integrated Authentication" in the browser's Advanced Options. (Q299838 Unable to Negotiate Kerberos Authentication After Upgrading to Internet)
3. Browsers can't be going through a proxy server. If using a proxy must select option in IE to "bypass proxy for local addresses"
4. If using IP Address or Fully Qualified Domain Name need to add to list of local sites in IE
IIS Server
1. Authentication: Only Windows Authentication, or Windows and Basic, if necessary (see below), should be enabled on the web site or virtual folder. Digest authentication does not support delegation
2. Application protection: Preferably, the web application should be running in High Application Protection, so the changes made to allow delegation will not extend these privileges to other web applications.
3. Package impersonation level: The Impersonation Level of the web application's COM+ package must be changed from the default of "Impersonate" to "Delegate".
4. Package identity: Preferably, the identity of the web application's COM+ package should be changed from the IWAM account to another domain account that is "trusted for delegation" (see above), again so the changes made to allow delegation will not extend these privileges to other web applications.
5. Trusted Connection: Specify a trusted connection to SQL Server with "Integrated Security=SSPI" in the connection string.
6. SQL Net-Lib: Preferably, specify the TCP/IP Network Library for the connection to SQL Server, either through the connection string ("Network Library=DBMSSOCN") or a server alias. On SQL Server 7.0, a problem with the 7.0 TCP/IP Net-Lib prevented Kerberos from working; in those circumstances, use Named Pipes
SQL Server
1. Logins: Windows logins (with appropriate database permissions) must be established for each of the Windows accounts that will be connecting to SQL Server using Windows Authentication. Microsoft recommends Windows Authentication only instead of Mixed Mode security.
2. SQL Net-Lib: SQL Server must be listening on the protocol that the client (web server) is using to connect -- TCP/IP or Named Pipes.
MISC:
1. Must have >= MDAC 2.6
2. Customer was running into known issue with limatation of number of groups in user account see below KB 327825 New Resolution for Problems That Occur When Users Belong to Many Groups http://support.microsoft.com/?id=327825 Needed to install hotfix.
3. For Active Directory to work correctly, DNS MUST function correctly. If the customer is having DNS problems, Kerberos will fail, due to name lookup problems. Have the customer repair his DNS before doing any additional trouble shooting on his probem or collaborate with a networking SP.
4. Check to verify there are no additional hotfixes the customer needs to install.
The ado connection application string. objConn.Connectionstring = "Provider=SQLOLEDB;Data Source=<your db server name>;Initial Catalog=<DBName>;Network Library=DBMSSOCN;Integrated Security=SSPI;Persist security info=False"
Getting Started
Let’s start off with the Domain controller:
1. Go to the DC and under “Active Directory Users and Computers” right click on the computer to be setup for delegation (IIS Server) and click to check the box next to “Trust this computer for delegation”
2. Grant delegation permission through the “Active Directory Users and Computers” by accessing each accounts you wish to have this right and altering the account properties by adding the permission “Account is Trusted for Delegation”
3. Install kerbtray.exe to easily verify and purge Kerberos tickets.
Let’s move on to the IIS Server:
1. Place an ASP test page in the default web site wwwroot.
2. Re-configure the web server to use “Integrated Windows Authentication” only by right clicking on the default web server, chose directory security, edit and unchecked anonymous access.
3. Install the SQL Server 2000 client tools to the web server.
Note: If the HKLM\SW\MS\MSSQLSERVER\Client\DSQUERY value was not present, added it as DSQUERY:Reg_SZ:DBNETLIB
4. Installed kerbtray.exe utility to easily verify and purge Kerberos tickets.
Finally on to MS SQL Server:
1. Added NT domain accounts to the SQL server login.
2. Download and install the setspn utility, from http://www.microsoft.com/windows2000/techinfo/reskit/tools/existing/setspn-o.asp
3. Run:
setspn –A MSSQLSvc/servername.FQDN SQL_service_account (i.e. SQLBUSTER.MYDOMAIN.COM SQLSVC)
setspn –A MSSQLSvc/ servername.FQDN:port SQL_service_account (i.e. SQLBUSTER.MYDOMAIN.COM:1433 SQLSVC)
4. Then verify both using a setspn –L
setspn –L _service_account (i.e. SQLSVC)
setspn –L _servername (i.e.SQLBUSTER)
5. Install kerbtray.exe utility to easily verify and purge Kerberos tickets
If anyone is getting this error "Cannot Generate SSPI context" with error number -2147467259 after installing service pack 4 on the Win2k servers they need to get the hot fix which I have, that is not public, from Microsoft.
Read this article http://support.microsoft.com/?id=818173 I am working on the Kerberos delegation article as we speak and I will cover all this in detail. But thought a heads up would help all of us who will get bit by this.
Some very good links:
SQL SERVER BOL search for Security Account Delegation.
http://www.microsoft.com/windows2000/techinfo/reskit/tools/existing/setspn-o.asp
http://support.microsoft.com/?id=327825
http://www.powerblue.net/forum/display_topic_threads.asp?ForumID=22&TopicID=22&PagePosition=1
http://support.microsoft.com/?id=818173
http://www.microsoft.com/windows2000/techinfo/reskit/tools/existing/kerbtray-o.asp
811889 HOW TO: Troubleshoot the "Cannot Generate SSPI Context" Error Message http://support.microsoft.com/?id=811889
814401 PRB: Error Message: Cannot Generate SSPI Context
http://support.microsoft.com/?id=814401
Useful commands:
"set L" to determine your logon server (DC).
Gpupdate /target:computer to refresh the AD policy.
Setspn –A MSSQLSRV/HOST:port serviceaccount. Creates a service principal name.
