We are now part of Eviden, discover more...

This blog is the continuation of the Azure SQL Security series. In the previous blog, we went through the security requirement scenario and discussed Dynamic Data Masking. In this blog, we will focus on the Transparent Data  Encryption (TDE) feature. 

Business use case 

Consider a scenario where you need to protect entire data at rest, from malicious offline access to raw files or backups, avoid data theft from lost hardware like backup, disk drives, etc. Transparent Data Encryption plays a crucial role in these scenarios of protecting your data at rest. It protects entire data in Azure SQL Database/Data Warehouse /Azure Synapse Analytics. 

Transparent Data Encryption (encryption at rest) 

TDE is used to encrypt data in Azure SQL Database, Azure SQL Data Warehouse/Azure Synapse Analytics, and Azure SQL Managed Instance for protecting the sensitivity of  data at rest against malicious offline access. It is enabled by default for all newly created Azure SQL Databases. Note that you must configure TDE manually for older Azure SQL Databases (created before May 2017), and Azure SQL Managed Instance (created before February 2019).   

Go to the Microsoft Azure portal, select SQL databases, and select your respective database on the SQL databases page. Under the Security section, select Transparent Data Encryption, and enable/disable based on the requirement, and save. 

Azure SQL Security - Transparent Data Encryption
1. Transparent data encryption in Azure SQL Database

Steps given above can be followed in Azure SQL Data Warehouse/ Azure Synapse Analytics, by navigating to the Security section and, selecting Transparent data encryption. Enable, or disable it as required. By default, it is disabled.

Transparent Data Encryption comprises real-time page by page I/O encryption and decryption mechanisms. TDE decrypts data when processed into memory. It uses Advanced Encryption Standard (AES) and Triple Data Encryption Standard (3DES) encryption algorithms. It will require a database encryption key for encryption and decryption. TDE protector protects the database encryption key. It is of two types: 

At the server or instance level, you can change the TDE protector type. Go to respective Azure SQL Server/Instance, and under the Security section, select Transparent data encryption

Azure SQL Security - Transparent Data Encryption
2.Transparent data encryption in Azure SQL Server

The service-managed key provides a unique key certificate to each of your database. Azure service manages the certificate maintenance and the key rotation process. 

Azure SQL Security - Transparent Data Encryption
3.Transparent data encryption- Azure Key Vault

The customer-managed key allows you to manage your TDE protector, by providing a Key vault or a Key identifier. But it should be available when data is in transit for decryption, or else your data might get lost as it may be corrupted. If you use Azure Key Vault, you need get, list, wrapKey, unwrapKey permissions for access, and use in TDE. 

Azure SQL Security - Transparent Data Encryption
4.Azure Key Vault selection

Choose details of Azure Key Vault to use as a TDE protector.

Azure SQL Security - Transparent Data Encryption
5. Key identifier input

If you have key identifier generated, mention it in the key identifier field. 

You can also achieve this certificate as TDE protector type using SQL Server Management Studio:

  1. Switch to the master database and execute the following SQL query statements.
Azure SQL Security - Transparent Data Encryption
6. SQL Query – Switch to master database

2. Go to the respective database and execute the following SQL query statements. 

Azure SQL Security - Transparent Data Encryption
7. TDE protector – certificate type using SQL statements

Note: You must be an administrator or assigned to DB manager role to execute the above SQL statements. You cannot use Transact-SQL to achieve customer-managed key type through Key Vault. In this case, go for other methods.

You may also use PowerShell or REST APIs for implementing TDE like Transact-SQL or Azure portal process shown above. You can check the status of TDE by accessing the sys.dm_database_encryption_keys dynamic management view using the below query. 

Azure SQL Security - Transparent Data Encryption
8.sys.dm_database_encryption_keys – query results

This dynamic management view provides info about database encryption keys, the algorithm usedencryptor_type, and the state of encryptionEncryption_state column value ‘three denotes Encrypted state. 

Enabling TDE helps you protect data at rest and it also provides compliance to privacy laws and, regulations, data security standards, and guidelines across multiple industries, which may require encryption of data at rest. 

We will focus on the Always Encrypted security feature in our next blog. Learn more about Visual BI’s Microsoft Azure offerings here

Corporate HQ:
5920 Windhaven Pkwy, Plano, TX 75093

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192


Copyright © Visual BI Solutions Inc.

Subscribe To Our Newsletter

Subscribe To Our Newsletter

Join our mailing list to receive the latest news and updates from our team.

You have Successfully Subscribed!

Share This!

Share this with your friends and colleagues!