Implement Transparent Data Encryption (TDE)
In this we will learn about Transparent data encryption (TDE). TDE helps protect Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics against the threat of malicious offline activity by encrypting data at rest. Moreover, it performs real-time encryption and decryption of the database, associated backups, and transaction log files at rest without requiring changes to the application. By default, TDE is enabled for all newly deployed SQL Databases and must be manually enabled for older databases of Azure SQL Database, Azure SQL Managed Instance.
- TDE performs real-time I/O encryption and decryption of the data at the page level. Each page is decrypted when it’s read into memory and then encrypted before being written to disk.
- Further, it also encrypts the storage of an entire database by using a symmetric key called the Database Encryption Key (DEK). On database startup, the encrypted DEK is decrypted and then used for decryption and re-encryption of the database files in the SQL Server database engine process.
- However, DEK is protected by the TDE protector. TDE protector is either a service-managed certificate (service-managed transparent data encryption) or an asymmetric key stored in Azure Key Vault (customer-managed transparent data encryption).
- Lastly, for Azure SQL Database and Azure Synapse, the TDE protector is set at the server level and is inherited by all databases associated with that server. For Azure SQL Managed Instance, the TDE protector is set at the instance level and it is inherited by all encrypted databases on that instance.
Service-managed transparent data encryption
In Azure, the default setting for TDE is that the DEK is protected by a built-in server certificate. The built-in server certificate is unique for each server and the encryption algorithm used is AES 256. However, if a database is in a geo-replication relationship, both the primary and geo-secondary databases are protected by the primary database’s parent server key. Secondly, if two databases are connected to the same server, they also share the same built-in certificate. In this, Microsoft automatically rotates these certificates in compliance with the internal security policy and the root key is protected by a Microsoft internal secret store.
Customer-managed transparent data encryption – Bring Your Own Key
Customer-managed TDE is also referred to as Bring Your Own Key (BYOK) support for TDE. Here, the TDE Protector that encrypts the DEK is a customer-managed asymmetric key. This is stored in a customer-owned and managed Azure Key Vault and never leaves the key vault. However, the TDE Protector can be generated by the key vault or transferred to the key vault from an on-premises hardware security module (HSM) device. SQL Database, SQL Managed Instance, and Azure Synapse need to be granted permissions to the customer-owned key vault to decrypt and encrypt the DEK. And, if permissions of the server to the key vault are revoked. Then, a database will be inaccessible, and all data is encrypted
Move a transparent data encryption-protected database
You don’t need to decrypt databases for operations within Azure. The TDE settings on the source database or primary database are transparently inherited on the target. Operations that are included involve:
- Firstly, Geo-restore
- Secondly, Self-service point-in-time restore
- Thirdly, Restoration of a deleted database
- Then, Active geo-replication
- Next, Creation of a database copy
- Lastly, Restore of backup file to Azure SQL Managed Instance
However, when you export a TDE-protected database, the exported content of the database isn’t encrypted. This exported content is stored in unencrypted BACPAC files. Be sure to protect the BACPAC files appropriately and enable TDE after import of the new database is finished.
Manage transparent data encryption
The Azure portal
- Firstly, manage TDE in the Azure portal.
- Secondly, to configure TDE through the Azure portal, you must be connected as the Azure Owner. Or it can be Contributor, or SQL Security Manager.
- Then, enable and disable TDE on the database level. For Azure SQL Managed Instance use Transact-SQL (T-SQL) to turn TDE on and off on a database. For Azure SQL Database and Azure Synapse, you can manage TDE for the database in the Azure portal. This is after you’ve signed in with the Azure Administrator or Contributor account. Find the TDE settings under your user database. By default, service-managed transparent data encryption is used. A TDE certificate is automatically generated for the server that contains the database.
Reference: Microsoft Documentation