MSSQL Server and Kerberos

Overview

This is an overview of the step necessary to get your Continuous Compliance Engine talking to an MS SQL Server database using Kerberos authentication. Since Active Directory already uses Kerberos for authentication, little or no additional configuration is need on the MSSQL Server database.

This page describes how to set up an MSSQL Server database instance for Kerberized connections. Since Active Directory already uses Kerberos for authentication, little or no additional configuration is needed on the MSSQL Server database. The following steps are described:

Create the necessary SPNs (Service Principal Names) for your MSSQL database service in Active Directory
Create the DB Connector on the Continuous Compliance Engine
Creating a keytab for an Active Directory User
Troubleshooting tips

Prerequisites

Configuring cross-realm trust between Active Directory and an MIT KDC Server is a complex topic, and will not be described here. In the absence of such a setup, it is possible to make the Delphix Appliance a Kerberos client of the Active Directory (AD) Server. In this configuration, no additional KDC in necessary. The example below assumes this kind of configuration.

This section of the document uses these example values in addition to or instead of those mentioned above:

The MSSQL server database is named mssql-db.bar.com.
The Active Directory user configured for masking access to the MSSQL database is aduser (rather than krbuser in other examples elsewhere in this document).
The Active Directory user that start the MS SQL Server service on the DB Server is dbuser.

Creating SPNs for the Database Service

MS SQL Server service will typically register several SPNs with Active Directory upon startup. However, there are several conditions which can cause these SPNs to not be registered successfully, or to be registered with service names other than those that are expected by the Microsoft JDBC Driver for SQL Server employed by Continuous Compliance.

The service principal name for an MS SQL Server expected by Continuous Compliance is: MSSQLSvc/

In addition, it is required that a reverse mapping exist in DNS from the IP address of the MS SQL Server system to the FQDN registered.

The following commands may be run in PowerShell on the MS SQL Server to assist in debugging SPN related issues:

List all SPNs for dbuser:

setspn -L -U dbuser

Deleting an old SPN associated with dbuser:

setspn -U -D MSSQLSvc/other-server.ad.bar.com:SQL2008R2 dbuser

Here's how to create the SPN describe above:

setspn -U -S MSSQLSvc/mssql-db.bar.com:1433 dbuser

Creating the Database Connector on the Continuous Compliance Engine

Once the above steps are complete, creating the database connector can be performed using the procedure above. Enter the username and optionally, password of the Active Directory user in the Connector definition. Be sure that the Active Directory user has sufficient access to the MS SQL Database for masking.

The password field can be left blank when creating the connector if the user is the same user configured in Server Setup for the appliance. Since keytabs are not typically used in an Active Directory environment, it may be useful to create one manually, to avoid having a password in the DB Connector.

Creating a keytab file for an Active Directory user

On a unix or MAC system with MIT Kerberos CLI utilities installed:

CODE

# ktutil

ktutil: addent -password -p krbuser -k 1 -e arcfour-hmac

<type password for krbuser>

ktutil: addent -password -p krbuser -k 1 -e aes128-cts-hmac-sha1-96

<type password for krbuser>

ktutil: addent -password -p krbuser -k 1 -e aes256-cts-hmac-sha1-96

<type password for krbuser>

ktutil: write_kt /var/tmp/krbuser.keytab

ktutil: exit

# base64 /var/tmp/krbuser.keytab ;# This is string to user for keytab in Server Setup kerberos configuration

kvno doesn't matter when using Kerberos keytabs with Active Directory. The password must match the active password for the Active Directory user in question

Troubleshooting tips

The client uses the incorrect service name. This will typically manifest an exception mentioning cred, like:

CODE

Caused by: org.ietf.jgss.GSSException: No valid credentials provided (Mechanism level: Fail to create credential. (63) - No service creds)
at sun.security.jgss.krb5.Krb5Context.initSecContext(Krb5Context.java:770)
at sun.security.jgss.GSSContextImpl.initSecContext(GSSContextImpl.java:248)
at sun.security.jgss.GSSContextImpl.initSecContext(GSSContextImpl.java:179)
at com.microsoft.sqlserver.jdbc.KerbAuthentication.intAuthHandShake(KerbAuthentication.java:163) ... 101 common frames omitted

Caused by: sun.security.krb5.internal.KrbApErrException: Fail to create credential. (63) - No service creds at sun.security.krb5.internal.CredentialsUtil.acquireServiceCreds(CredentialsUtil.java:162)
at sun.security.krb5.Credentials.acquireServiceCreds(Credentials.java:458)
at sun.security.jgss.krb5.Krb5Context.initSecContext(Krb5Context.java:693) ... 104 common frames omitted

This could happen if using the JTDS JDBC driver and your MSSQL Server’s IP address does not have a reverse mapping DNS. In that case, the driver could construct a service name like MSSQLSvc/, and try to use it.

Either correct the DNS to have a valid reverse mapping for the IP of your SQL server or manually add an SPN to the active directory for the name of the JDBC client being used. Determine the user that starts MSSQL Server on the database machine using the following command in PowerShell:

CODE

setspn -AU MSSQLSvc/ :1433
e.g. setspn -AU MSSQLSvc/10.43.100.101:1433 AD\dbuser

The database server has multiple DNS names (FQDNs). In this case, SPNs may be registered only for some of them. It may be necessary to add SPNs for the other FQDNs as above. The MS SQL Server did not automatically register an SPN. There is a limit (in the thousands) to the number of SPNs that may be registered for a given Active Directory user. It is quite possible to hit this limit in an environment where many MS SQL Server VMs are actively created and destroyed with the same configuration.

In Active Directory, setspn is not creating a service principal with distinct key as is typical for services on MIT KDCs - rather it is mapping the service principal to the key for the Active Directory user in question.

The SPN for the SQL Server is registered to the incorrect Active Directory account

Manifests as an exception with this text: GSS failure: Defective token detected (Mechanism level: AP_REP token id does not match!)

Resolution: From PowerShell on the MS SQL Server:

CODE

PS> setspn -Q <SPN>

This will show what the user has the SPN registered.

CODE

PS> setspn -U -D <SPN> <WRONG_ACCT>

This will unregister the SPN from that user

CODE

PS> setspn -AU <SPN> <CORRECT_ACCT>