SQL Server Always Encrypted Key Store Generic Provider

This solution provide 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 store described below in following table :

Provider Name	Class	Details
MSSQL_CERTIFICATE_STORE	SqlColumnEncryptionCertificateStoreProvider	Represent 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 https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/configure-always-encrypted-keys-using-powershell?view=sql-server-2017#KeyProvisionWithRoles. All steps are described in this document.

   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 (exemple: jdbc:sqlserver://server:1433;databaseName=CLINIC;user=admin;password=P@ssw0rd";columnEncryptionSetting=Enabled;keyStoreAuthentication=JavaKeyStorePassword;keyStoreLocation=$HOME/CLINIC-CMK.pfx;keyStoreSecret=SecretP@ssw0rd");)

Security Concerns

From my opinion there is no real reasons for the CMK metadata to store both the provider and key path. It should be the client responsability to provide the right key store and key path.

If it is intended to ensure that only one kind of client can decrypt the values, then you probably don't really know to who you gave the certificate.

If an attacker gain access to a cient able to decrypt the database, the attacker can. If worst the attacker gain access to the SQL Server, it will probably be very easy to gain access to a client able to decrypt the database.

From the last two sentences, using the AzureKeyVault provider seems a bit more secure, because any client (Windows/Linux) may have access to the web, and it would be probably challenging for an attacker to gain access to the key.

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 CMK.

This solution highlight 2 issues regarding usage of a custom/generic provider. These issues are detailed in:

• PS Module unable to retrieve a registered custom provider
• Already registered custom provider

This solution provide in order:

• Setup SQL sample database/table
• a SQLColumEncryptionGenericKeyStoreProvider for both JDBC Driver and .NET Driver
• Extended Always Encrypted cmdlets (for the PowerShell SqlServer module) to bypass issue encountered.
• the patched Microsoft.SqlServer.Management.AlwaysEncrypted.Management.dll (ensure to bypass strong name verification, use at your own risk) to bypass issue encountered.
• sample to access encrypted data with both .NET Driver and JDBC Driver

Results

• a generic CMK
• CEK encrypted with the not so generic CMK
• Database encryption
• .NET client use the underlying MSSQLStoreProvider
• JDBC client use the underlying JAVAKeyStoreProvider
• Patch the DLL
• Authorize the patched DLL
• Import SQLServer module
• Import Extended Always Encrypted cmdlets ()
• Create the underlying provider you want to use.
• Create the generic provider
• Register the generic provider (Microsoft issue 1)

Known Issues

• SQL Server Management Studio cannot decrypt columns when setting "Column Encryption Setting=enabled". Or we should access the .NET assemblies loaded by the 'smss' process and register the generic provider into the SqlConnection class loaded by the process. I even don't know if this can be done (easily I mean). Nevertheless, it may be possible to update the IL code of any assembly (remove Strong Name verification for this assembly) loaded by ssms.exe to provide a static constructor (in any existing class) that would load the assembly of the generic provider (the generic dll) and create an instance of this generic provider with the right parameters to access the key, and register it. This would allow ssms.exe to decrypt columns.

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'...

Licence

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