This chapter covers Transparent Data Encryption (TDE) in Azure SQL, a critical security feature that encrypts data at rest without requiring application changes. For the DP-900 exam, TDE is a key topic under objective 2.1 (Describe relational data workloads on Azure) and appears in roughly 5-10% of questions, often in the context of security features for Azure SQL Database and Azure SQL Managed Instance. Understanding TDE's mechanism, key management options, and how it differs from other encryption methods is essential for the exam.
Jump to a section
Imagine a bank vault that stores cash in transparent plastic boxes. The vault door is locked, but anyone looking through the glass can see the money. TDE is like replacing those plastic boxes with opaque steel safes, each with its own combination lock. The bank teller (database server) knows the combination and can open the safe instantly when a customer (application) requests cash. However, the combination is not written on the safe — it is stored in a separate master key safe (Azure Key Vault) that only the bank manager (database service) can access. When the teller needs to open a safe, they call the manager, who retrieves the combination, unlocks the safe, and hands the cash to the teller. The customer never sees the combination or the safe-opening process; they just get their cash. If a thief steals a safe (backup file or data file), they cannot open it because they do not have the combination and cannot call the manager. The transparent part means the teller and customer experience no change — they do not even know the safes exist. The encryption and decryption happen automatically at the page level as data is written to or read from disk. This is exactly how TDE works: the database engine encrypts data before writing to disk and decrypts when reading into memory, all without application changes.
What is Transparent Data Encryption (TDE)?
Transparent Data Encryption (TDE) is a Microsoft SQL Server and Azure SQL feature that performs real-time I/O encryption and decryption of data files, log files, and backup files. The encryption is 'transparent' because it operates at the page level — when a page is written to disk, it is encrypted before the write operation completes; when read from disk, it is decrypted before being placed into the buffer pool. This means applications and users do not need to be modified to benefit from encryption.
TDE is primarily designed to protect data at rest — that is, the physical files on disk. It does not protect data in transit (network traffic) or data in use (in memory). The exam focuses on TDE as a built-in security control for Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics dedicated SQL pools.
How TDE Works Internally
TDE uses a two-tier key architecture: - Database Encryption Key (DEK): A symmetric key stored in the database boot record and used to encrypt the entire database. The DEK is itself encrypted by a protector. - Protector: An asymmetric key or certificate that encrypts the DEK. In Azure SQL, the protector can be either a service-managed certificate (service-managed TDE) or a customer-managed key stored in Azure Key Vault (customer-managed TDE).
When a page is written to disk: 1. The buffer pool manager flushes a dirty page to disk. 2. Before the write, the database engine uses the DEK to encrypt the page. 3. The encrypted page is written to the data file (.mdf or .ndf). 4. The transaction log entries for that write are also encrypted before being written to the log file (.ldf).
When a page is read from disk: 1. A read request is issued for a specific page. 2. The encrypted page is read from the data file. 3. The database engine uses the DEK to decrypt the page. 4. The decrypted page is placed into the buffer pool for use.
This process happens for every page I/O, including those involved in backups. Backup files are encrypted because the pages are encrypted as they are written to the backup media.
Key Components, Values, and Defaults
DEK Algorithm: The default encryption algorithm is AES-256. Other supported algorithms include AES-128 and Triple DES (3DES), but AES-256 is the recommended and most commonly used.
Protector: In service-managed TDE, Azure automatically rotates the certificate every 90 days. In customer-managed TDE, the key is stored in Azure Key Vault and must be rotated manually or via automation.
Master Key: The master key of the database (a symmetric key) is used to protect the private keys of certificates and asymmetric keys. This is a separate concept from the DEK.
Performance Impact: TDE typically incurs a 3-5% CPU overhead due to encryption/decryption operations. The impact is minimal because encryption occurs at the page level and uses hardware-accelerated AES instructions (AES-NI) on modern processors.
Compression: TDE is applied before backup compression. Encrypted backups can still be compressed, but compression ratios may be lower because encrypted data is less compressible.
Configuration and Verification Commands
For Azure SQL Database and Azure SQL Managed Instance, TDE is enabled at the database level. The default setting for new databases is service-managed TDE enabled. You can check TDE status using T-SQL:
SELECT
db.name AS DatabaseName,
dek.encryption_state,
dek.encryption_state_desc,
dek.key_algorithm,
dek.key_length
FROM sys.dm_database_encryption_keys dek
JOIN sys.databases db ON dek.database_id = db.database_id;encryption_state values:
- 0 = No encryption key present, encryption not available
- 1 = Unencrypted
- 2 = Encryption in progress
- 3 = Encrypted
- 4 = Key change in progress
- 5 = Decryption in progress
To enable TDE on a user database in Azure SQL Database:
ALTER DATABASE [YourDatabase] SET ENCRYPTION ON;For customer-managed TDE, you must first configure Azure Key Vault and assign a key. This is done via the Azure portal, PowerShell, or Azure CLI. The key must have the wrapKey, unwrapKey, and get permissions for the SQL server's Azure AD identity.
Interaction with Related Technologies
Always Encrypted: TDE encrypts data at rest, while Always Encrypted encrypts data in use and in transit at the column level. They can be used together. TDE protects the entire database file, while Always Encrypted protects specific columns from the database administrator.
Dynamic Data Masking: TDE does not mask data; it encrypts the physical files. Dynamic Data Masking obfuscates data at query time for unauthorized users.
Azure Key Vault: Used as the key store for customer-managed TDE. The SQL server must have a system-assigned or user-assigned managed identity to access the key vault.
Backup and Restore: Backups of TDE-enabled databases are encrypted. Restoring a backup to a different server requires access to the same DEK protector (certificate or key). In Azure, this is handled automatically for service-managed TDE; for customer-managed TDE, the key must be accessible in the target region.
Security Considerations
Service-Managed TDE: Azure manages the protector key and rotates it automatically. This is the simplest option and meets many compliance requirements. However, the customer has no control over the key material.
Customer-Managed TDE (CMK): Also known as Bring Your Own Key (BYOK). The customer controls the key in Azure Key Vault, can rotate it, revoke access, and audit usage. This is required for certain compliance standards (e.g., PCI DSS, HIPAA).
Key Revocation: If the customer revokes access to the key vault, the database will become inaccessible within a few minutes. The database goes into a 'restricted access' state, and queries fail. Recovery requires restoring key access.
Geo-Replication: For geo-replicated databases, TDE must be enabled on both primary and secondary databases. In customer-managed TDE, the same key (or a key from the same key vault) must be available in the secondary region.
Enable TDE on Azure SQL Database
In the Azure portal, navigate to your SQL server, then under 'Security', select 'Transparent data encryption'. For each database, you can toggle TDE on or off. By default, new databases have service-managed TDE enabled. To use customer-managed keys, you must first configure Azure Key Vault and assign a key to the server. The portal shows the encryption status and allows you to switch between service-managed and customer-managed modes.
Verify TDE Status Using T-SQL
Connect to the database using SQL Server Management Studio (SSMS) or Azure Data Studio. Run the query: SELECT * FROM sys.dm_database_encryption_keys. This returns the encryption state, algorithm, and key length. For a TDE-enabled database, encryption_state should be 3. You can also check the database properties in the portal under 'Transparent data encryption'.
Configure Customer-Managed Key in Key Vault
Create an Azure Key Vault (if not existing) and generate or import a key. The key must be an RSA key of size 2048, 3072, or 4096. Assign the SQL server a system-assigned managed identity. Grant that identity the get, wrapKey, and unwrapKey permissions on the key vault. Then, in the Azure portal under the SQL server's 'Transparent data encryption' blade, select 'Customer-managed key' and choose the key.
Rotate the Customer-Managed Key
Key rotation is done by creating a new key version in Azure Key Vault (manual or automatic). The SQL server automatically uses the latest version of the key. To force a re-encryption of the DEK with the new key, you can run: ALTER DATABASE [YourDatabase] SET ENCRYPTION ON; (if already on, it re-encrypts). This operation is online but may cause a brief performance impact.
Restore TDE-Encrypted Database to Another Server
For service-managed TDE, backups are automatically encrypted and can be restored to any Azure SQL server. For customer-managed TDE, the target server must have access to the same key vault and key. If restoring to a different region, the key vault must be available in that region (use geo-replication or export the key). The restore operation will fail if the key is not accessible.
In a financial services company handling credit card transactions, TDE is mandatory for PCI DSS compliance. The company uses Azure SQL Database with customer-managed TDE to maintain control over encryption keys. They store the key in a dedicated Azure Key Vault with soft-delete and purge protection enabled. The key is rotated every 90 days using Azure Key Vault's automatic rotation feature. The operations team monitors TDE status via Azure Monitor alerts that trigger if the database encryption state changes or if key vault access fails. A common issue occurs when the managed identity is accidentally removed from the key vault access policy, causing the database to become inaccessible. The team uses a runbook to automatically re-grant permissions if an alert fires.
A healthcare organization uses Azure SQL Managed Instance with service-managed TDE for HIPAA compliance. They chose service-managed because they lacked the operational maturity to manage keys. However, they discovered that service-managed TDE does not allow them to audit key usage, which is required for their compliance framework. They later migrated to customer-managed TDE using a key from their on-premises HSM via Azure Key Vault. The migration required a planned downtime window because changing from service-managed to customer-managed TDE triggers a re-encryption of the DEK, which is an online operation but can cause a temporary performance degradation.
An e-commerce platform runs Azure Synapse Analytics dedicated SQL pool with TDE enabled. They use customer-managed TDE to meet their internal security policy. During a disaster recovery drill, they attempted to restore a backup to a secondary region but failed because the key vault in the primary region was not geo-replicated. They resolved this by configuring key vault geo-replication and ensuring the target server's managed identity had access to the key vault in the secondary region. They now test restore operations quarterly to validate key accessibility.
DP-900 tests your understanding of TDE as a data-at-rest encryption feature. Key objective: 2.1 (Describe relational data workloads on Azure). The exam will ask you to identify which security feature encrypts data files without application changes. The most common wrong answer is 'Always Encrypted' because it also encrypts data, but it requires application changes and operates at the column level. Another trap is 'Dynamic Data Masking' — candidates confuse masking with encryption. Remember: TDE encrypts the entire database file; Dynamic Data Masking obfuscates data at query time.
The exam expects you to know:
TDE is enabled by default for new Azure SQL databases (service-managed).
TDE uses AES-256 encryption.
TDE does NOT protect data in transit — that is TLS/SSL.
TDE does NOT protect data in memory — that is not covered by TDE.
Customer-managed TDE requires Azure Key Vault.
TDE is available for Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics dedicated SQL pools.
Common exam scenario: You are asked to recommend a solution to encrypt data at rest with minimal application changes. The correct answer is TDE. Wrong answers include: Always Encrypted (requires app changes), Dynamic Data Masking (does not encrypt), and Azure Storage Service Encryption (encrypts storage but not SQL files).
Edge cases: If a candidate sees 'SQL Server on Azure VM', TDE is still available but must be manually configured with certificates. The exam may ask about TDE for IaaS vs PaaS. For PaaS, TDE is a built-in feature; for IaaS, you manage it yourself.
To eliminate wrong answers: Focus on the scope of encryption. TDE = entire database at rest. Always Encrypted = specific columns with client-side encryption. Dynamic Data Masking = obfuscation, not encryption. Azure Storage Service Encryption = encrypts blob storage, not SQL files.
TDE encrypts data at rest (data and log files) using AES-256 by default.
TDE is transparent — no application changes required.
Azure SQL Database has service-managed TDE enabled by default for new databases.
Customer-managed TDE requires Azure Key Vault and a managed identity.
TDE does NOT encrypt data in transit or in memory.
Each database has its own Database Encryption Key (DEK).
TDE is available for Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics dedicated SQL pools.
The encryption state can be checked using sys.dm_database_encryption_keys.
Switching between service-managed and customer-managed TDE triggers re-encryption of the DEK.
Backups of TDE-enabled databases are automatically encrypted.
These come up on the exam all the time. Here's how to tell them apart.
Service-Managed TDE
Azure automatically manages the protector key.
Key rotation happens every 90 days automatically.
No additional cost for key management.
Simplest to configure — enabled by default.
Limited auditing capability for key usage.
Customer-Managed TDE
Customer controls the key in Azure Key Vault.
Customer is responsible for key rotation (manual or automated).
May incur costs for Key Vault operations and key storage.
Requires additional configuration of managed identity and key vault permissions.
Full audit trail of key operations via Key Vault logs.
Mistake
TDE encrypts data in transit between the application and the database.
Correct
TDE only encrypts data at rest (database files on disk). Data in transit is protected by TLS/SSL, which is a separate feature. TDE does not affect network traffic.
Mistake
TDE requires application changes to encrypt data.
Correct
TDE is transparent to applications. No code changes are needed because encryption and decryption happen at the page I/O level within the database engine.
Mistake
Service-managed TDE and customer-managed TDE are mutually exclusive — you cannot switch between them.
Correct
You can switch from service-managed to customer-managed TDE or vice versa. Switching triggers a re-encryption of the DEK with the new protector, but the database remains online.
Mistake
TDE encrypts only the data files (.mdf) and not the transaction log (.ldf).
Correct
TDE encrypts both data files and transaction log files. When a log record is written, it is encrypted before being written to the log file.
Mistake
TDE uses a single key for all databases on a server.
Correct
Each database has its own Database Encryption Key (DEK). The DEK is unique per database. The protector (certificate or key) can be shared across databases on the same server.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
Yes, backups of a TDE-enabled database are encrypted because the pages are encrypted as they are written to the backup media. When you restore a backup, the database engine decrypts the pages using the DEK. If the backup is restored to a different server, that server must have access to the same DEK protector (certificate or key). In Azure SQL, service-managed TDE backups can be restored to any server in the same region; for customer-managed TDE, the key must be accessible in the target region.
Yes, you can use both features together. TDE encrypts the entire database at rest, while Always Encrypts encrypts specific columns with client-side keys. They serve different purposes: TDE protects against physical theft of files, while Always Encrypted protects sensitive data from database administrators. They are complementary and can be enabled on the same database.
If the key is revoked (e.g., by deleting the key or removing access permissions), the SQL database will become inaccessible within a few minutes. The database enters a 'restricted access' state where queries fail. To recover, you must restore access to the key by re-granting permissions or restoring the key from a backup. Data is not lost, but the database is effectively offline until the key is available.
TDE typically adds 3-5% CPU overhead due to encryption and decryption operations. The impact is minimal because modern processors have AES-NI hardware acceleration. The overhead is most noticeable on write-heavy workloads. In practice, many production systems run with TDE enabled without significant performance degradation. The exam expects you to know that TDE has a small performance impact.
Yes, TDE is available for all deployment options of Azure SQL Database: single databases, elastic pools, and managed instances. It is also available for Azure Synapse Analytics dedicated SQL pools. For Azure SQL Database, TDE is enabled by default on new databases. For Azure SQL Managed Instance, TDE is also enabled by default. You can disable it if needed, but it is recommended to keep it enabled for security.
You can check TDE status using the Azure portal: navigate to the SQL server, select 'Transparent data encryption' under Security, and view the status for each database. Alternatively, use T-SQL: SELECT * FROM sys.dm_database_encryption_keys. The encryption_state column indicates the status: 3 means encrypted. You can also query sys.databases for the is_encrypted column.
TDE encrypts SQL Server data files at the database engine level. Azure Storage Service Encryption (SSE) encrypts the underlying storage (blobs, disks) at the storage layer. Both protect data at rest, but TDE is specific to SQL databases and provides an additional layer of encryption. For Azure SQL Database, storage is already encrypted by SSE by default, so TDE adds a second layer. The exam may ask which encryption is used for SQL data at rest — the answer is TDE.
You've just covered Transparent Data Encryption (TDE) in Azure SQL — now see how well it sticks with free DP-900 practice questions. Full explanations included, no account needed.
Done with this chapter?