Quick Track: Beginner’s Guide to Azure SQL- Data Protection

Source: Pexels

Data needs to be protected no matter where it lives, On-prem or in Azure. Data can be protected by using the encryption that Azure provides. What are the types of encryption we have in Azure?

Encryption in Transit: The data needs to be protected as it moves all the way from the database to the client application and vice-versa. The encryption is always by default enforced by TLS (Transport Layer Security). Making sure your application supports the TLS version you choose on your server is important. If for example, you chose the TLS version 1.2 on your server and your client application only supports the 1.2 TLS version, your application cannot connect to the server.

As a best practice from Microsoft, client applications should have the encryption enabled on their connection string and not trust the Server certificate.

Encryption-at-rest: Data at rest on the disks- which includes the data files, log files and backup files. These are encrypted with the technology called Transparent Data Encryption.

This option is enabled by default as well. As the data is moved in and out of the drives, the data is being protected through the transparent data encryption. By default, Microsoft is going to manage the keys known as Microsoft-managed keys but also you can choose bring your own keys (BYOK). In the default option Microsoft-managed keys, the responsibility of the key generation, key rotation and the management of these keys are taken care by the Microsoft. All these keys need to be stored in the Key vault which is a Microsoft product. In the second option BYOK, the customer is responsible for the management of the keys including the key generation, key access to the users and key rotation. The third option we have is Hold your own key (HYOK) which is a IaaS offering only where the key generation including the key management and storing the keys is totally taken care by the customer.

Double layer of encryption is where you can chose the TDE along with the infrastructure encryption (volume encryption). This is an additional layer of security for your data on disk. The second layer Infrastructure encryption is optional choice.

In the case of customer managed key, the server identity should have the access to the key vault. So, when the server wants to encrypt and decrypt the data on the drive, it will use this key. The first step to create the customer managed key is to create the Key Vault if you do not already have one.

Once you created the Key vault, choose the customer managed key. To select a key, first we need to create a key that is saved in the key vault. Here I have created two keys.

Then go to the access policies on the Key vault and give the server (Securitydemo) enough permissions to access the key. That requires Wrap and unwrap permissions.

Now go to the transparent data encryption and choose the key by clicking on the Select a key and then click on change key.

Choose the key you have created and click on select

Once you select the key, click on Save on the transparent data encryption page.

Encryption-in-use: It encrypts the data during the query processing and the technology used is always encrypted. This is the client side encryption technology. Cryptographic keys that are used to encrypt and decrypt the data are never shared with anyone. The SQL client driver in between the client application and the database will serve as the mediator to encrypt and decrypt the data as the data moves in and out of the database. Initially only the equality searches were only supported but to add more functionality in supporting the range queries, pattern matching, sorting, indexing and more operations, a new technology is being introduced in SQL Server 2019 known as Secure Enclaves.

Secure Enclave is a part of memory in the SQL Server that no one can access and this part of memory is used to process the computational operations involving the sensitive encrypted column. In this part of the memory, the secure enclave will have the data which is decrypted safely and queries can be processed here.

How do we know that the Secure enclave is secure to process the sensitive data? There is a service known as Microsoft Azure attestation which will check if the secure enclave is safe and process the queries. By using the Secure Enclaves, even the windows administrators or DBA’s cannot access the sensitive data on always encrypted columns.

Dynamic Data Masking

Dynamic Data masking is a protection of the data from the application users who should not have access to specific information. This is not the encryption where the files are encrypted.

This is limiting the access to the data where the data is masked on the fly when the data is moved from the database to the application. As the data reaches to the application, only the needed information needs to be shown to the application users. You can use the custom built functions or create the custom functions to mask the data.


Ledger is a technology which uses blockchain technology to protect the data tampering from the privileged users who have access to the sensitive data. This involves the ledger tables built in as you enable this feature on the database.

Updatable ledger tables allow to capture the data of updates and deletes. The updated rows will be stored in the history tables. The database digests using the block chain technology is saved in the trusted Azure storage where database digests are used to verify the database whether the data in it is being tampered.

Because the Ledger captures all the changes happening to the data in the database and if the tampering happens through application, these changes can be also be identified through the ledger views. This is one of the great features in Azure in protecting the data.

This blog post covered multiple data protection technologies available in Azure. In upcoming blog post, we will learn about the next security layer Threat prevention and detection.

Thanks for reading!


Azure SQL Security: Data Protection

One thought on “Quick Track: Beginner’s Guide to Azure SQL- Data Protection

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s