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=[firstname.lastname@example.org];PWD=[password];Authentication=ActiveDirectoryPassword;DATABASE=[database];ColumnEncryption=Enabled;KeyStoreAuthentication=KeyVaultPassword;KeyStorePrincipalId=[email@example.com];KeyStoreSecret=[password]
I expect that to insert into the table, but it results in this error:
[ADO NET Destination ] 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.