SQL Server Always Encrypted Key Store Generic Provider

This solution provides a workaroud for the SQL Server Always Encrypted feature, for interoperability between clients that do not share any common Key Store Provider. Especially, this is intended for OS interoperability (Windows/Linux) not using Azure (or with no access to Internet).

Always Encrypted definitions

• SQL Server Always Encrypted documentation
• Column Master Key / Column Encryption Key overview

Column Master Key

Colum Master Key (CMK) represent a key or generally a certificate. Clients accessing the SQL Server must have access to both the public and private keys of the certificate.

Colum Encryption Key

Column Encryption Key (CEK) represent a key used to encrypt the values stored in a database column. The CEK is encrypted with the CMK.

Database Columns

Columns of the database are encrypted with the Column Encryption Key (CEK) using either a Deterministic or Randomized algorithm.

Keys/Certificate Store

Always Encrypted feature comes with some builtin key stores described below in following table :

Provider Name	Class	Details
MSSQL_CERTIFICATE_STORE	SqlColumnEncryptionCertificateStoreProvider	Represents the Windows Certificate Store
MSSQL_CNG_STORE	SqlColumnEncryptionCngProvider
MSSQL_CSP_PROVIDER	SqlColumnEncryptionCspProvider
MSSQL_JAVA_KEYSTORE	SQLServerColumnEncryptionJavaKeyStoreProvider	only available with the JDBC Driver
AZURE_KEY_VAULT	SqlColumnEncryptionAzureKeyVaultProvider	available for both JDBC/.NET Driver but considered as a custom provider, not registered by default

Interoperability Issue

The following architecture schema describe in details how the SQL Server Always Encrypted feature works. Each arrow is described below in order.

Key generation and deployement

1. You generate a certificate (public/private keys) and you deploy this keys to clients allowed to decrypt columns.

2. You have a Windows Client (MSSQL_Certificate_Store) and an Unix client ( JavaKeyStore), you provide the certificate to both clients.

   1. You import the certificate (.pfx) in the Windows certificate store on the Windows client.
   2. You store the certificate on the file system as file (.pfx) for the JDBC client.

CMK / CEK / Database Columns Encryption

3. To stick on a real production example, you configure Always Encrypted keys provisioning with role separation as described in Microsoft documentation. All steps from this documentation are detailed here.

   1. The security administrator with access to the certificate private key generate an encrypted value for the CEK.

   2. The DBA administrator get this encrypted value and generate both CMK (with the metadata: the Key Store Provider and the Key Path).

   3. The security administrator can now encrypt colums.

.NET Client Data Access

4. The .NET client connect to the database and attempt to decrypt encrypted values in encrypted columns.

   1. Internally, the .NET Driver call the store procedure sys.sp_describe_parameter_encryption

   2. [supposed] The .NET Driver read the metadata of the CMK and check if it has access to the provider (MSSQL_Certificate_Store) and key path.

   3. The database return encrypted values.

   4. The .NET Driver can decrypt the encrypted values.

JDBC Client Data Access

4. The JDBC client connect to the database and attempt to decrypt encrypted values in encrypted columns.

   1. Internally, the JDBC Driver call the store procedure sys.sp_describe_parameter_encryption

   2. [supposed] The JDBC Driver read the metadata of the CMK and check if it has access to the provider (MSSQL_Certificate_Store) and key path.

   3. The client does not have any knowledge of the MSSQL_CERTIFICATE_STORE. It cannot access the key to decrypt values. Whatever you provide in your connectionstring the use of a JAVA_KEY_STORE, path to the file, and password. example: "jdbc:sqlserver://server:1433;databaseName=CLINIC;user=admin;password=P@ssw0rd";columnEncryptionSetting=Enabled;keyStoreAuthentication=JavaKeyStore;keyStoreLocation=$HOME/CLINIC-CMK.pfx;keyStoreSecret=SecretP@ssw0rd" ). The JavaKeyStore settings are ignored by the driver.

Security Concerns

From my opinion, I still do not understand the reasons for the CMK to store metadata about the provider and the key path.

Worst, I find that this metadata reveals too much information about the key location (store provider and path).

The generic provider used here finally from a security point of view reveals nothing about the key location. And it's finally the client responsibility to provide the right information to access encrypted columns.

Issues discovered

The development of this generic provider for interoperability between operating system highlighted some issues in the original source code from Microsoft, and in a less measure in the Oracle JRE/JDK.

These issues are detailed in following documents:

• Microsoft : PS Module unable to retrieve a registered custom provider
• Microsoft : Already registered custom provider
• Microsoft / Oracle JDK : Windows Server 2016 certificate are not compatible with the current publicly available JRE/JDK

Solution

Using the provided documentation, its possible to create a generic key store wrapping an underlying real keystore. This solution provide interoperability for clients (Windows/Unix), and do not expose any hint about the path to the key.

This solution provide in order :

1. The setup of the SQL database table.

2. The generation of the certificate used to encrypted columns and deployed to both the JDBC client (as .pfx file for the MSSQL_JAVA_KEYSTORE provider) and the .NET client (as .pfx imported into the Windows Certificate store for the MSSQL_CERTIFICATE_STORE provider). The certificate generation has currently some restrictions detailed in the aformentioned document.

3. the patched Microsoft.SqlServer.Management.AlwaysEncrypted.Management.dll (ensure to bypass strong name verification, use at your own risk) to bypass issue encountered.

4. Extended Always Encrypted cmdlets that allow to bypass issue encountered. These cmdlets are extensions for the Always Encrypted Microsoft cmdlets included in the SqlServer PowerShell module.

5. a SQLColumEncryptionGenericKeyStoreProvider class implementation for both JDBC Driver and the .NET Driver. This generic provider is compiled in DLL for usage in the PowerShell configuration of the SQL Server Always Encrypted feature.

6. The complete documentation to setup the environment (computer wide, powershell-session wide) and all steps to create, register and use the generic provider, generate the CMK, the CEK and encrypt columns of the table in the database.

7. samples to read encrypted data with both .NET Driver and JDBC Driver :

   • Sample .NET Driver
   • Sample JDBC Driver

Results

• The generic provider CMK (Generic Key Store provider, no meaningfull Key Path) :

Known Issues

SQL Server Management Studio cannot decrypt columns when using the generic provider

SQL Server Management Studio cannot decrypt columns when setting "Column Encryption Setting=enabled". This is because when ssms.exe process start, the generic provider is not registered/available into its process's memory.

Using the following script on a table with encrypted columns, the following error happens :

SELECT [PatientID]
      ,[SSN]
      ,[FirstName]
      ,[LastName]
      ,[MiddleName]
      ,[StreetAddress]
      ,[City]
      ,[ZipCode]
      ,[State]
      ,[BirthDate]
  FROM [CLINIC].[dbo].[Patients]

Output:

Msg 0, Level 11, State 0, Line 0<br />
Failed to decrypt column 'SSN'.<br />
Msg 0, Level 11, State 0, Line 0<br />
Failed to decrypt a column encryption key. Invalid key store provider name: 'GENERIC'...

Solution 1

Accessing the memory of the process to register the generic provider is probably not an easy task.

Solution 2

Another option may relie on the IL code modification to patch any Microsoft assembly loaded by the ssms.exe process :

• discover an assembly loaded by ssms.exe process (preference for a dedicated assembly for the process, not a shared one).
• include a static constructor on any class of this assembly.
• in the static constructor, dynamically load the assembly of the generic provider (the provided dll).
• create an instance of the real provider MSSQL_CERTIFICATE_STORE (SqlColumnEncryptionCertificateStoreProvider).
• create an instance of the generic provider with the wrapped instance of MSSQL_CERTIFICATE_STORE and path to the key of this wrapped provider.
• create a dictionnay of custom providers, adding the generic provider to this dictionnary.
• Register this generic provider in the custom provider, calling SqlConnection.RegisterColumnEncryptionKeyStoreProviders() method.
• Remove Strong Name verification for the updated assembly.

References Documentation

• Always Encrypted (Database Engine)
• Using Always Encrypted with the JDBC driver

Licence

No licence, used to document and report. Use at your own risk.