Use SQL Server Always Encrypted with SSIS and Azure Key Vault

4
votes

Has anyone successfully used SSIS to pull and decrypt data from a SQL Server that uses Always Encrypted with key storage in Azure Key Vault (NOT Windows Cert Store, which i know is possible)?

Since setting up access to the Azure Key Vault involves programmatically registering providers to the SqlClient, I was wondering:

Does an SSIS Script Task share the same app domain / context as subsequent ADO.NET data flow tasks, as it will have to access the SqlClient providers, setup as follows:

var provs = new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>();
provs.Add(SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, azureKeyVaultProvider);
SqlConnection.RegisterColumnEncryptionKeyStoreProviders(providers);

Additionally, Script Tasks default to .NET 4.5, but Always Encrypted features are only available in 4.6.1. When i try to set the framework in the script editor, it always gets reset to 4.5 when i re-open the project.

I was hoping to find an example, as setting this up involves registering the Key Vault & all dependency assemblies in the GAC.

UPDATE May 5th, 2017 Thanks to @Josh G, i was able to get this 95% working using the ODBC Driver.

In my ODBC Source, i can now see unencrypted data when i choose 'Preview', however after running the package, i get the following errors:

Error: 0x384 at Broker, ODBC Source [12]: Open Database Connectivity (ODBC) error occurred. state: 'CE202'. Native Error Code: 0. [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The keystore provider AZURE_KEY_VAULT failed to decrypt the ECEK https://keyvault.vault.azure.net:443/keys/CMKAuto1/mykey with RSA_OAEP.

Error: 0x384 at Broker, ODBC Source [12]: Open Database Connectivity (ODBC) error occurred. state: 'CE269'. Native Error Code: 0. [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Error 12038 sending request to https://mykeyvault.vault.azure.net:443 Error: 0x384 at Broker, ODBC Source [12]: Open Database Connectivity (ODBC) error occurred. state: 'CE263'. Native Error Code: 0. [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Error verifying signature of ECEK.

2
c#sql-serverazuressisodbc
The 4.6.1 not sticking seems like a bug to me. My guess/hope is that the assembly that is serialized into the SSIS package when the project is compiled/saved it used the correct framework - otherwise the task would have invalid bytecode. My sinking feeling is that the OOB ADO.NET Source component in your dataflow won't know how to decrypt the data and you'll have to write a custom Script Component to access it. I don't have an AE instance or Azure key store to confirm, just lots of assumptions – billinkc
I know the OOB ADO works with a Windows Cert Store, so was hopeful that the above snippet would work for keyvault. – Cam Bruce
I was able to make it work with Driver 13.1 or greater. docs.microsoft.com/en-us/sql/connect/odbc/windows/… Also check whether access policies are set as for the Azure AD App or Identity you are using. get, create, delete, list, update, import, backup, restore, wrapKey,unwrapKey, sign – Vinay

2 Answers

2
votes

The short answer for this question is that it is possible to support the scenario requested if you use the ODBC driver to connect to an Always Encrypted database. The ODBC driver lets you specify AKV credentials in the connection string that the driver uses to connect with AKV and perform encryption/decryption operations against SQL.

Best, Josh

0
votes

It's a bit old thread, but updating here in case anyone gets similar error "[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Error verifying signature of ECEK."

Make sure the Service Principle/AD App which is used for Authenticating to Azure Key Vault has the following Key permissions.

Get, List, Decrypt, Unwrap and Verify