Azure SQL PaaS Database | Encrypting columns with PI Data

Question

Hello,

We have provisioned Azure SQL PaaS DB, this database will contain tables which will contain personally identifiable data.

We have to cherry pick columns from the tables and do encryption.

From the documentation here : https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data?view=sql-server-ver15

I can see that we can encrypt column data. In regards with that, I have below questions.

1) Is it supported only for Azure Managed Instance? Not a single database?
2) The developers should not be able to view the PI data. When a query is triggered, the encrypted data should be returned. However, the DB admin should be able to view the data using the key. How can this be accomplished?

Thanks,
Prasenna

Answer 1

Maybe Dynamic data masking is the feature that you are looking for

https://learn.microsoft.com/en-us/azure/azure-sql/database/dynamic-data-masking-overview

https://www.mssqltips.com/sqlservertip/4213/configure-and-customize-sql-azure-dynamic-data-masking/

With this feature, only admin can view the complete data and others see masked data like below

----------

Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav

Answer 2

@Vaibhav Chaudhari : Fantastic, thanks!