How to enable Low Level Security in Azure SQL Database with Always Encrypted and Dynamic data masking

Why should we enable Low Level Security in SQL Database?

Database has been always the core of any application because it contains different types of data like PII (Personal Information Identity), Transactional, Operational etc. So, Database should be secure from all kind of vulnerabilities. There are so many ways to protect database from high level to low level as per here but here i am going to discuss low level security i.e, Always Encryption and Dynamic Masking.

Low Level Security is required because Database contains Public, Confidential and Strict Confidential types of data which shouldn't be breach at point of time. Personal Data always belongs to Confidential category so Confidential and Strict Confidential types of data should always stored in encryption format not as plain text in database and this is mandate as per GDPR policy also. This we can achieve through Always Encrypted and Dynamic data masking.

Configure Always Encrypted with Azure Key Vault

Microsoft has documented this process very elaborative here just they have not mentioned what access policy needs to be setup in Azure Key Vault.

Azure Key Vaults provide three ways to store data securely Keys, Secrets and Certificates and to access any section we need to setup proper access policy through Access Policy of Azure Key Vault. We need to provide access to current user user who is configuring and SQL server also. You need to select correct types means whether you are using either Keys or Secrets or Certificates or combination of twos or all. Depending upon selection you will get an option to set permissions for each action.

Here we are working with Keys only, also will use keys for encryption and decryption. So, we should follow least privilege's access policy and provide access related to only required section not all.

The same process we have to follow for service principal, if we are using and those users also who want to see encrypted text through SSMS. This is also mentioned at bottom of document.

To use SSMS to access the plaintext data, you first need to ensure that the user has proper permissions to the Azure Key Vault:?get,?unwrapKey, and?verify. For detailed information,

Dynamic data masking

Dynamic data masking is nothing but just hide the actual data and hope so that you would be familiar also. You would have seen on your credit card statement where complete card number is not displayed, on DigiLocker your complete Aadhaar number/DL number is not displayed. This behavior is Dynamic data masking and we can enable as per here.

If you are seeing below message or error screen while encryption/decryption then you have to adjust only access policy. This happens when you provide full permission that's why provide only those permissions what is required only.

microsoft.sqlserver.management.servicemanagement.resourcemanagement.azurekeyvaultkeypermissionenumconverter.readjson(jsonreader reader, type objecttype, object existingvalue, jsonserializer serializer) at newtonsoft.json.serialization.jsonserializerinternalreader.deserializeconvertable(jsonconverter converter, jsonreader reader, type objecttype, object existingvalue) at