How to use Kerberos authentication in SQL Server

• How to: Enable Kerberos Authentication Including SQL Server Virtual Servers on Server Clusters
• Registration of Service Principal Name

Connect to a server that is running Microsoft Internet Information Services and make a Kerberos connection to SQL Server 2000

Step 1: Configure the domain controller

1. Right-click the computer that you want to set up for delegation (IIS Services server), and then click to select Trust this computer for delegation. If the computer that is running SQL Server is what appears to be the last computer contacted but that computer has a linked server, it must also granted delegation permissions. If it is not the last computer in the chain, all the computers that are intermediaries must be trusted for delegation.
2. Grant delegation permission to the SQL Server service account domain user account. You must have a domain user account for clustered SQL Server installations (this step is not required for computers that are running SQL Server that are using a local system account):
   1. In the Users folder, right-click the user account, and then click Properties.
   2. In the user account properties dialog box, click the Account tab.
   3. Under Account Options, click to select the Account is Trusted for Delegation check box. Make sure that the Account is sensitive and cannot be delegated check box is cleared for this account.
      
      Note The 'Account is trusted for delegation' right is required for the SQL Server service account only when you are delegating credentials from the target SQL server to a remote SQL server such as in a double hop scenario like distributed queries (linked server queries) that use Windows authentication.
   Note These steps apply only to Windows 2000 Server. If you are using Windows Server 2003, visit the following Microsoft Developer Network (MSDN) Web site:
   http://technet2.microsoft.com/windowsserver/en/library/bef202b0-c8e9-4999-9af7-f56b991a4fd41033.mspx
3. Use the Kerbtray.exe utility to verify that Kerberos tickets were received from the domain controller and host:
   1. Right-click the Kerbtray icon in the notification area, and then click purge tickets.
   2. Wait for the green Kerbtray icon to change from green to yellow. As soon as this occurs, open a command prompt window and run this command:
      net session * /d
      This will drop the existing sessions, and force a new session to be established and a Kerberos ticket received.

Step 2: Configure the IIS services server

1. Replace the default Web site Wwwroot files with the sample .asp files. To create the sample .asp files, use the code that is provided in the "ASP test script for SQL Server data retrieval" section.
2. Add the file to the Wwwroot folder. To do so, use the sample code in the "ASP Test Script for SQL Server Data Retrieval" section. Save the file as Default.asp.
3. Re-configure the Web server to use Integrated Windows Authentication only:
   1. Right-click the default Web server, and then click the Security folder.
   2. In the Security folder, make the correct changes, and then click to clear anonymous access.
   3. From a command prompt, run this command:
      cscript C:\Inetpub\Adminscripts\adsutil.vbs get w3svc/NTAuthenticationProviders
      If Negotiate is enabled, the following is returned:

       NTAuthenticationProviders : (STRING) Negotiate,NTLM

      For more information, click the following article number to view the article in the Microsoft Knowledge Base:
      215383 How to configure IIS to support both the Kerberos protocol and the NTLM protocol for network authentication
   Notes
   • You must install Microsoft Data Access (MDAC) 2.6, or later, on the IIS Services server. To do so (and to make the tools available for testing), install the SQL Server 2000 client tools to the Web server. To install only MDAC 2.6, or later (without installing the client tools), visit the following Microsoft Web site:
     http://msdn2.microsoft.com/en-us/data/aa937730.aspx
   • IIS is a common middle tier system. However, IIS is not the only middle tier system. If IIS is not the middle tier system in your environment, follow the appropriate steps for your middle tier system.
4. Verify that the
   HKLM\SW\MS\MSSQLSERVER\Client\DSQUERY
   value is present in the registry. If the value is not displayed, add it as
   DSQUERY:Reg_SZ:DBNETLIB
   .
5. Use the Kerbtray.exe utility to verify that Kerberos tickets were received from the domain controller and host:
   1. Right-click the Kerbtray icon in the notification area, and then click purge tickets.
   2. Wait for the green Kerbtray icon to change from green to yellow. As soon as this occurs, open a command prompt window and run this command:
      net session * /d
      This will drop the existing sessions, and force a new session to be established and a Kerberos ticket received.

Step 3: Configure the SQL Server service to create SPNs dynamically

• Read servicePrincipalName
• Write servicePrincipalName

• If you use the Active Directory Service Interfaces (ADSI) Edit snap-in, the LDP utility, or the LDAP 3 clients and you incorrectly modify the attributes of Active Directory objects, serious problems occur. To resolve these problems, you may have to reinstall Microsoft Exchange 2000 Server or Microsoft Exchange Server 2003. In some cases, you may have to reinstall Microsoft Windows 2000 Server or Microsoft Windows Server 2003 and then reinstall Exchange 2000 Server or Exchange Server 2003. We cannot guarantee that these problems can be resolved. Modify these attributes at your own risk.
• You must be logged on as a domain administrator. Alternatively, you must ask your domain administrator to grant the appropriate permissions and the appropriate user rights to the SQL Server startup account.

1. Click Start, click Run, type Adsiedit.msc, and then click OK.
   
   Note The ADSIEdit tool is included in the Windows Support Tools. To obtain the Windows Support Tools, visit the following Microsoft Web site:
   http://www.microsoft.com/downloads/details.aspx?familyid=6EC50B78-8BE1-4E81-B3BE-4E7AC4F0912D&displaylang=en
2. In the ADSI Edit snap-in, expand Domain [DomainName], expand DC= RootDomainName, expand CN=Users, right-click CN= AccountName , and then click Properties.
   
   Notes
   • DomainName is a placeholder for the name of the domain.
   • RootDomainName is a placeholder for the name of the root domain.
   • AccountName is a placeholder for the account that you specify to start the SQL Server service.
   • If you specify the Local System account to start the SQL Server service, AccountName is a placeholder for the account that you use to log on to Microsoft Windows.
   • If you specify a domain user account to start the SQL Server service, AccountName is a placeholder for the domain user account.
3. In the CN= AccountName Properties dialog box, click the Security tab.
4. On the Security tab, click Advanced.
5. In the Advanced Security Settings dialog box, make sure that SELF is listed under Permission entries.
   
   If SELF is not listed, click Add, and then add SELF.
6. Under Permission entries, click SELF, and then click Edit.
7. In the Permission Entry dialog box, click the Properties tab.
8. On the Properties tab, click This object only in the Apply onto list, and then click to select the check boxes for the following permissions under Permissions:
   • Read servicePrincipalName
   • Write servicePrincipalName
9. Click OK two times.
   
   Note For help with this process, contact Active Directory product support, and mention this Microsoft Knowledge Base article.
   
   Note To use the dsacls tool to determine if the self account has the Write ServicePrincipalName permission, use the dsacls command. The following is the syntax:

   dsacls <distinguished_Name_of_service_account>

   If the self account has the Write ServicePrincipalName permission, you see the following output:

   Allow NT Authority\SELF SPECIAL ACCESS for Validated Write to Service principal name
   WRITE PROPERTY

   The dsacls tool is part of the Support Tools.
10. In the CN= AccountName Properties dialog box, click Attribute Editor.
11. Under Attributes, click servicePrincipalName in the Attribute column, and then click Edit.
12. In the Multi-valued String Editor dialog box, remove the service principle names (SPNs) for the instances of SQL Server that use this SQL Server service account.
    
    Warning You should only delete the SPNs for the instances of SQL Server that you are currently working on. The other instances of SQL Server that use this service account will be able to remove the SPNs that are related to these instances the next time that you start these instances.
13. Exit the ADSI Edit snap-in.

• There are multiple domain controllers.
• SQL Server is clustered.

• A SQL virtual instance named Sqlcluster with two nodes: Node A and Node B.
• Node A is authenticated by domain controller A and Node B is authenticated by domain controller B.

1. The Sqlcluster instance is active on Node A and registered the SQL SPN in domain controller A during start up..
2. The Sqlcluster instance fails over to Node B when Node A is shutdown normally.
3. The Sqlcluster instance deregistered its SPN from domain controller A during the shutdown process on Node A.
4. The SPN is removed from domain controller A but the change has not yet been replicated to domain controller B.
5. When starting up on Node B, the Sqlcluster instance tries to register the SQL SPN with domain controller B. Since, the SPN still exists Node B does not register the SPN.
6. After some time, domain controller A replicates the deletion of the SPN (from step 3) to domain controller B as part of Active Directory replication. The end result is that no valid SPN exists for the SQL instance in the domain and hence you see connection issues to the Sqlcluster instance.

Step 4: Configure the client computers

1. For each client that will connect, verify that Microsoft Internet Explorer is configured to use Windows authentication:
   1. In Internet Explorer, on the Tools menu, click Internet Options.
   2. Click the Advanced tab.
   3. Under Security, click to select Enable Integrated Windows Authentication (requires restart), and then click OK.

Step 5: Test the configuration

1. Log on to the computer, and then use Kerbtray.exe to verify that the computer can obtain a valid Kerberos ticket from the domain controller.
2. Use Kerbtray.exe to remove all tickets on the computer.
3. Create and connect to the Web page that returns the SQL Server data.
   
   Note Replace SQLSERVERNAME with the name of the computer that is running SQL Server:
   • If data is returned, this page displays the authentication type Negotiate, and the SQL Server data for the result of the sp_helpdb stored procedure that should return a list of the databases on the server that is being connecting to through the .ASP page.
   • If you have auditing turned on in SQL Server, in the Application log you will see that the connection is "trusted".

ASP test script for SQL Server data retrieval

<%@ Language=VBScript %>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>
<%="'auth_user' is" & request.servervariables("auth_user")%>
<P>
<%="'auth_type' is" & request.servervariables("auth_type")%>
<P>
Connections string is <B>" Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=pubs;Data Source=SQLSERVERNAME </B>
<P>
<%
	set rs = Server.CreateObject("ADODB.Recordset")
	set cn = Server.CreateObject("ADODB.Connection")
	cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=pubs;Data Source=SQLSERVERNAME"
	rs.open "MASTER..sp_helpdb",cn
	Response.Write cstr(rs.Fields.Count) +"<BR>"
	while not rs.EOF
		Response.Write cstr(rs(0))+"<BR>"
		rs.MoveNext
	wend
	rs.Close
	cn.Close
	set rs = nothing ' Frees memory reserved by the recordset.
	set cn = nothing ' Frees memory reserved by the connection.
%>
</BODY>
</HTML>
					

How to gather a list of Active Directory server principle name information

• Entries for servers that no longer exist, but that were not completely removed from Active Directory.
• The user "User Name" has valid SPN information about ten different servers.

Domain level output of NewouputUsers.txt

	dn: CN=User Name,CN=Users,DC=betaland
	changetype: add
	servicePrincipalName: MSSQLSvc/CLUSTERDEFAULT.betaland:1257
	servicePrincipalName: MSSQLSvc/INST3.betaland:3616
	servicePrincipalName: MSSQLSvc/INST2.betaland:3490
	servicePrincipalName: MSSQLSvc/SQLMAN.betaland:1433
	servicePrincipalName: MSSQLSvc/VSS1.betaland:1433
	servicePrincipalName: MSSQLSvc/INST1.betaland:2536
	servicePrincipalName: MSSQLSvc/INST4.betaland:3967
	servicePrincipalName: MSSQLSvc/SQLVIRTUAL1.betaland:1434
	servicePrincipalName: MSSQLSvc/SQLVIRTUAL.betaland:1433
	servicePrincipalName: MSSQLSvc/SQLBUSTER.betaland:1315