Using Liquibase and MSSQL Server with Integrated Security and Kerberos Authentication

If your application runs on a Linux or Windows-based intranet, you can use the integrated authentication to access your database. Integrated security uses the identity established on the operating system thread to access MSSQL Server.

Kerberos is an authentication protocol that works based on tickets to provide strong authentication for client/server applications by using secret-key cryptography. The Kerberos authentication protocol does not store passwords locally or send them over the Internet.

Prerequisites

  1. Introduction to Liquibase – Dive into Liquibase concepts.
  2. Install Liquibase – Download Liquibase on your machine.
  3. Get Started with Liquibase – Learn how to use Liquibase with an example database.
  4. Design Your Liquibase Project – Create a new Liquibase project folder and organize your changelogs
  5. How to Apply Your Liquibase Pro License Key – If you use Liquibase Pro, activate your license.

Prerequisites

  1. Set up Liquibase and MSSQL Server with Windows Integrated Security by following Using Liquibase and MSSQL Server with Windows Integrated Security.
  2. Set up Active Directory and integrate it with Kerberos.
  3. Have the krb5.conf file.

Configuration

To ensure the configuration of Liquibase and Windows Integrated Security works with Kerberos:

  1. Open the command prompt as an administrator and set the service principal name:
    2. setspn -s "MSSQLSvc/<dns>" "domain\username"

    Note: If you use a MSSQL Server RDS instance, convert the endpoint to a fully qualified domain name (FQDN). If the endpoint is <hostname>.czd233xxxyyyyyzz.us-east-1.rds.amazonaws.com, the equivalent FQDN is <hostname>.<parent domain>.<net or com, or others>.

  2. Verify that the service principal name works by running the following command:
    3. setspn -T * -F -Q MSSQLSvc/<hostname>*
    C:\Users\admin\Desktop>setspn -T * -F -Q MSSQLSvc/mssql-kerberos*
Checking forest DC=DOMAINNAME,DC=DOMAINNAME2,DC=net
CN=EC2AMAZ-BQEU7B1,OU=RDS,OU=AWS Reserved,DC=DOMAINNAME,DC=net
MSSQLSvc/mssql-kerberos.example.net:1433
...
Existing SPN found!
  3. Open SQL Server Management Studio as an Active Directory user and log in with Authentication set to Windows Authentication.

  4. From the command line, run the following query to ensure authentication is running with Kerberos:
    5. SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid ;

Tip: Kerberos usernames use the full Kerberos principal user username and must be uppercase. For example: USER@DOMAIN.COM

  1. Set up MSSQL Server and Active Directory. Check Using Liquibase and MSSQL Server with Windows Integrated Security and Active Directory authentication for SQL Server on Linux for more details.
  2. Create a keytab file using the ktutil utility:
    • From the command line, enter ktutil to launch the utility.
    • Enter the following commands:

      • Note: Username is the Kerberos principal user.

      add_entry -password -p <username> -k 1 -e des3-cbc-sha1-kd
	add_entry -password -p <username> -k 1 -e arcfour-hmac-md5
	add_entry -password -p <username> -k 1 -e des-hmac-sha1
	add_entry -password -p <username> -k 1 -e des-cbc-md5
	add_entry -password -p <username> -k 1 -e des-cbc-md4
    • Following the command prompt instructions, enter the password for the principal user.
    • Enter the following commands to create the keytab file:
      • wkt /path/to/<user name>.keytab
    • Enter q to quit the ktutil utility
    • Check the registered user in the keytab file by running the following command:
      • klist -k path/to/<username>.keytab
  3. Create the /etc/krb5.conf file.
    4. [logging]
 default = FILE:/var/log/krb5libs.log
 kdc = FILE:/var/log/krb5kdc.log
 admin_server = FILE:/var/log/kadmind.log
[libdefaults]
 clockskew = 6000
 forwardable = true
 default_realm = MYDOMAIN.COM
 default_ccache_name = /path/to/krb5cc
[realms]
MYDOMAIN.NET = {
 kdc = domainserver.mydomain.com
 admin_server = domainserver.mydomain.com
 }
[domain_realm]
 .MYDOMAIN.COM = MYDOMAIN.COM
 MYDOMAIN.COM = MYDOMAIN.COM
  4. Create the /etc/JDBCDriverLogin.conf file:
    5. SQLJDBCDriver {
  com.sun.security.auth.module.Krb5LoginModule required
  useKeyTab=true
  useTicketCache=true
  storeKey=true
  keyTab="/path/to/<keytabname>.keytab"
  principal="<AD username>@MYDOMAIN.COM";
};
KrbLogin {
  com.sun.security.auth.module.Krb5LoginModule required
  useKeyTab=true
  storeKey=true
  keyTab="/path/to/<username>.keytab"
  useTicketCache=true
  debug=true;
};

Note: For more information, see the Use Active Directory authentication with SQL Server on Linux page.

Configuring the Liquibase and Kerberos connection

  1. Run the kinit command to obtain and cache Kerberos ticket-granting tickets. To verify and list the new information from the krb5cc file, you can also run the klist command. For example: 
    2. kinit lbuser
Password for lbuser@MYDOMAIN.COM:
klist
Ticket cache: FILE:/temp/krb5cc
Default principal: @MYDOMAIN.COM
  2. Specify the database URL in the liquibase.properties file (defaults file), along with other properties you want to set a default value for. Liquibase does not parse the URL. You can either specify the full database connection string or specify the URL using your database's standard connection format:
    3. url: jdbc:sqlserver://<host name or endpoint>:1433;IntegratedSecurity=true;databaseName=<database name>;authenticationScheme=JavaKerberos;useTicketCache=true;doNotPrompt=true;moduleBanner=false;ServerSpn=MSSQLSvc/<hostname><FQDN>:1433@MYDOMAIN.COM;
  3. Include the following JAVA_OPTS arguments to ensure you have all the necessary Java arguments: 
    4. JAVA_OPTS="-Djava.security.krb5.conf=/path/to/krb5.conf -Dsun.security.krb5.debug=true -Dcom.sun.security.auth.module.Krb5LoginModule=required -DuseTicketCache=true -Djava.security.auth.login.config=/path/to/JDBCDriverLogin.conf"
  4. Run the liquibase status command to ensure the connection works.
    5. JAVA_OPTS="-Djava.security.krb5.conf=/etc/krb5.conf -Dsun.security.krb5.debug=true -Dcom.sun.security.auth.module.Krb5LoginModule=required -DuseTicketCache=true -Djava.security.auth.login.config=/etc/JDBCDriverLogin.conf" 
liquibase status --verbose
  5. (Optional) If you want to use the SQLCMD native executor with Kerberos, you must first set the KRB5CCNAME environment variable:
    6. export KRB5CCNAME=<path to ticket file>

    Then run Liquibase:

    KRB5CCNAME=<path to ticket file>
JAVA_OPTS="-Djava.security.krb5.conf=/etc/krb5.conf -Dsun.security.krb5.debug=true -Dcom.sun.security.auth.module.Krb5LoginModule=required -DuseTicketCache=true -Djava.security.auth.login.config=/etc/JDBCDriverLogin.conf" 
liquibase status --verbose
Starting Liquibase at 14:56:40
Liquibase Version: 4.5.0
Liquibase Community 4.5.0
>>>KinitOptions cache name is /tmp/krb5cc_1000
>>> KeyTabInputStream, readName(): MYDOMAIN.COM
>>> KeyTabInputStream, readName(): lbuser
>>> KeyTab: load() entry length: 68; type: 16
>>> KeyTabInputStream, readName(): MYDOMAIN.COM
>>> KeyTabInputStream, readName(): lbuser
>>> KeyTab: load() entry length: 60; type: 23
>>> KeyTabInputStream, readName(): MYDOMAIN.COM
>>> KeyTabInputStream, readName(): lbuser
>>> KeyTab: load() entry length: 52; type: 8
>>> KeyTabInputStream, readName(): MYDOMAIN.COM
>>> KeyTabInputStream, readName(): lbuser
>>> KeyTab: load() entry length: 52; type: 3
>>> KeyTabInputStream, readName(): MYDOMAIN.COM
>>> KeyTabInputStream, readName(): lbuser
>>> KeyTab: load() entry length: 52; type: 2
Looking for keys for: MYDOMAIN.COM
Java config name: /etc/krb5.conf
Loading krb5 profile at /etc/krb5.conf
...
...
>>> DEBUG: ----Credentials----
client: MYDOMAIN.COM
server: MSSQLSvc/xxx:1433@MYDOMAIN.COM
ticket: sname: MSSQLSvc/xxx:1433@MYDOMAIN.COM
startTime: 1635951401000
endTime: 1635987401000
----Credentials end----
Subject is readOnly;Kerberos Service ticket not stored
Using builtin default etypes for default_tgs_enctypes
default etypes for default_tgs_enctypes: 18 17 20 19 16 23.
>>> EType: sun.security.krb5.internal.crypto.ArcFourHmacEType
>>> CksumType: sun.security.krb5.internal.crypto.HmacMd5ArcFourCksumType
>>> EType: sun.security.krb5.internal.crypto.ArcFourHmaEType
>>> KrbKdcReq send: kdc=MYDOMAIN.COM TCP:88, timeout=30000, number of retries =3, #bytes=1482
>>> KDCCommunication: kdc=MYDOMAIN.COM TCP:88, timeout=30000,Attempt =1, #bytes=1482
>>>DEBUG: TCPClient reading 1442 bytes
>>> KrbKdcReq send: #bytes read=1442
>>> KdcAccessibility: remove MYDOMAIN.COM
>>> EType: sun.security.krb5.internal.crypto.ArcFourHmacEType
>>> EType: sun.security.krb5.internal.crypto.Aes256CtsHmacShaEType
>>> KrbApReq: APOptions are 00100000 00000000 00000000 00000000
>>> EType: sun.security.krb5.internal.crypto.Aes256CtsHmacShaEType
Krb5Context setting mySeqNumber to: 00000
Created InitSecContextToken:
0000: 01 00 6E 82 0B 91 30 82   0B 8D A0 03 02 01 05 A1  ..n...0.........
0010: 03 02 01 0E A2 07 03 05   00 20 00 00 00 A3 82 04  ......... ......
0020: EA 61 82 04 E6 30 82 04   E2 A0 03 02 01 05 A1 13  .a...0..........
0030: 1B 11 4C 42 2D 43 53 2E   44 41 54 49 43 41 4C 2E  ..MYDOMAIN.
0040: 4E 45 54 A2 3D 30 3B A0   03 02 01 00 A1 34 30 32  com.=0;......402
0050: 1B 08 4D 53 53 51 4C 53   76 63 1B 26 6D 73 73 71  ..MSSQLSvc.&mssq
...
Entered Krb5Context.initSecContext with state=STATE_IN_PROCESS
>>> EType: sun.security.krb5.internal.crypto.Aes256CtsHmacShaEType
Krb5Context setting peerSeqNumber to: 1481374XX
username@jdbc:sqlserver://xxx:1433;maxResultBuffer=-1;
sendTemporalDataTypesAsStringForBulkCopy=true;delayLoadingLobs=true;
useFmtOnly=false;useBulkCopyForBatchInsert=false;cancelQueryTimeout=-1;sslProtocol=TLS;
jaasConfigurationName=SQLJDBCDriver;statementPoolingCacheSize=0;serverPreparedStatementDiscardThreshold=10;
enablePrepareOnFirstPreparedStatementCall=false;fips=false;socketTimeout=0;authentication=NotSpecified;
authenticationScheme=JavaKerberos;xopenStates=false;sendTimeAsDatetime=true;trustStoreType=JKS;trustServerCertificate=false;
TransparentNetworkIPResolution=true;serverSpn=MSSQLSvc/xxx:1433@MYDOMAIN.COM;serverNameAsACE=false;sendStringParametersAsUnicode=true;
selectMethod=direct;responseBuffering=adaptive;queryTimeout=-1;packetSize=8000;multiSubnetFailover=false;loginTimeout=15;lockTimeout=-1;
lastupdate-count=true;encrypt=false;disableStatementPooling=true;databaseName=WF_DEV;columnEncryptionSetting=Disabled;applicationName=Microsoft JDBC Driver for SQL Server;
applicationIntent=readwrite; is up to date
Liquibase command 'status' was executed successfully.