I have a control table in ADF pipeline with total of 36tables in it. I want to encrypt and decrypt storage account target file data columns. How can i get this done? Please Note: Encryption will not be done in SQL Server

Question

I am about to begin an SFTP process and need to encrypt some tables columns in a control table in Azure Data Factory. How best can this be done.

Answer 1

Hi Godswill Mbata ,

Welcome to Microsoft Q&A platform and thanks for posting your query here.

As per my understanding , you want to encrypt and decrypt column level data present in files using ADF.

Encrypt Azure Data Factory with customer-managed keys feature is present to encrypt the data factory environment i.e. to encrypt data that datafactory storing in the system. Unfortunately there is no out of box feature in Azure Data factory to perform encryption/decryption of columns in files.

Though you can encrypt the data in Storage account and also in ADF separately using customer-managed public key.

Go the storage account and click on Encryption on the left side of the panel. Select the key vault and the key which you want to encrypt the data. In ADF also, go to the Manager option on the left panel and click on Customer managed key and add Key URL to encrypt the ADF environment and data associated with it.

However, You can encrypt a column of data by using symmetric encryption in SQL Server using Transact-SQL: Encrypt a Column of Data

You can also control access to table columns based on the user's execution context or group membership: Column-level security

Additional resources :

SFTP: Encrypting or Decrypting Files with PGP

https://learn.microsoft.com/en-us/answers/questions/1042526/decryption-or-encryption-of-pgp-files-in-adf

https://medium.com/couchdrop-io/sftp-encrypting-or-decrypting-files-with-pgp-266bb964c8b9

Hope it helps. Thankyou