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.
- Follow the steps to configure Always Encrypted by using Azure Key Vault , Column Master Key (CMK) is now stored in Azure Key Vault.
- Enable client application access to get the Application (client) ID and client secrete.
3. Grant the client application proper permissions (get, unwrapKey, and verify) in the Azure Key Vault.
- Create simple SSIS package in Visual Studio 2017 that uses an ODBC source and a flexible file destination. Configure connection string like below:
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=&%};
- Execute package successfully in Visual Studio.
- Deploy project to SSIS in Azure Data Factory.
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.