Setting up SSIS 2017 to read Always encrypted data with key in azure key vault

Question

I have a source table with a column that is encrypted using deterministic encryption with the key stored in azure key vault.

I get the following error when reading the data through an ADO.NET connection with Column Encryption Setting Enabled.

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "uspMoveToTarget" failed with the following error: "Failed to decrypt a column encryption key. Invalid key store provider name: 'AZURE_KEY_VAULT'. A key store provider name must denote either a system key store provider or a registered custom key store provider. Valid system key store provider names are: 'MSSQL_CERTIFICATE_STORE', 'MSSQL_CNG_STORE', 'MSSQL_CSP_PROVIDER'. 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.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Execute SQL Task

I have looked though a bunch of posts and they recommend using an ODBC connection passing in key vault specific parameters. The examples i have found online are mostly using an Azure application on top of an always encrypted table in a database encrypted with AKV keys. My case is for ETL only so there isnt an app on top of it. Just pure SSIS.

I have looked at this article but i am unable to get this to work as i am not sure which option to use and which values to pass into the keyvalue specific params.

https://learn.microsoft.com/en-us/sql/connect/odbc/using-always-encrypted-with-the-odbc-driver?view=sql-server-ver16

Using the info under "Using the Azure Key Vault provider" heading.

It is a onprem 2017 SQL Server and SSIS installation using Azure Keyvault for the encryption. I have added a azure group to the KeyVault access policies which enables my team and myself to work with the data without any issues in SSMS. When running similar code though SSIS we all get the error above.

Any help will be greatly appreciated!

Answer 1

Hi @Bryan Lemmer

• Can you try checking out the steps listed on following link and see if that works ?

https://learn.microsoft.com/en-us/azure/azure-sql/database/always-encrypted-azure-key-vault-configure?tabs=azure-powershell&view=azuresql

From error Invalid key store provider name: 'AZURE_KEY_VAULT'. it seems like its not supported out of box and you need to install some packages along with enabling some parameters in connection string to make it work.

• Also check out the steps listed on this answer as it could be due to permissions (rewriting them down here along with image from source).
  https://learn.microsoft.com/en-us/answers/questions/258903/support-always-encryption-on-azure-sql-db-by-stori.html For giving proper permissions:
  Go to Key Vault
  Select Access Policies from the Key Vault resource.
  Click the "add new" link/button at the top
  Select Principal to select the application that you are using (i.e. the app registration from which you got the client ID from)
  From the Key permissions drop down, make sure you give it "Decrypt", "Sign", "Get", "UnwrapKey" permissions
  Save changes.
  Source: Link

• Here is another link with some more help:
  https://learn.microsoft.com/en-us/answers/questions/690047/invalid-key-store-provider-name-39azure-key-vault3.html

Regards,
Shivam