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.
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:
- Service-managed key- TDE protector can be a built-in certificate, and Azure services manage it entirely.
- Customer-managed key- An asymmetric key stored in an Extensible Key Management module (EKM) or Azure Key Vault by the customers
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.
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.
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.
Choose details of Azure Key Vault to use as a TDE protector.
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:
- Switch to the master database and execute the following SQL query statements.
2. Go to the respective database and execute the following SQL query 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.
This dynamic management view provides info about database encryption keys, the algorithm used, encryptor_type, and the state of encryption. Encryption_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.
