Support Portal

Welcome, Guest Login

Support Center

How to Configure Kerberos Authentication

Last Updated: Oct 25, 2016 02:49PM PDT

Summary

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

Information

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

To verify that DNS is correctly configured for the virtual Microsoft SQL Server running on DxEnterprise or DxConsole, ensure that the following tests are successful:

Ping the virtual SQL Server 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

NOTE: If the ping fails, ensure that an [A] record is created in DNS for the hostname.

Ping the virtual SQL Server 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

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

Nslookup the Virtual SQL Server. For example:

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

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

Nslookup the Virtual SQL Server 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

NOTE: 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 a 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 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

NOTE: 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

Register a Service Principal Name for Kerberos Connections

How to use Kerberos authentication in SQL Server

Applies to...

  • DxEnterprise 15.0
  • DxEnterprise 15.5
  • DxEnterprise 16.0
  • DxConsole
  • Microsoft SQL Server

Contact Us

support.desk@dh2i.com
http://assets0.desk.com/
false
desk
Loading
seconds ago
a minute ago
minutes ago
an hour ago
hours ago
a day ago
days ago
about
false
Invalid characters found
/customer/en/portal/articles/autocomplete