Share via

How to: Enable Kerberos Authentication on a SQL Server Failover Cluster

  • Article

Kerberos is a network authentication protocol designed to provide strong authentication for client/server applications. Kerberos provides a foundation for interoperability while enhancing the security of enterprise-wide network authentication.

You can use Kerberos authentication with Microsoft SQL Server 2005 stand-alone instances or with failover cluster instances running on Microsoft Windows 2000 Service Pack 3 (SP3). SQL Server 2005 supports this functionality as part of a typical Microsoft Windows 2000 Active Directory domain installation.

When the Network Name resource that SQL Server is dependant on is in a Windows 2000-based cluster, you can use Kerberos authentication on the resource after you upgrade the computer to Windows 2000 SP3. For additional information about enabling Kerberos on server clusters, see the Knowledge Base article, "Kerberos Support on Windows 2000-based Server Clusters."

The following section describes how to connect to a server that is running Microsoft Internet Information Services (IIS) to make a Kerberos connection to a server that is running SQL Server.

Prerequisites

You can only use this functionality if you are running Windows 2000 SP3.

Before you perform the Setup procedure, download the Kerbtray and the SetSPN utilities.

  • To download the Kerbtray utility, visit the following Microsoft Web site. With Kerbtray.exe, you can easily verify or remove (or both) Kerberos tickets from any of the associated computers that are being used.
  • To download the SetSPN utility, visit the following Microsoft Web site.

SQL Server only uses Kerberos if the client uses the TCP/IP protocol to connect to SQL Server. For example, if a client uses the Named Pipes protocol, Kerberos is not used. If you have multiple instances of SQL Server on a computer, you must configure a Server Principal Name (SPN) for each instance of SQL Server because each instance of SQL Server uses a unique TCP-IP port.

Enabling Kerberos for SQL Server 2005

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

  1. Step 1: Configure the domain controller

On a domain controller, in Active Directory Users and Computers:

  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.
  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. Reconfigure 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 additional information on configuring IIS to support both Kerberos and NTLM authentication, see the Knowledge Base article, "How to Configure IIS to Support Both Kerberos and NTLM Authentication."

    Note

    You must install Microsoft Data Access (MDAC) 2.8 SP1, or later, on the IIS Services server. To do so (and to make the tools available for testing), install the Microsoft SQL Server 2000 client tools to the Web server. To install only MDAC 2.8 SP1 or later without installing the client tools, visit the following Microsoft Web site.

  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 drops the existing sessions, and forces a new session to be established and a Kerberos ticket received.

Step 3: Create an SPN for SQL Server

Warning

SQL Server only uses Kerberos if the client uses the TCP/IP protocol to connect to SQL Server. For example, if a client uses the Named Pipes protocol, Kerberos is not used. If you have multiple instances of SQL Server on a computer, you must configure a Server Principal Name (SPN) for each instance of SQL Server because each instance of SQL Server uses a unique TCP-IP port.

Important

If the SQL Server service is running under the LocalSystem account, you do not have to manually configure an SPN for SQL Server. The SPN is created automatically when the SQL Server service starts. If the SQL Server service runs under a domain user account, you must manually configure an SPN. To do so, follow these steps.

To configure an SPN for SQL Server, use the SETSPN utility in the Microsoft Windows Resource Kit. To download the SETSPN utility, visit the following Microsoft Web site.

Before you run SETSPN, consider the following information:

  • You must run setspn.exe under a logon account with permissions to register the SPN.
  • Note the domain user account that the instance of SQL Server is running under. In the following examples, this account is named <SQL_Service_Account>.
    Important   If the instance of SQL Server is running under the LocalSystem account, you do not have to run the SETSPN utility.
  • You must have the fully qualified domain name (FQDN) of the computer that is running SQL Server. To determine the FQDN of the computer that is running SQL Server, use the ping utility. To do so, follow these steps:
  1. Ping the computer that is running SQL Server to determine its IP address:
    C:\>ping MySQLServer
    Pinging MySQLServer.MyDomain.com [10.10.10.10] with 32 bytes of data:
    Reply from 10.10.10.10: bytes=32 time=1ms TTL=128
  2. Use ping -a to perform a reverse lookup of the IP address to make sure that the FQDN is correctly returned by the Domain Name System (DNS) protocol:
    C:\>ping -a 10.10.10.10
    Pinging MySQLServer.MyDomain.com [10.10.10.10] with 32 bytes of data:
    Reply from 10.10.10.10: bytes=32 time<1ms TTL=128
  3. Ping the failover cluster instance name to obtain the IP address, and then run ping -a to make sure that the FQDN is correctly returned by the DNS.

Note

If you are using SQL Server failover clustering, you use the FQDN for the failover cluster instance name. Note the exact TCP/IP port that the instance of SQL Server uses. To determine this information, open SQL Server Configuration Manager on the computer that is running SQL Server, click the instance of SQL Server, and then view the properties for the TCP/IP protocol (default port).

After you determine the domain user account that the SQL Server service is running under, the FQDN of the computer that is running SQL Server, and the TCP/IP port that the instance of SQL Server is using, follow these steps to create the SPN for SQL Server.

Note

You must be a member of the Domain Administrators group to run the SETSPN command.

  1. If you are using SQL Server failover clustering, run the following SETSPN command:
    setspn -A MSSQLSvc/<FQDN> <SQL_Service_Account>
    For example, if MySQLServer.MyDomain.com is running under the domain user account SQLSVC, where MySQLServer.MyDomain.com is the name of the instance of SQL Server 2005 that is clustered, run the following command:
    setspn -A MSSQLSvc/MySQLServer.MyDomain.com SQLSVC
  2. For both clustered and non-clustered computers that are running SQL Server, run the following SETSPN command to register an SPN for the port that the computer that is running SQL Server is using:
    setspn -A MSSQLSvc/<FQDN>:<Port> <SQL_Service_Account>
    For example, if MySQLServer.MyDomain.com is running under the domain user account SQLSVC on port 1433, run the following SETSPN command:
    setspn -A MSSQLSvc/MySQLServer.MyDomain.com:1433 SQLSVC
  3. After the SPN is registered, verify that it is correctly registered by using the LIST feature (-L switch) of the SETSPN utility. Run SETSPN -L <SQL_Service_Account> to list all the SPNs that are registered to the domain user account that the instance of SQL Server is running under:
    setspn -L <SQL_Service_Account>
    For example, if MySQLServer.MyDomain.com is running under the domain user account SQLSVC on port 1433, run the following command:
    setspn -A SQLSVC
    The SPN that you created in step 1 (if SQL Server is clustered) and step 2 (if SQL Server is not clustered) is shown in the following output:
    C:\>setspn -l SQLSVC
    Registered ServicePrincipalNames for CN=SQLSVC,CN=Users,DC=MyDomain,DC=com:
        MSSQLSvc/MySQLServer.MyDomain.com
        MSSQLSvc/MySQLServer.MyDomain.com:1433

Note

If you are using SQL Server failover clustering, you must register a SPN without the port number and another SPN with the port number. With a typical, non-clustered computer that is running SQL Server, you only have to register the SPN with the port number. However, if you have an additional SPN without the port number, it will not cause any problems with non-clustered computers.

Note

You can also use the Ldifde.exe utility on the domain controller to verify both SPN registrations. This is described in the "How to gather a list of Active Directory server principal name information" section in this topic.

Step 4: Configure the client computers

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

Step 5: Test the configuration

For each computer that is involved:

  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

The following is an ASP test script for SQL Server data. If you use this code sample, make sure that you replace SQLSERVERNAME with the name of the computer that is running SQL Server:

<%@ 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 principal name information

To gather a list of Active Directory server principal name (SPN) information, type the following command on one of your domain controllers, where betaland is the NetBIOS domain name and NewoutputUsers.txt is the name of the output file that you will use to port the results. If you do not use a full path, the file is placed in the current folder where you run the command line. This sample command queries the whole domain:

ldifde -d "CN=Users,DC=betaland" -l servicePrincipalName -F NewoutputUsers.txt

This syntax creates a file named NewoutputUsers.txt that contains information that is similar to the output in the "Domain level output of NewouputUsers.txt" section of this topic.

This output may be overwhelming when you gather it for a whole domain. Therefore, to limit the gathered information to a specific user name, use the following syntax, where User Name is the user name and betaland is the domain that you are querying:

ldifde -d "CN=User Name,DC=betaland" -l servicePrincipalName -F NewoutputUsers.txt

Gathering the information for a specific user greatly reduces the data that you must search through. If you gather the information for a whole domain, search for the specific user name of the server in question. In the output sample, you see:

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

Additionally, you can use the Active Directory Service Interfaces (ADSI) tool to correct Active Directory entries that are not valid.

Caution   If you use the ADSI Edit snap-in, the LDP utility, or any other LDAP version 3 client, and you incorrectly modify the attributes of Active Directory objects, you can cause serious problems. These problems may require you to reinstall Microsoft Windows 2000 Server, Microsoft Exchange 2000 Server, or both. Microsoft cannot guarantee that problems that occur if you incorrectly modify Active Directory object attributes can be solved. Modify these attributes at your own risk.

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

See Also

Other Resources

Configuring Availability How-to Topics
Installing SQL Server 2005
Upgrading to SQL Server 2005

Help and Information

Getting SQL Server 2005 Assistance