How to Enable Transparent Data Encryption (TDE) in SQL Server

Encrypting a database at rest is no longer a luxury reserved for large organizations: with Transparent Data Encryption (TDE) in SQL Server you protect the data files, the log files, and even the backups without changing a single line of your application. It is one of the most direct ways to meet compliance requirements such as GDPR and to make sure that an .mdf or .bak file copied improperly cannot be read outside your server.

Prerequisites

• SQL Server 2019 or later — TDE became available in the Standard edition from that version onward; up to SQL Server 2017 it was exclusive to Enterprise.
• An account with elevated permissions (member of the sysadmin role).
• A test database to practice on before applying this in production.
• A secure folder, off the server, to store the certificate backup.

Step 1: Create the Master Key in the master database

Encryption in SQL Server is organized in layers. At the top of that hierarchy sits the Database Master Key, stored in the master database, which protects the certificate you are about to create. Connect to the instance and run:

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'UmaPass!Muito_Forte_2026';
GO

Choose a strong password and keep it safe: you will need it if you ever have to recover the certificate on another server.

Step 2: Create the server certificate

The certificate is the key that will, in practice, protect your database. Stay connected to the master database:

USE master;
GO
CREATE CERTIFICATE TDE_Cert
    WITH SUBJECT = 'Certificado para TDE';
GO

Notice that we did not set a password for the certificate: by default, it is protected by the Master Key you created in the previous step.

Step 3: Back up the certificate (critical step)

This is the step you must never skip. If the certificate is lost, no one will ever open the encrypted database again — not even you. Back up the certificate and its private key immediately:

USE master;
GO
BACKUP CERTIFICATE TDE_Cert
    TO FILE = 'C:\Backup\TDE_Cert.cer'
    WITH PRIVATE KEY (
        FILE = 'C:\Backup\TDE_Cert.pvk',
        ENCRYPTION BY PASSWORD = 'OutraPass!Forte_2026'
    );
GO

Copy both files (.cer and .pvk) to a secure location separate from the server, and store the passwords in a credentials manager. Without them, restoring the database on a new machine is impossible.

Step 4: Create the Database Encryption Key

Now switch to the database you want to protect and create the Database Encryption Key (DEK), specifying the algorithm and the certificate that will protect it:

USE MinhaBaseDeDados;
GO
CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;
GO

AES_256 is the recommended algorithm today, as it offers a good balance between security and performance.

Step 5: Turn encryption on

The final switch is all that is left. This command starts the encryption in the background, page by page, without taking the database offline:

ALTER DATABASE MinhaBaseDeDados
    SET ENCRYPTION ON;
GO

On large databases this first encryption pass can take a while, but the application keeps responding normally throughout the process.

Check the result

To confirm the status, query the system view that shows encryption for each database. An encryption_state of 3 means "encrypted"; a value of 2 means encryption is still in progress:

SELECT
    DB_NAME(database_id) AS base_de_dados,
    encryption_state,
    percent_complete,
    key_algorithm,
    key_length
FROM sys.dm_database_encryption_keys;
GO

When encryption_state reaches 3 and percent_complete is 0, your database is fully protected on disk.

Remember: TDE protects data at rest. It does not encrypt data in transit over the network (use TLS for that) nor hide it from users who already have read permissions (use permissions and masking for that).

Conclusion

In five steps you enabled encryption at rest that protects data, logs, and backups transparently for your applications. The natural next step is to simulate a recovery: restore the certificate and database backups on a test server, because a recovery plan is only worth something once it has been tested. Have you already tried opening an encrypted database on another machine to confirm that your certificate backups really work?