Skip to main content
Version: Archive

Configure Kerberos Authentication on Windows

Summary​

How to configure Kerberos authentication for Microsoft SQL Server when running DxEnterprise.

Information​

Domain Name Service (DNS) is the primary name resolution mechanism for Microsoft Windows. If DNS is not configured correctly, Windows authentication may fail.

Verify that DNS is correctly configured for the Vhost, ping the Vhost by name. For example:

C:>ping vsql1
Pinging vsql1.dh2i [10.1.200.161] with 32 bytes of data:
Reply from 10.1.200.161: bytes=32 time<1ms TTL=128
Reply from 10.1.200.161: bytes=32 time=1ms TTL=128
Reply from 10.1.200.161: bytes=32 time<1ms TTL=128
Reply from 10.1.200.161: bytes=32 time<1ms TTL=128
Ping statistics for 10.1.200.161:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 1ms, Average = 0ms
info

If the ping fails, ensure that an [A] record is created in DNS for the Vhost name.

Ping the Vhost by IP address. For example:

C:>ping -a 10.1.200.161
Pinging vsql1.dh2i [10.1.200.161] with 32 bytes of data:
Reply from 10.1.200.161: bytes=32 time<1ms TTL=128
Reply from 10.1.200.161: bytes=32 time<1ms TTL=128
Reply from 10.1.200.161: bytes=32 time<1ms TTL=128
Reply from 10.1.200.161: bytes=32 time<1ms TTL=128
Ping statistics for 10.1.200.161:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),
Approximate round trip times in milli-seconds:
Minimum = 0ms, Maximum = 0ms, Average = 0ms
info

If the ping -a does not return the fully qualified hostname for the Vhost, make sure that a [PTR] record is created for the virtual IP.

Run nslookup against the Vhost name. For example:

C:>nslookup vsql1
Server:  dh2i-dns.dh2i
Address:  10.1.200.254
Name:    vsql1.dh2i
Address:  10.1.200.161
info

If the nslookup fails, make sure that an [A] record is created in DNS for the Vhost.

Run nslookup against the Vhost IP address. For example:

C:>nslookup 10.1.200.161
Server: dh2i-dns.dh2i
Address: 10.1.200.254
Name: vsql1.dh2i
Address: 10.1.200.161
info

If the nslookup fails, make sure that a [PTR] record is created in DNS for the virtual IP.

When a SQL client uses integrated security (SSPI) to connect to SQL Server, the SQL driver authenticates the client via the strong network authentication, Kerberos first. If Kerberos is not available, NTLM authentication is then used to authenticate the client. Kerberos authentication is only used if the following prerequisites/conditions are met:

  1. Both the client and server computers are running Microsoft Windows 2000 with Service Pack 3 (SP3) or later.

  2. Both the client and server computers are part of the same domain or trusted domains.

  3. The SQL Server service SPN is registered with Active Directory.

  4. The SQL Server instance is listening on TCP/IP.

  5. The SQL client is connecting over TCP/IP.

Kerberos uses a domain unique identifier, Service Principal Name (SPN), to identify a resource within a network. An SPN for SQL Server is composed of the following three components:

  1. ServiceClass — the class of service. MSSQLSvc is for SQL Server.

  2. Host — the fully qualified domain name for the computer running the SQL Server service.

  3. Port — the TCP port on which the SQL Server service is listening.

An example of a valid SPN for a Virtual SQL Server is:

MSSQLSvc/vsql1.dh2i:50001

When connecting to SQL Server via Kerberos, the client SQL driver uses the Winsock API (gethostbyname and gethostbyaddr) to resolve the SQL Server fully qualified name to form an SPN for the target SQL Server. Whether the SPN is valid depends entirely on DNS name/address resolution. If the client-formed SPN is invalid, the SSPI interface retries by looking up an SPN in Active Directory. If an SPN does not exist in AD for the SQL Server, Kerberos authentication is not used, and the logon reverts to an NTLM authentication.

A valid SPN for SQL Server is assigned to two types of containers. When the SQL Server service account is a domain administrator, or the local system account, an SPN for SQL Server is automatically registered and assigned to the hostname container when SQL Server starts up. If the SQL Server service account is not a domain administrator, or the local system account, the SPN for the SQL Server container is the service account. The SETSPN utility can be used to register a SQL Server SPN for the service account.

For example:

SETSPN.EXE -A MSSQLSvc/vsql1.dh2i:50001 SqlSvcAcct
info

You must be a domain administrator to run the setspn command.

Kerberos authentication will try to use the first SQL Server SPN that it finds in Active Directory; thus, it is important to have only one SPN for each SQL Server service, and to assign the correct container to each SPN.

To delete an invalid SPN, run the SetSPN command with "-D" flag. For example:

SETSPN.EXE -D MSSQLSvc/vsql1:50001 SqlSvcAcct

Additional Information​