Support for encrypted column data for MSSQL

[hedo29]

Describe the feature

There doesn't appear to be a way to properly transact on encrypted columns from a SQL Server DB using the reactive client. SQL Server's JDBC driver does support this through explicit methods, and decrypts using a CMK/CEK pair.

Use cases

Any MSSQL DB that contains an encrypted column cannot use this project to interface with said data because it will not decrypt the data. This feature will allow a wider support to include all MSSQL DBs that contain encrypted data. Currently Vert.x is the only supported way to work with Hibernate Reactive, so this would be tremendous for anyone working with relational annotations in Java.

Contribution

I would be more than happy to take my best shot at implementing this feature, but I do not know anyone that is already looking for this feature and the level of complexity may be outside of my expertise, especially given my limited knowledge of Vert.x. I would be extremely grateful to the Vert.x developers if this was deemed a high enough priority for their attention.

[tsegismont]

@hedo29 thank you for reporting this.

Can you elaborate on your use case? Working with a cloud database or on premises? Can you give an example of schema definition and JDBC code?

[hedo29]

Sure! To be more specific, for my purposes I'd be working with an on-premise DB that is using MSSQL's Always Encrypted.

Encryption is enabled on specific tables/columns by T-SQL, so it's not something that has to be managed by Vert.x. Vert.x only has to see the metadata that comes back from the DB transaction when the query is executed:

If query results include data from encrypted columns, the Database Engine attaches encryption metadata for each column, including the information about the encryption algorithm, the encryption type, and key metadata to the result set.

This is supported from directly within Microsoft JDBC driver, so on the surface, the actual JDBC code wouldn't change.
For the sake of example, though, let's say I have the following as my schema:

Table users:

• firstName (nvarchar)
• lastName (nvarchar)
• userName (nvarchar)
• ssn (nvarchar) (encryption enabled)

When I make a JDBC call to retrieve a user's information, my code will simply look like this (identical to if it had no encrypted values):

// setup connection code omitted. assume java keystore path/pass is provided via jdbc url
try (Connection sourceConnection = DriverManager.getConnection(connectionUrl);
        PreparedStatement selectStatement = sourceConnection
                .prepareStatement("SELECT [userName], [firstName], [lastName], [ssn] FROM [dbo].[users] WHERE userName= ?;");) {
    selectStatement.setString(1, "hedo29");
    ResultSet rs = selectStatement.executeQuery();
    while (rs.next()) {
        System.out.println("SSN: " + rs.getString("ssn") + ", FirstName: " + rs.getString("firstName") + ", LastName:"
                + rs.getString("lastName") + ", UserName: " + rs.getString("userName"));
    }
}
catch (SQLException e) {
    e.printStackTrace();
}

The JDBC driver contains all of the logic necessary to do the following:

1. When an application issues a parameterized query, the SQL client driver within the application transparently contacts the Database Engine [by calling sp_describe_parameter_encryption (Transact-SQL) to determine which parameters target encrypted columns and should be encrypted. For each parameter that needs to be encrypted, the driver receives the encryption algorithm, encryption type, and key metadata, including the encrypted column encryption key and the location of its corresponding column master key.
2. The driver calls the key store, containing column master keys in order to decrypt the encrypted column encryption key values. The resultant plaintext column encryption keys are cached to reduce the number of round trips to the key store on subsequent uses of the same column encryption keys.
3. The driver uses the obtained plaintext column encryption keys to encrypt the query parameters corresponding to encrypted columns.
4. The driver substitutes the plaintext values of the parameters targeting encrypted columns with their encrypted values, and it sends the query to the Database Engine for processing.
5. The Database Engine executes the query, which may involve equality comparisons on columns using deterministic encryption.
6. If query results include data from encrypted columns, the Database Engine attaches encryption metadata for each column, including the information about the encryption algorithm, the encryption type, and key metadata to the result set.
7. The Database Engine sends the result set to the client application.
8. For each encrypted column in the received result set, the driver first tries to find the plaintext column encryption key in the local cache, and only makes a round trip to a key store holding the column master key if it can't find the key in the cache.
9. The driver decrypts the results and returns plaintext values to the application.

All of this occurs from directly within the driver code itself. The JDBC code is open source and can be found here.

For my use case, and what I imagine would work to cover most others who are using always encrypted, using a Java keystore along with the corresponding URL parameter (currently columnEncryptionSetting=enabled ) would likely suffice.

In order to enable all of this, the Vert.x client would likely have to replicate the above behavior in the MSSQL-specific driver. Without this behavior, Always Encrypted support is not possible for MSSQL DBs, or at least retrieving/querying any encrypted column is not possible.

[tsegismont]

Thank you for the details. This is would be a nice to have, would you like to contribute it? I can assist you in the process