[QUESTION] Invalid key store provider name: MSSQL_CERTIFICATE_STORE

[botekchristophe]

Question Invalid key store provider name: MSSQL_CERTIFICATE_STORE

Hi,

I've a SQL server 19 on which I was given some encrypted test data to play with. I was also given a .pfx file generated with SSMS and now I'm trying to decrypt the data from a linux server using apache spark. I've been successful at reading the encrypted data and displaying the binary data. But for some reason when I try to decrypt an encrypted column I get the following error.

org.apache.spark.SparkException: Job aborted due to stage failure: Task 0 in stage 18.0 failed 1 times, most recent failure: Lost task 0.0 in stage 18.0 (TID 18) (jupyter-cbotek executor driver): com.microsoft.sqlserver.jdbc.SQLServerException: Failed to decrypt a column encryption key. Invalid key store provider name: MSSQL_CERTIFICATE_STORE. A key store provider name must denote either a system key store provider or a registered custom key store provider. Valid system key provider names are: [MSSQL_JAVA_KEYSTORE]. Valid (currently registered) custom key store provider names are: []. Please verify key store provider information in column master key definitions in the database, and verify all custom key store providers used in your application are registered properly.

The documentation seems to say that I only need to provide a .pfx or a .jks keystore and that I should be able to read the data. I've been following the documentation here https://docs.microsoft.com/en-us/sql/connect/jdbc/using-always-encrypted-with-the-jdbc-driver?view=sql-server-ver15

I've setup my connectionUrl like this, trying both a .pfx file and a .jks file but receiving the same error each time.

val connectionUrl = "jdbc:sqlserver://xxxx:1433;databaseName=test-always-encrypted;" +
    "user=xxxxx;password=xxxxx;" +
    "domain=xxxx;authenticationScheme=NTLM;" +
    "columnEncryptionSetting=Enabled;integratedSecurity=true;" +
    "keyStoreAuthentication=JavaKeyStorePassword;" +
    "keyStoreLocation=~/keystore.jks;" + // or "keyStoreLocation=~/keystore.pfx;"
    "keyStoreSecret=xxxxxx;"

also, the spark code itself

spark.read.format("com.microsoft.sqlserver.jdbc.spark")
      .options(options ++ Map("query" -> s"SELECT * FROM xxx.Encrypted"))
      .load()
      .show(false)

using these dependencies

MSSQL_JDBC="com.microsoft.sqlserver:mssql-jdbc:8.4.1.jre8"
ADAL4J="com.microsoft.aad:adal4j:0.0.2"
MSSQL_SPARK_CONNECTOR="com.microsoft.azure:spark-mssql-connector_2.12:1.2.0"

Thank you for you help

[VeryVerySpicy]

Hello @botekchristophe, Do you receive the same error when using a stand-alone java application doing a select?

[botekchristophe]

Hi @v-zhangw , yes I have the same issue on a standalone java app.

Do you know what the error means ?

Failed to decrypt a column encryption key. Invalid key store provider name: MSSQL_CERTIFICATE_STORE.
A key store provider name must denote either a system key store provider or a registered custom key store provider.
Valid system key provider names are: [MSSQL_JAVA_KEYSTORE].

How do I set the keystore provider to MSSQL_JAVA_KEYSTORE ? Am I missing a configuration in the url ?

[David-Engel]

@botekchristophe

The MSSQL_CERTIFICATE_STORE is only available on Windows (it uses the Windows Certificate Store). Since your Java client is running on Linux, that key store provider is not available in the JDBC driver.

A client driver interacts with a key store, containing a column master key, using a column master key store provider, which is a client-side software component that encapsulates a key store containing the column master key. Providers for common types of key stores are available in client-side driver libraries from Microsoft or as standalone downloads. You can also implement your own provider. Always Encrypted capabilities, including built-in column master key store providers vary by a driver library and its version.

The way Always Encrypted works is that encryption keys are only known to the client, not the server. The database has some metadata about where the client can find the encryption keys. One piece of that metadata is a key store provider name. In your case, it's been set up to use the MSSQL_CERTIFICATE_STORE provider. To see what platforms the built-in providers are available on, see the table in the docs here: https://docs.microsoft.com/en-us/sql/connect/jdbc/using-always-encrypted-with-the-jdbc-driver?view=sql-server-ver15#using-built-in-column-master-key-store-providers

If you are going to mix and match different technologies across clients (Java, C#, Windows, Linux, etc), you need to pick a provider that is available for all of them. While the MSSQL_JAVA_KEYSTORE is available on Windows and Linux in the JDBC driver, it's not available to SSMS users. Currently the only available built-in provider that is cross-technology and cross-platform is the AZURE_KEY_VAULT provider.

Regards, David

[botekchristophe]

@David-Engel thank you for the detailed answer, it's very appreciated !

As you may understand it already, the encryption and the decryption are done by different codes and different teams in my organization. Does the team in charge of the encryption could use a MSSQL_JAVA_KEYSTORE with let say SSIS running on windows ? Or, can they set manually the metadata in the db to MSSQL_JAVA_KEYSTORE instead of MSSQL_CERTIFICATE_STORE ? Could I override the metadata configuration and set the provider to MSSQL_JAVA_KEYSTORE when my code is trying to read the data ?

We can live with a solution that doesn't include SSMS as it would even increase the overall security. We'll consider AZURE_KEY_VAULT if it's the only solution.

Thanks a lot for your time

[David-Engel]

Does the team in charge of the encryption could use a MSSQL_JAVA_KEYSTORE with let say SSIS running on windows ? They would have to have SSIS launch an external Java application to process the data (if that's possible). Probably not possible.

Or, can they set manually the metadata in the db to MSSQL_JAVA_KEYSTORE instead of MSSQL_CERTIFICATE_STORE ? Could I override the metadata configuration and set the provider to MSSQL_JAVA_KEYSTORE when my code is trying to read the data ?

No. Another piece of AE column metadata is the key path. This is specific to the key store provider so you can’t mix key store providers across different clients. The key path is not the same for them. A key path for the MSSQL_CERTIFICATE_STORE looks something like this: 'CurrentUser/My/A2A91F59C461B559E4D962DA9D2BC6131B32CB91' A key path for the AZURE_KEY_VAULT provider is a URL like this: 'https://.vault.azure.net:443/keys/Always-Encrypted-Auto1/c61f01860f37302457fa512bb7e7f4e8' A key path for the MSSQL_JAVA_KEYSTORE looks something like this: 'AlwaysEncryptedKey'

Another option is to implement a custom key store provider for each driver/technology you are using. Then you control the key path and what it means to the provider. But since you mentioned SSIS, I'm not sure how you would register it since registration is done programmatically by the application. If you use Azure, the Azure Key Vault provider should be available in all drivers. (Not 100% sure how to use that one with SSIS, though, unless SSIS has a UI option for it.)