Integrating SQL Server Always Encrypted into Java Application

SQL Server allows column level encryption using feature called Always Encrypted. The application controls encryption of fields such as credit card numbers. Separating responsibilities as the encryption key is not revealed to the database.

In this blog I will work through my Encrypted-DB project on GitHub. The project includes example code and Unit Tests to show how to use this feature.

Using Always Encrypted is an easy and effective way to encrypt sensitive data. Data remains encrypted over the network. The application handles decryption via the driver and the KeyStore.

SQL Server 2016 introduced this feature but the JDBC documentation is lite. Microsoft released its JDBC documentation in May 2020 but does work.

SQL Server 2016 or higher

Microsoft’s JDBC Driver 6.0 or higher

Microsoft’s driver supports 3 different KeyStores Azure KeyVault, Windows Certificate Store, and Java KeyStore. For this article we will be using the Java KeyStore. For more information about other KeyStore Implementations you can read Microsoft’s Documentation.

To begin first generate a new KeyStore and Certificate using the commands below. If you have a KeyStore and Certificate skip this step.

The Java KeyStore requires creation of the Column Master Key as shown below.

Create Column Encryption Key using the KeyStore created in the prerequisites and AlwaysEncrypted.java.

Create encrypted byte array from SQL Server Column Encryption Key Store provider and string as shown below. Then convert byte array to a hex string and store in the database.

Use the method below to convert the byte array created above to the hex string required for the database.

Now that we have Column Master and Encryption Keys we can now create the schema and tables. We will create 1 schema with 2 different tables each table will contain 1 encrypted column. There are 2 different encryption types Randomized and Deterministic. If you need to use the column in a join statement then you must use Deterministic. For this example we will not be using the column to join 2 tables so we will use Randomized.

Now that we have our Database set up we can integrate this with out Java Application. You can see examples of the DAO’s in my Encrypted-DB project on GitHub.

Note encrypted columns must be set using a PreparedStatement or CallableStatement as shown below. Using the set methods is the only way the driver knows to encrypt the field.

Now that we have the DAOs written we can write a test to verify we can insert and retrieve the data. For this project we will use Testcontainers to integrate a SQL Server 2019 Database.

To connect to the database we will set up 2 different connections. The first will enable always encrypted by setting the column encryption setting as well as the location to the KeyStore and secret.

The test above will produce output like below. First using the always encrypted enabled datasource second using the always encrypted disabled.

SQL Server 2019 Microsoft released a new feature Secure Enclaves which does not currently support use of the Java KeyStore. Also SSIS and SSMS do not support the Java KeyStore.

Currently supported frameworks include Hibernate, JDBCTemplate, JDBC but MyBatis is not.

Thanks to Clinton Teegarden