SSIS: Error from ODBC provider when inserting into Always Encrypted table using CMK in Azure Key Store

Question

I'm trying to use SSIS (Visual Studio 2017) to insert data into an Azure SQL table with a column that's encrypted via Always Encrypted, and a column master key stored in Azure Key Vault, but I keep getting errors.

Per advice I've found in other posts, I'm using an ADO.NET destination with the ODBC data provider. The user is an Azure Active Directory user that's provisioned in the Azure SQL database so it's able to log in. It has all key privileges granted to it in the key vault's access policy.

My ODBC connection string looks like this:

DRIVER=ODBC Driver 17 for SQL Server;SERVER=[dbs].database.windows.net;UID=[[email protected]];PWD=[password];Authentication=ActiveDirectoryPassword;DATABASE=[database];ColumnEncryption=Enabled;KeyStoreAuthentication=KeyVaultPassword;KeyStorePrincipalId=[[email protected]];KeyStoreSecret=[password]

I expect that to insert into the table, but it results in this error:

[ADO NET Destination [2]] Error: An exception has occurred during data insertion, the message returned from the provider is: ERROR [CE269] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Error 12038 sending request to https://[keyvault].vault.azure.net:443 ERROR [CE263] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Error verifying signature of ECEK. ERROR [CE202] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The keystore provider AZURE_KEY_VAULT failed to decrypt the ECEK https://[keyvault].vault.azure.net:443/keys/[cmk]/[version] with RSA_OAEP.

I'm able to successfully insert into the table via SSMS and also via a custom console app, and verify the encryption works as expected. The error only occurs from SSIS.

Answer 1

I spent lot of time lately to make this thing work. If you get the error 12038 with ECEK, then you need to update your master key in Azure SQL db without having :443. That creates the error when ODBC connection tries to match the url with the url in AKV. This is the short answer. I am planning on providing the whole solution as soon as I get some time to do it.