How to Enable Encryption in SQL Server

Galaxy Glossary

How do I enable Transparent Data Encryption in SQL Server?

ALTER DATABASE … SET ENCRYPTION ON activates Transparent Data Encryption (TDE) to protect data at rest.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why should I encrypt a SQL Server database?

Encryption shields data files and backups from unauthorized reads. Even if someone copies the .mdf or backup file, the data remains unreadable without the key hierarchy, meeting compliance rules like PCI-DSS and GDPR.

What are the prerequisites?

Run SQL Server 2019+ Enterprise or Azure SQL DB. You need CONTROL SERVER permission, a strong password for the master key, and disk space for encrypted tempdb growth.

How do I enable encryption step-by-step?

Step 1 – Create the database master key (DMK)

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd!Strong';

Step 2 – Create or reuse a server certificate

CREATE CERTIFICATE TDE_ServerCert WITH SUBJECT = 'TDE Cert';

Step 3 – Create the database encryption key (DEK)

USE EcommerceDB; CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TDE_ServerCert;

Step 4 – Turn encryption on

ALTER DATABASE EcommerceDB SET ENCRYPTION ON;

How do I verify encryption status?

Query sys.dm_database_encryption_keys or sys.databases. A status of 3 or 2 means encryption is active or in progress.

Can I disable or pause encryption?

Disable with ALTER DATABASE EcommerceDB SET ENCRYPTION OFF; but note that decryption is slow and blocks backups until complete.

Best practices for production deployments

Back up the server certificate and private key immediately: BACKUP CERTIFICATE TDE_ServerCert TO FILE = 'C:\backup\TDE_ServerCert.cer' WITH PRIVATE KEY (FILE = 'C:\backup\TDE_ServerCert.pvk', ENCRYPTION BY PASSWORD = 'CertBackUp@123'); Store backups off-site.

Why How to Enable Encryption in SQL Server is important

How to Enable Encryption in SQL Server Example Usage


--Encrypt the Orders database that stores customer transactions
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Sup3r$ecret!';
CREATE CERTIFICATE OrdersTDECert WITH SUBJECT = 'Orders DB TDE';
USE Orders;
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE OrdersTDECert;
ALTER DATABASE Orders SET ENCRYPTION ON;
--Verify
SELECT db.name, dek.encryption_state
FROM sys.databases AS db
JOIN sys.dm_database_encryption_keys AS dek
  ON db.database_id = dek.database_id
WHERE db.name = 'Orders';

How to Enable Encryption in SQL Server Syntax


--Create master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd!Strong';

--Create server certificate
CREATE CERTIFICATE TDE_ServerCert
    WITH SUBJECT = 'TDE Cert';

--Enable TDE on ecommerce database
USE EcommerceDB;
CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE TDE_ServerCert;
ALTER DATABASE EcommerceDB SET ENCRYPTION ON;

--Check status
SELECT name, encryption_state, key_algorithm, key_length
FROM sys.databases d
JOIN sys.dm_database_encryption_keys k ON d.database_id = k.database_id;

Common Mistakes

Mistake: Skipping the certificate backup after enabling TDE. If the certificate is lost, you can’t restore encrypted backups. Fix: Immediately run BACKUP CERTIFICATE … WITH PRIVATE KEY and store it securely off-site.
Mistake: Using the default AES_128 algorithm. It’s weaker and can breach compliance. Fix: Specify AES_256 in CREATE DATABASE ENCRYPTION KEY for stronger security.