How to use Always Encrypted in Azure SQL Database in Azure Data Factory Step by Step

 Issue: How to use Always Encrypted in Azure SQL Database in Azure Data Factory Step by Step.

In this article, we are going to learn how to use always Encrypted in Azure SQL Database in Azure Data Factory Step by Step.

How to create Azure SQL Database:

Open your Azure portal, in the portal search for the SQL databases, click on + Create, in the basics tab select your Azure subscription, select your resource group, name your database, then select server. 

Go to the configure and select the tier as per your workload/Requirement, and then click on Review + create and then create.

Once our database and server are ready, go to the server, then click on the Firewalls and Virtual networks, then click on ''Allow Azure services, and resources to access this server'' and make it Yes, then click on save.

How to create a master key in SSMS:

Open the SQL Server Management Studio and connect with the server.

Click on the Database, in the security tab click on the Always Encrypted keys, then name the master key, select the Azure Key Vault as your key store, sign in your account, select your Azure subscription, select the Azure key vault, generate the key and then click on ok.

Next, right-click on  Column Encryption Keys, click on the New column encryption key, name your column encryption key, choose the master key and click on ok.

To Encrypt a column or columns, right-click on the Table, click on ''Encrypt Columns'' and select single or multiple columns as per your requirement to encrypt.

Next, Open your Azure portal, go to the Azure key vault, click on the access policies under the settings tab, then click on + Add access policy.

First of all select the principal, which is the Azure data factory, then select the key permissions, Select secret permissions, then select certificate permissions and click on Add.

How to Create a Linked service:

Open your Azure Data Factory, go to the Manage tab, click on linked services, then click on + New to create a new linked service.

Select Azure SQL database, then click on continue.

Name your linked service, select Azure subscription, select the server name, select the database name, select the authentication type, provide the user name and password, select always encrypted and then click ok create.

How to create a Pipeline:

Go to the author tab, click on pipelines, click on + New sign to create a new pipeline, find and drag the copy data activity, then go to the source tab and click on + New to create a new source dataset.

Select Azure SQL database, then click on continue.

Name your dataset, select the linked service that we have created, select none for import schema and then click on ok

Once we are done with our source dataset, go to the sink tab and click on the + New button to create a new sink dataset where we will write our data.

Select the Azure blob storage and click on continue.

Select the output file format and click on continue.

Name your sink dataset, select the linked service, provide the file path, provide the file name, select the first row as header, as my file has the header so I have select it, select none for Import schema, then click on ok.

Once we are done with our sink dataset, go to the pipeline and click on Debug.

Video Demo: How to use Always Encrypted in Azure SQL Database in Azure Data Factory Step by Step