SSIS Data Flow into always encrypted column with Azure Key Vault

3
votes

Has anyone managed to get an SSIS Data Flow task into an always encrypted column with Azure Key Vault working?

I am running an SSIS Data Flow Task from a table in a database with not encrypted columns into a table in another database with always encrypted column and key in azure key vault. I am using an ODBC Destination with connection string DRIVER=ODBC Driver 13 for SQL Server;SERVER=myServer;Trusted_Connection=Yes;DATABASE=myDB;ColumnEncryption=Enabled;KeyStoreAuthentication=KeyVaultClientSecret;KeyStorePrincipalId=;KeyStoreSecret=

I am getting execution error:Operand type clash: nvarchar(50) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1'

enter image description here

1
ssisazure-keyvaultalways-encrypted

1 Answers

0
votes

After struggling with this for a couple of days, to get it working I had to first change the destination data type of the encrypted column to NVARCHAR instead of VARCHAR. Seems the dataflow task was automatically casting to NVARCHAR. The second change was to make the same SSIS package in Visual Studio 2019 SQL Server Integration Services Projects, which is still in Preview. In VS 2017 it was not working.