This tutorial walks you through the process on how to load data from Always Encrypted enabled Azure SQL database using SQL Server Integration Services (SSIS) in Azure Data Factory.
Azure-SSIS integration runtime has built-in Microsoft ODBC Driver 13 for SQL Server. In this tutorial, we use SSIS ODBC source with Microsoft ODBC Driver 13 to load data from Azure SQL database with Always Encrypted enabled using Azure Key Vault. Details about ODBC driver setup, refer to using Always Encrypted with the ODBC Driver for SQL Server via Azure Key Vault built-in keystore provider.
3. Grant the client application proper permissions (get, unwrapKey, and verify) in the Azure Key Vault.
DRIVER={ODBC Driver 13 for SQL Server};SERVER= <EnterYourServer.database.windows.net>;uid=<myuser>; DATABASE=<mydatabase>;ColumnEncryption=Enabled;KeyStoreAuthentication=KeyVaultClientSecret; KeyStorePrincipalId=<Application (client) ID>;KeyStoreSecret=\<client secrete>
Note:
Surround the value with { } when there are [ ] { ( ) , ; ? * = ! @ characters in the client secrete, and manually escape the interior } with a second }.
Example:
keystoresecret={)#|;_=&*{+>*-.&)$8?%}}|S|_^?|*=?.;o==_#-QA=&%};
7. Author data factory pipeline with execute SSIS package activity, input password in connection manager parameter.
8. Execute data factory pipeline.
Known limitation:
When writing to destination table which has the Always encrypted column, it requires more than double the size of the source column, if it is of the same size as the source it gives error.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.