Transparent Data Encryption for SQL Database
In this tutorial, we will learn how Transparent data encryption (TDE) protects the Azure SQL Database and Azure SQL Managed Instance.
TDE, on the other hand, encrypts and decrypts the database, backups, and transaction log files in the background without needing modifications to the program. TDE is enabled by default for all newly created SQL Databases, however, it must be activated explicitly for older Azure SQL Database and Azure SQL Managed Instance databases. Further:
- TDE has the capacity to execute data encryption and decryption at the page level in real time. When reading into memory, however, each page decrypts.
- Secondly, TDE encrypts a complete database’s storage with a symmetric key called the Database Encryption Key (DEK). The DEK encryptions get decrypt when the database begins. This is also important for the SQL Server database engine process’s decryption and re-encryption of database files.
- TDE protector safeguards a DEK in Azure Key Vault that is either a service-managed certificate or an asymmetric key.
- Lastly, the TDE protector is also configured at the server level for Azure SQL Database and Azure Synapse, and it is inherited by all databases linked with that server. The TDE protector is set at the instance level for Azure SQL Manageable Instance, and all encrypted databases on that instance inherit it.
Service-managed transparent data encryption
In Azure, the default setting for TDE is that the DEK is safe and secure by a built-in server certificate. However, the built-in server certificate is unique for each server and the encryption algorithm is AES 256. And, if a database is in a geo-replication relationship, both the primary and geo-secondary databases gets protection by the primary database’s parent server key. Moreover, if two databases connect to the same server, then they also share the same built-in certificate.
Customer-manageable transparent data encryption
Customer-manageable TDE refers to Bring Your Own Key (BYOK) support for TDE. The TDE Protector that encrypts the DEK in this case is a customer-controlled asymmetric key that gets stored in a customer-owned and managed Azure Key Vault and never leaves it. Moreover, the TDE Protector can generate through the key vault or transferred to the key vault from an on-premises hardware security module (HSM) device. And, if permissions of the server to the key vault revokes, a database will be inaccessible, and all data gets encrypts.
With TDE with Azure Key Vault integration, users can control key management tasks as well as key rotations, key vault permissions, key backups. However, key vVault provides central key management, leverages tightly monitored HSMs, and enables separation of duties between management of keys and data to help meet compliance with security policies.
Moving a transparent data encryption-protected database
There is no need to decrypt databases for operations within Azure. However, the TDE settings on the source database or primary database transparently inherit on the target. Operations in this are:
- Geo-restore
- Self-service point-in-time restore
- Restoration of a deleted database
- Active geo-replication
- Creation of a database copy
- Restoring backup file to Azure SQL Managed Instance
However, when you export a TDE-protect database, then the content of the database does not encrypt.
Managing transparent data encryption
The Azure portal
Managing TDE in the Azure portal.
- Firstly, for configuring TDE through the Azure portal, you must connect as the Azure Owner, Contributor, or SQL Security Manager.
- Enabling and disabling TDE on the database level. For Azure SQL Managed Instance use Transact-SQL (T-SQL) for turning TDE on and off on a database. Find the TDE settings under your user database. By default, it uses ervice-manageable transparent data encryption.
- Then, set the TDE master key, known as the TDE protector, at the server or instance level. For using TDE with BYOK support and protect your databases with a key from Key Vault.
Reference: Microsoft Documentation