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.