Transparent Data Encryption (TDE)

APPLIES TO: YesSQL Server YesAzure SQL Database YesAzure Synapse Analytics (SQL Data Warehouse) YesParallel Data Warehouse

Transparent Data Encryption (TDE) encrypts SQL Server, Azure SQL Database, and Azure Synapse Analytics (SQL Data Warehouse) data files. This encryption is known as encrypting data at rest.

To help secure a database, you can take precautions like:

  • Designing a secure system.
  • Encrypting confidential assets.
  • Building a firewall around the database servers.

But a malicious party who steals physical media like drives or backup tapes can restore or attach the database and browse its data.

One solution is to encrypt sensitive data in a database and use a certificate to protect the keys that encrypt the data. This solution prevents anyone without the keys from using the data. But you must plan this kind of protection in advance.

TDE does real-time I/O encryption and decryption of data and log files. The encryption uses a database encryption key (DEK). The database boot record stores the key for availability during recovery. The DEK is a symmetric key. It's secured by a certificate that the server's master database stores or by an asymmetric key that an EKM module protects.

TDE protects data at rest, which is the data and log files. It lets you follow many laws, regulations, and guidelines established in various industries. This ability lets software developers encrypt data by using AES and 3DES encryption algorithms without changing existing applications.

About TDE

Encryption of a database file is done at the page level. The pages in an encrypted database are encrypted before they're written to disk and are decrypted when read into memory. TDE doesn't increase the size of the encrypted database.

Information applicable to SQL Database

When you use TDE with SQL Database V12, SQL Database automatically creates for you the server-level certificate stored in the master database. To move a TDE database on SQL Database, you don't have to decrypt the database for the move operation. For more information on using TDE with SQL Database, see Transparent Data Encryption with Azure SQL Database.

Information applicable to SQL Server

After you secure a database, you can restore it by using the correct certificate. For more information about certificates, see SQL Server Certificates and Asymmetric Keys.

After you enable TDE, immediately back up the certificate and its associated private key. If the certificate becomes unavailable, or if you restore or attach the database on another server, you need backups of the certificate and private key. Otherwise, you can't open the database.

Keep the encrypting certificate even if you've disabled TDE on the database. Although the database isn't encrypted, parts of the transaction log might remain protected. You also might need the certificate for some operations until you do a full database backup.

You can still use a certificate that exceeds its expiration date to encrypt and decrypt data with TDE.

Encryption hierarchy

The following illustration shows the architecture of TDE encryption. Only the database-level items (the database encryption key and ALTER DATABASE portions) are user-configurable when you use TDE on SQL Database.

The Transparent Database Encryption architecture

Using Transparent Data Encryption

To use TDE, follow these steps.

Applies to: SQL Server.

  1. Create a master key.

  2. Create or obtain a certificate protected by the master key.

  3. Create a database encryption key and protect it by using the certificate.

  4. Set the database to use encryption.

The following example shows encryption and decryption of the AdventureWorks2012 database using a certificate named MyServerCert that's installed on the server.

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>';
go
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
go
USE AdventureWorks2012;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
ALTER DATABASE AdventureWorks2012
SET ENCRYPTION ON;
GO

The encryption and decryption operations are scheduled on background threads by SQL Server. To view the status of these operations, use the catalog views and dynamic management views in the table that appears later in this article.

Caution

Backup database files that have TDE enabled are also encrypted with the database encryption key. As a result, when you restore these backups, the certificate that protects the database encryption key must be available. Therefore, in addition to backing up the database, make sure to maintain backups of the server certificates. Data loss results if the certificates are no longer available.

For more information, see SQL Server Certificates and Asymmetric Keys.

Commands and functions

For the following statements to accept TDE certificates, use a database master key to encrypt them. If you encrypt them by password only, the statements reject them as encryptors.

Important

If you make the certificates password protected after TDE uses them, the database becomes inaccessible after a restart.

The following table provides links and explanations of TDE commands and functions:

Command or function Purpose
CREATE DATABASE ENCRYPTION KEY (Transact-SQL) Creates a key that encrypts a database
ALTER DATABASE ENCRYPTION KEY (Transact-SQL) Changes the key that encrypts a database
DROP DATABASE ENCRYPTION KEY (Transact-SQL) Removes the key that encrypts a database
ALTER DATABASE SET Options (Transact-SQL) Explains the ALTER DATABASE option that is used to enable TDE

Catalog views and dynamic management views

The following table shows TDE catalog views and dynamic management views.

Catalog view or dynamic management view Purpose
sys.databases (Transact-SQL) Catalog view that displays database information
sys.certificates (Transact-SQL) Catalog view that shows the certificates in a database
sys.dm_database_encryption_keys (Transact-SQL) Dynamic management view that provides information about a database's encryption keys and state of encryption

Permissions

Each TDE feature and command has individual permission requirements as described in the tables shown earlier.

Viewing the metadata involved with TDE requires the VIEW DEFINITION permission on a certificate.

Considerations

While a re-encryption scan for a database encryption operation is in progress, maintenance operations to the database are disabled. You can use the single-user mode setting for the database to do maintenance operations. For more information, see Set a Database to Single-user Mode.

Use the sys.dm_database_encryption_keys dynamic management view to find the state of database encryption. For more information, see the "Catalog views and dynamic management views" section earlier in this article.

In TDE, all files and filegroups in a database are encrypted. If any filegroup in a database is marked READ ONLY, the database encryption operation fails.

If you use a database in database mirroring or log shipping, both databases are encrypted. The log transactions are encrypted when sent between them.

Important

Full-text indexes are encrypted when a database is set for encryption. Such indexes created in a SQL Server version earlier than SQL Server 2008 are imported into the database by SQL Server 2008 or later and are encrypted by TDE.

Tip

To monitor changes in the TDE status of a database, use SQL Server Audit or SQL Database auditing. For SQL Server, TDE is tracked under the audit action group DATABASE_CHANGE_GROUP, which you can find in SQL Server Audit Action Groups and Actions.

Restrictions

The following operations are disallowed during initial database encryption, key change, or database decryption:

  • Dropping a file from a filegroup in a database

  • Dropping a database

  • Taking a database offline

  • Detaching a database

  • Transitioning a database or filegroup into a READ ONLY state

The following operations are disallowed during the CREATE DATABASE ENCRYPTION KEY, ALTER DATABASE ENCRYPTION KEY, DROP DATABASE ENCRYPTION KEY, and ALTER DATABASE...SET ENCRYPTION statements:

  • Dropping a file from a filegroup in a database

  • Dropping a database

  • Taking a database offline

  • Detaching a database

  • Transitioning a database or filegroup into a READ ONLY state

  • Using an ALTER DATABASE command

  • Starting a database or database-file backup

  • Starting a database or database-file restore

  • Creating a snapshot

The following operations or conditions prevent the CREATE DATABASE ENCRYPTION KEY, ALTER DATABASE ENCRYPTION KEY, DROP DATABASE ENCRYPTION KEY, and ALTER DATABASE...SET ENCRYPTION statements:

  • A database is read-only or has read-only filegroups.

  • An ALTER DATABASE command is running.

  • A data backup is running.

  • A database is in an offline or restore condition.

  • A snapshot is in progress.

  • Database maintenance tasks are running.

When database files are created, instant file initialization is unavailable when TDE is enabled.

To encrypt a database encryption key with an asymmetric key, the asymmetric key must be on an extensible key-management provider.

Transparent Data Encryption and transaction logs

Letting a database use TDE removes the remaining part of the current virtual transaction log. The removal forces creation of the next transaction log. This behavior guarantees that no clear text is left in the logs after the database is set for encryption.

To find the status of log-file encryption, see the encryption_state column in the sys.dm_database_encryption_keys view, as in this example:

USE AdventureWorks2012;
GO
/* The value 3 represents an encrypted state
   on the database and transaction logs. */
SELECT *
FROM sys.dm_database_encryption_keys
WHERE encryption_state = 3;
GO

For more information about the SQL Server log-file architecture, see The Transaction Log (SQL Server).

Before a database encryption key changes, the previous database encryption key encrypts all data written to the transaction log.

If you change a database encryption key twice, you must do a log backup before you can change the database encryption key again.

Transparent Data Encryption and the tempdb system database

The tempdb system database is encrypted if any other database on the SQL Server instance is encrypted by using TDE. This encryption might have a performance effect for unencrypted databases on the same SQL Server instance. For more information about the tempdb system database, see tempdb Database.

Transparent Data Encryption and replication

Replication doesn't automatically replicate data from a TDE-enabled database in an encrypted form. Separately enable TDE if you want to protect distribution and subscriber databases.

Snapshot replication can store data in unencrypted intermediate files like BCP files. The initial data distribution for transactional and merge replication can too. During such replication, you can enable encryption to protect the communication channel.

For more information, see Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager).

Transparent Data Encryption and FILESTREAM data

FILESTREAM data isn't encrypted even when you enable TDE.

Transparent Data Encryption scan

To enable TDE on a database, SQL Server must do an encryption scan. The scan reads each page from the data files into the buffer pool and then writes the encrypted pages back out to disk.

To give you more control over the encryption scan, SQL Server 2019 (15.x) introduces TDE scan, which has a suspend and resume syntax. You can pause the scan while the workload on the system is heavy or during business-critical hours and then resume the scan later.

Use the following syntax to pause the TDE encryption scan:

ALTER DATABASE <db_name> SET ENCRYPTION SUSPEND;

Similarly, use the following syntax to resume the TDE encryption scan:

ALTER DATABASE <db_name> SET ENCRYPTION RESUME;

The encryption_scan_state column has been added to the sys.dm_database_encryption_keys dynamic management view. It shows the current state of the encryption scan. There's also a new column called encryption_scan_modify_date, which contains the date and time of the last encryption-scan state change.

If the SQL Server instance restarts while its encryption scan is suspended, a message is logged in the error log on startup. The message indicates that an existing scan has been paused.

Transparent Data Encryption and buffer pool extension

When you encrypt a database using TDE, files related to buffer pool extension (BPE) aren't encrypted. For those files, use encryption tools like BitLocker or EFS at the file-system level.

Transparent Data Encryption and In-Memory OLTP

You can enable TDE on a database that has In-Memory OLTP objects. In SQL Server 2016 (13.x) and Azure SQL Database, In-Memory OLTP log records and data are encrypted if you enable TDE. In SQL Server 2014 (12.x), In-Memory OLTP log records are encrypted if you enable TDE, but files in the MEMORY_OPTIMIZED_DATA filegroup are unencrypted.

Move a TDE Protected Database to Another SQL Server
Enable TDE on SQL Server Using EKM
Extensible Key Management Using Azure Key Vault (SQL Server)

Transparent Data Encryption with Azure SQL Database
Get started with Transparent Data Encryption (TDE) on SQL Data Warehouse
SQL Server Encryption
SQL Server and Database Encryption Keys (Database Engine)

See also

Security Center for SQL Server Database Engine and Azure SQL Database
FILESTREAM (SQL Server)