DBAtlas · Database operations atlas

Set up TDE: master key, certificate, certificate backup and encryption progress

SQL ServerSecurity

Command

USE master;
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'<GucluParola_1>';
CREATE CERTIFICATE [TDE_Cert_2026]
    WITH SUBJECT = N'TDE certificate', EXPIRY_DATE = '20361231';

-- Back it up NOW: without cert + private key the backups cannot be restored anywhere
BACKUP CERTIFICATE [TDE_Cert_2026]
    TO FILE = N'D:\Keys\TDE_Cert_2026.cer'
    WITH PRIVATE KEY (FILE = N'D:\Keys\TDE_Cert_2026.pvk',
                      ENCRYPTION BY PASSWORD = N'<GucluParola_2>');

USE [Musteri];
CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE [TDE_Cert_2026];
ALTER DATABASE [Musteri] SET ENCRYPTION ON;

-- Progress and certificate backup date:
SELECT DB_NAME(dek.database_id) AS db, dek.encryption_state_desc, dek.percent_complete,
       dek.encryption_scan_state_desc, c.name AS cert_name, c.expiry_date, c.pvt_key_last_backup_date
FROM   sys.dm_database_encryption_keys dek
  LEFT JOIN master.sys.certificates c ON c.thumbprint = dek.encryptor_thumbprint;

-- On the target server (before restore / AG secondary):
-- CREATE CERTIFICATE [TDE_Cert_2026] FROM FILE = N'D:\Keys\TDE_Cert_2026.cer'
--   WITH PRIVATE KEY (FILE = N'D:\Keys\TDE_Cert_2026.pvk', DECRYPTION BY PASSWORD = N'<GucluParola_2>');

What it does

Creates a database master key and certificate in master, backs up the certificate with its private key, creates an AES-256 database encryption key and encrypts the data and log files at rest; shows the progress of the background encryption scan.

When to use it

For data-at-rest encryption requirements such as GDPR/PCI, and against theft of backup files and MDF/LDF files.

Caution

If the certificate + private key backup or its password is lost, the TDE database and all its backups are PERMANENTLY unreadable; keep the backup in a secure vault away from the server. Enabling TDE on any database also encrypts tempdb (a small server-wide CPU cost). The encryption scan is I/O heavy; on 2019+ it can be paused with ALTER DATABASE ... SET ENCRYPTION SUSPEND/RESUME. For AG/log shipping the certificate must be installed on every target server first. TDE does not encrypt data in memory or on the network.

Open in DBAtlas →

Related