Microsoft SQL Server (Kerberos)

Overview #

A datasource consists of a database resource and the credentials used to access it. This guide describes how to add a Microsoft SQL Server (Kerberos) database as a datasource in the StrongDM Admin UI. This resource type only supports Windows authentication with Active Directory (AD) via Kerberos. For other options, see the Microsoft SQL Server (Azure AD) and Microsoft SQL Server resource types.

Prerequisites #

To add a datasource, make sure you have met the following prerequisites:

• Properly configure an account for your database resource. If you choose to store credentials for the resource with StrongDM, have those credentials ready. When not using StrongDM, set up a Secret Store integration and be able to enter the location of the secrets required to access the resource.
• The hostname or endpoint you enter for your resource must be accessible by at least one gateway or relay. To verify this, log in to the gateway or relay and use the nc -zv <YOUR_HOSTNAME> <YOUR_PORT> Netcat command. For example, use nc -zv testdb-01.fancy.org 5432. If your gateway server can connect to this hostname, you can proceed.
• Generate a krb5.conf file if you do not already have one.
• Generate a keytab file if you do not already have one, and ensure that it is Base64-encoded.
• Ensure that TCP connections are enabled on the server.
• Ensure that the correct AD user has been added to the logins for the database.
• Ensure that SQL Server is running on a computer joined to the AD domain, and the Service Principal Name (SPN) of the server instance is registered.
• You will need the credentials (username and password) of a user that is present in the AD domain and allowed access to the SQL Server.

Add a Datasource #

To add your new Microsoft SQL Server database as a StrongDM datasource, use the following steps.

1. Log in to the Admin UI.

2. Go to Resources > Datasources.

3. Click Add datasource.

4. Select Microsoft SQL Server (Kerberos) as the Datasource Type and set other configuration properties for your new database resource.

   

5. Complete all required fields.

6. Click Create to save the resource.

7. Click the resource name to view status, diagnostic information, and setting details.

Resource properties #

Configuration properties are visible when you add a datasource or when you click to view its settings. The following table describes the settings available for your Microsoft SQL Server database.

Secret Store options #

By default, datasource credentials are stored in Strong Vault. However, these credentials can also be saved in a third-party secrets management tool.

Options other than Strong Vault appear in the Secret Store dropdown if they are created under Network > Secret Stores. When you select another Secret Store type, its unique properties display. Instead of the normal Username, Kerberos Keytab, Kerberos Configuration, Realm, and Server SPN fields, you see versions of them that indicate that you should enter a path. This is the path to where the secret is stored in your Secret Store location (for example, path/to/credential?key=optionalKeyName where key argument is optional).

For more details, see Configure Secret Store Integrations.

Resource status #

After a resource is created, the Admin UI displays that resource as unhealthy until the health checks run successfully. When the resource is ready, the Health icon indicates a positive, green status.

When the resource does not display a positive status, click the resource name to go to the Diagnostics tab and check for errors.

Generate a krb5.conf file #

The krb5.conf file is a text file containing Kerberos configuration information, including the locations of Key Distribution Centers (KDCs) for the Kerberos realms of interest, defaults for the current realm and for Kerberos applications, and mappings of hostnames onto Kerberos realms.

An example krb5.conf file for use with a StrongDM Microsoft SQL Server (Kerberos) resource is as follows:

[libdefaults]
default_realm = AD.EXAMPLE.COM
dns_lookup_realm = false
dns_lookup_kdc = false
ticket_lifetime = 24h
forwardable = yes
default_tgs_enctypes = aes256-cts-hmac-sha1-96
default_tkt_enctypes = aes256-cts-hmac-sha1-96
permitted_enctypes = aes256-cts-hmac-sha1-96

[realms]
AD.EXAMPLE.COM = {
  kdc = ad.example.com:88
}

[libdefaults]

• default_realm: This should match the name of the realm under the [realms] section. For Active Directory (AD), it should generally be defined as the AD domain in uppercase.
• enctypes: Weaker encryption algorithms should not be needed so this should not need to be changed.
• All other parameters should be specified as in the example.

[realms]

• This specifies the name of the realm and the KDC (or KDCs) to use for that realm. In AD terms, a Kerberos realm is the same as an AD domain.
• kdc: the hostname or IP address and port of the KDC (the AD server). The port can be omitted when the default (88) is used. Multiple kdc lines may be specified if the server is accessible from multiple addresses for failover purposes.

Each StrongDM datasource is authenticated with a single realm (AD domain). Optionally, if your situation requires it, multiple realms may be specified in the krb5.conf to allow the same configuration file to be used for configuring other datasources in different realms. In that case the default_realm may be omitted and the krb5.conf must also specify a mapping from each server principal name domain (or subdomain) to the desired realm instructing the Kerberos library which realm to use for a given Service Principal Name (SPN). For a single configuration supporting resources with both an SQL Server principal name like MSSQLSvc/prod-mssql.ad.example.com:1433 and MSSQLSvc/dev-mssql.dev.ad.example.com:1433 the mapping would look like the following example:

[domain_realm]
.dev.ad.example.com:1433 = DEV.AD.EXAMPLE.COM
.dev.ad.example.com = DEV.AD.EXAMPLE.COM
.ad.example.com:1433 = AD.EXAMPLE.COM
.ad.example.com = AD.EXAMPLE.COM

A mapping with the domain name both including the port number from the SPN and excluding the port number from the SPN must be specified.

Generate a keytab file #

A Kerberos keytab (short for “key table”) stores long-term keys for one or more principals (users or service accounts, for example). Keytabs are normally represented by files in a standard binary format, although in rare cases they can be represented in other ways. Keytabs are used most often to allow server applications to accept authentications from clients, but can also be used to obtain initial credentials for client applications.

Each entry in the keytab consists of:

• Principal name, such as example-user@AD.EXAMPLE.COM
• Encryption key, derived from a password supplied for the principal
• Encryption type of the key, typically aes256-cts-hmac-sha1-96
• Timestamp the entry was added to the keytab
• Key version number (“kvno”), indicating the version of the key (where a higher version indicates a more recent key)

For use with a Microsoft SQL Server (Kerberos) datasource through StrongDM, the keytab should be created with an entry that has a principal name matching the AD user account password of the user that is used to log in. The username should include the AD domain/realm.

While some Kerberos libraries and tools do not validate the key version number (or always use the highest or zero), the Kerberos library used by StrongDM currently does require the key version number in the keytab to match the key version number stored in AD (under the msDS-KeyVersionNumber attribute of the user). The key version number in AD increments every time the password of the account changes (including any automatic rotation), requiring the key version number in the keytab to also be updated to match. If the key version number does not match, authentication between StrongDM and SQL Server fails. The correct key version number to use in the keytab can be determined from the logged error, or queried from AD in advance.

The keytab can be created locally on any operating system (macOS, Linux, or Windows), using commands described in the relevant sections. Testing the keytab locally (before configuring the StrongDM resource) is optional, but must be done on a system with access to the KDC.

Keytabs on macOS #

macOS comes preinstalled with the Heimdal Kerberos user space tools, including ktutil and kinit. Refer to the inline help or man pages of these tools for more details, including commands not described here.

Add an entry to the keytab #

To add an entry to a keytab (creating the keytab if it does not exist), use kutil as follows:

ktutil -k <KEYTAB_FILE> add -p <PRINCIPAL> -e <ENCRYPTION_TYPE> -V <KVNO>

• KEYTAB_FILE: Name of the output keytab file to create or modify
• PRINCIPAL: Principal name to add (the user account like USER@DOMAIN)
• ENCRYPTION_TYPE: Type of encryption to use for the key (where aes256-cts-hmac-sha1-96 is most likely to be used)
• KVNO: Key version number (matching the key version number of the corresponding user account in AD)

The utility prompts for the password of the principal. If the keytab file already exists it adds a new entry to the existing keytab. For example:

ktutil -k krb5.keytab add -p example-user@AD.EXAMPLE.COM -e aes256-cts-hmac-sha1-96 -V 5

List entries in the keytab #

To list all entries in a specified keytab, use the following:

ktutil -k <KEYTAB_FILE> list

For example:

$ ktutil -k krb5.keytab list  
krb5.keytab:
Vno  Type                     Principal                                Aliases
  5  aes256-cts-hmac-sha1-96  example-user@AD.EXAMPLE.COM 

Verify the keytab #

To verify that the keytab can be used to acquire a service ticket for an SQL Server instance, kinit can be used. This must be run on a system with network connectivity to AD and uses the krb5.conf created previously.

KRB5_CONFIG=<KRB5_FILE> kinit -c <CACHE_FILE> -V -t <KEYTAB_FILE> -S <SERVICE_PRINCIPAL_NAME> <PRINCIPAL_NAME>

• KRB5_FILE: Path to the krb5.conf
• CACHE_FILE: Path at which to store the acquired ticket (can be anything)
• KEYTAB_FILE: Path to the keytab
• SERVICE_PRINCIPAL_NAME: SPN of the SQL Server instance to acquire a ticket for, typically MSSQLSvc/HOSTNAME:1433 where HOSTNAME is the hostname of the SQL Server and 1433 is the port
• PRINCIPAL_NAME: Principal name added to the keytab earlier to use to acquire the ticket (that is, USER@DOMAIN)

For example:

$ KRB5_CONFIG=krb5.conf kinit -c krb5.cache -V -t krb5.keytab -S 'MSSQLSvc/prod-mssql.ad.example.com:1433' example-user@AD.EXAMPLE.COM

klist -c krb5.cache
Credentials cache: FILE:krb5.cache
        Principal: example-user@AD.EXAMPLE.COM

  Issued                Expires               Principal
May 10 16:43:19 2023  May 10 16:48:18 2023  MSSQLSvc/prod-mssql.ad.example.com:1433@AD.EXAMPLE.COM

Note that success with kinit does not necessarily guarantee that the keytab works with a StrongDM Microsoft SQL Server resource, as it is built on a different Kerberos library than that used by the StrongDM relay. In particular, kinit does not validate the key version number, so it does not detect a keytab with an incorrect version number.

Base64 encode the keytab #

Because the keytab is in a binary format, inputting it into the StrongDM Admin UI requires it to be Base64-encoded. This can be done using the following command:

base64 KEYTAB_FILE

Look up the key version number #

If the system has LDAP connectivity to the AD server, ldapsearch can be used to query the current key version number of a user for use in creating the keytab.

ldapsearch -h <AD_SERVER> -D <USER_ACCOUNT> -W -b <BASE_DN> <FILTER> <ATTR>

This prompts for the password of the user for authentication.

For example:

$ ldapsearch -h ad.example.com -D example-user@ad.example.com -W -b "cn=users,dc=ad,dc=example,dc=com" "(cn=example-user)" msDS-KeyVersionNumber
# extended LDIF
#
# LDAPv3
# base <cn=users,dc=ad,dc=example,dc=com> with scope subtree
# filter: (cn=example-user)
# requesting: msDS-KeyVersionNumber 
#

# example-user, Users, ad.example.com
dn: CN=example-user,CN=Users,DC=ad,DC=example,DC=com
msDS-KeyVersionNumber: 5

Keytabs on Linux #

Add an entry to the keytab #

To add an entry to a new or existing keytab, invoke the ktutil command and use the add_entry and write_kt commands:

$ ktutil
ktutil:  add_entry -password -p <PRINCIPAL_NAME> -e <ENCRYPTION_TYPE> -k <KVNO>
ktutil:  write_kt <KEYTAB_FILE>
ktutil:  quit

For example:

$ ktutil
ktutil:  add_entry -password -p example-user@AD.EXAMPLE.COM -e aes256-cts-hmac-sha1-96 -k 5
Password for example-user@AD.EXAMPLE.COM: 
ktutil:  write_kt krb5.keytab 
ktutil:  quit

To modify an existing keytab, run read_kt krb5.keytab as the first command after starting ktutil to load the existing keytab into memory first.

List entries in the keytab #

To list all entries in the keytab, invoke ktutil and use the read_kt and list commands:

$ ktutil
ktutil:  read_kt <KEYTAB_FILE>
ktutil:  list
ktutil: quit

For example:

$ ktutil
ktutil:  read_kt krb5.keytab
ktutil:  list
slot KVNO Principal
---- ---- ---------------------------------------------------------------------
   1    5  example-user@AD.EXAMPLE.COM
ktutil:  quit

Verify the keytab #

To verify the keytab by acquiring a service ticket, kinit can be used:

KRB5_CONFIG=<KRB5_FILE> kinit -c <CACHE_FILE> -V -k -t <KEYTAB_FILE> -S <SERVICE_PRINCIPAL_NAME> <PRINCIPAL_NAME>

For example:

KRB5_CONFIG=krb5.conf kinit -c krb5.cache -V -k -t krb5.keytab -S 'MSSQLSvc/prod-mssql.ad.example.com:1433' example-user

Base64 encode the keytab #

The keytab is in a binary format, so inputting it into the StrongDM Admin UI requires it to be Base64-encoded. This can be done using the following command:

base64 KEYTAB_FILE

Look up the key version number #

If the system has LDAP connectivity to the AD server, and has the package ldapsearch installed, it can be used to query the current key version number of a user for use in creating the keytab.

ldapsearch -h <AD_SERVER> -D <USER_ACCOUNT> -W -b <BASE_DN> <FILTER> <ATTR>

This prompts for the password of the user for authentication.

For example:

$ ldapsearch -h ad.example.com -D example-user@ad.example.com -W -b "cn=users,dc=ad,dc=example,dc=com" "(cn=example-user)" msDS-KeyVersionNumber
# extended LDIF
#
# LDAPv3
# base <cn=users,dc=ad,dc=example,dc=com> with scope subtree
# filter: (cn=example-user)
# requesting: msDS-KeyVersionNumber 
#

# example-user, Users, ad.example.com
dn: CN=example-user,CN=Users,DC=ad,DC=example,DC=com
msDS-KeyVersionNumber: 5

Keytabs on Windows #

Add an entry to the keytab #

On Windows, the ktpass command-line utility can be used to create a keytab:

ktpass -princ <WINDOWS_USERNAME>@<REALM_NAME> -pass <PASSWORD> -crypto <ENCRYPTION_TYPE> -ptype <PRINCIPAL_TYPE> -kvno <KEY_VERSION_NUMBER> -out <KEYTAB_FILE_PATH>

For example:

ktpass -princ example-user@AD.EXAMPLE.COM -pass * -crypto AES256-SHA1 -ptype KRB5_NT_PRINCIPAL -kvno 6 -out krb5.keytab

See ktpass for more details on its usage.

List entries in the keytab #

ktpass can also be used to list entries in an existing keytab. For example:

C:\Users\example-user>ktpass -in krb5.keytab
Existing keytab:

Keytab version: 0x502
keysize 89 example-user@AD.EXAMPLE.COM ptype 1 (KRB5_NT_PRINCIPAL) vno 6 etype 0x12 (AES256-SHA1) keylength 32 (0xfa8d4d926f1c32084d32cee7365e56edfb5ba53a0b209fb676f864e46130012b)

Base64 encode the keytab #

The certutil command-line utility can be used to Base64-encode a keytab file. This utility is intended for encoding certificates but can be used to generically encode in Base64 by removing the “BEGIN CERTIFICATE” and “END CERTIFICATE” header and footer from the generated file. For example:

certutil -encode krb5.keytab krb5.keytab-tmp.txt && findstr /v /c:- krb5.keytab-tmp.txt > krb5.keytab.txt

Look up the key version number #

The key version number of a user account is stored in AD as the msDS-KeyVersionNumber attribute of the user. The current value of this attribute can be determined from the command-line using dsquery, for example:

C:\Users\example-user>dsquery * -filter sAMAccountName=example-user -attr msDS-KeyVersionNumber
  msDS-KeyVersionNumber
  5

It is also possible to find this attribute using Server Manager. Open Server Manager and select Active Directory Users & Computers or ADSI Edit under Tools. Both of these tools allow browsing of the user attributes. Find the user and select Properties and then Attributes. Ensure the Constructed option is selected under Filters, as msDS-KeyVersionNumber is a generated attribute and therefore not editable.