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 by pinging 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 = 0mstipIf 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 = 0mstipIf 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.161tipIf 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.161tipIf the nslookup fails, make sure that a [PTR] record is created in DNS for the virtual IP.
Kerberos vs NTLM
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:
-
Both the client and server computers are running Microsoft Windows 2000 with Service Pack 3 (SP3) or later.
-
Both the client and server computers are part of the same domain or trusted domains.
-
The SQL Server service SPN is registered with Active Directory.
-
The SQL Server instance is listening on TCP/IP.
-
The SQL client is connecting over TCP/IP.
Service Principal Names
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:
-
ServiceClass — the class of service. MSSQLSvc is for SQL Server.
-
Host — the fully qualified domain name for the computer running the SQL Server service.
-
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
Authenticating via Kerberos
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
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