What is Transparent Data Encryption (TDE) and why does it matter?
TDE is a data encryption mechanism with SQL Server that protects data at rest. It protects the data in the database, Data and Log Files (MDF, NDF & LDF). What does at rest mean? This is where the data is not in transit and is stored, for example, on a device or storage layer such as SAN. TDE protects data from unauthorised access by 3rd parties that are not allowed to access it. This encryption type is used for:
- Protecting data from physical theft, for example, if a hard drive were stolen, the user couldn’t use the data stored on it without the required encryption key.
- Regulatory Compliance – GDPR is an example of UK regulation that specifies restrictions on the protection of data.
- Integrates without the need for application changes.
What TDE does not cover?
- Data in Transit – This is where other technologies, such as Always Encrypted, come into play for example, where data moves between data sources like the Database and the Application.
- Access Control – This will not stop a user from seeing specific data via an application, as this is still done via Roles and User Rights.
TDE has a minimal performance impact, Microsoft states between 3-5% and this is mainly on CPU, but the impact can be less if the data accessed is in memory. TDE uses symmetric key encryption, which is very efficient.
Within Azure (PAAS & MI) Azure enables TDE by default.
SMK & CMK. What’s the difference?
SMKs are Service Managed keys, and Microsoft manages these keys within Azure. If TDE is turned on (and it is by default within Azure PAAS offerings), then your data at rest is protected by this Key by default, with no extra input required from the customer.
CMKs are Customer Managed Keys. Depending on which platform you are in, these can be known by different names and are often called BYOK (Bring Your Own Key). This is where, as the name suggests, the customer provides and looks after their own key. Within Azure, this process can be managed by using the Azure Key Vault.
The table below highlights the main differences between the two options.
SMKs vs CMKs in Azure
| Feature | Service-Managed Key | Customer-Managed Key |
| Ownership | Microsoft | Customer |
| Storage | Azure-managed | Azure Key Vault |
| Rotation | Automatic | Manual or automated via Key Vault |
| Control | Limited | Full (enable/disable access, revoke, audit, etc.) |
When choosing to use a CMK, you have ownership of those keys, and there is a higher level of control. However, this does come with the extra management overhead. Which in effect places the onus on you and not Microsoft to make sure the keys are maintained.
Why does it matter if I have control of the keys, I hear you ask? Well, for some and indeed most, it is not important. However, it does give you extra choice where required, for instance, you can choose when to rotate the keys for extra protection, to meet certain regulatory requirements for your data.
Major Considerations when Choosing CMKs
As part of the setup process when choosing CMKs, you will also need to create or use an existing Key Vault (which will store your key information), it is important to make sure ‘Purge Protection’ (as per the image below) is enabled at the vaults creation, as this will then keep copies of the keys for 90 days after deletion. If the keys are purged accidentally without this extra layer of protection, then you or even Microsoft would not be able to access your data if there were a problem with your keys.
You will need to set this when creating the Vault, so if is not set as required, then it would be advisable to create a new Key Vault with the Purge protection.
Another reason why CMKs are the only choice.
If your encrypted databases need to be restored to an on-premises environment, then your only option is to use CMKs, as SMKs are managed within Azure by Microsoft and cannot support restoration to an on-premises environment. This is also the case for restores between different Managed Instances (MIs).
Summary
The main reason for choosing CMKs is the requirement for ultimate governance and sovereignty over your data’s encryption keys. Whilst SMK provides strong, default encryption, it offers minimal customer control. CMKs are the preferred option when an organisation’s security posture, internal policies, or regulatory frameworks require complete command over the key lifecycle and access to sensitive data.
There is no right or wrong with either choice. However, it is good to have that choice, and the use of CMKs can offer that alternative, though, as specified, it does have that extra management overhead.