How to Enable Encryption at Rest in SQL Server

How do I enable encryption at rest in SQL Server?

Transparent Data Encryption (TDE) secures data and log files on disk by encrypting them automatically without changing application code.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why enable encryption at rest in SQL Server?

Disk-level threats expose databases to theft or unauthorized copies. Transparent Data Encryption (TDE) encrypts MDF, NDF, and LDF files, satisfying PCI-DSS, HIPAA, and GDPR requirements with minimal configuration.

How does Transparent Data Encryption (TDE) work?

TDE uses a database encryption key (DEK) stored in the boot record. The DEK encrypts pages as they move to disk and decrypts them in memory, so queries on tables like Customers or Orders remain unchanged.

What prerequisites are needed?

SQL Server Enterprise (or Azure SQL Database), CONTROL SERVER & ALTER ANY DATABASE permissions, and a secure location to back up the certificate and private key.

How to create a master key and certificate?

Create a master key in master, then generate a server certificate. Back up both objects immediately to prevent data-loss scenarios.

How to enable TDE on the ecommerce database?

Generate the DEK inside EcommerceDB, specify AES_256, and turn encryption on. SQL Server starts encrypting pages in the background while the database stays online.

How to verify encryption status?

Query sys.databases or sys.dm_database_encryption_keys. A status of 3 (encrypted) confirms that all objects, including Products and OrderItems, are protected on disk.

How to back up and restore encrypted databases?

Always back up the certificate and its private key along with regular database backups. During restore to another server, create the master key, restore the certificate, then restore the database.

What are best practices?

Rotate certificates periodically, store key backups off-site, monitor encryption state, and test restores routinely.

Why How to Enable Encryption at Rest in SQL Server is important

How to Enable Encryption at Rest in SQL Server Example Usage


-- Encrypt the EcommerceDB that hosts Customers, Orders, Products, OrderItems
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPass!2024';
CREATE CERTIFICATE TDE_ServerCert
WITH SUBJECT = 'TDE Certificate for EcommerceDB';

BACKUP CERTIFICATE TDE_ServerCert
  TO FILE = 'C:\tde\TDE_ServerCert.cer'
  WITH PRIVATE KEY (
      FILE = 'C:\tde\TDE_ServerCert_PrivateKey.pvk',
      ENCRYPTION BY PASSWORD = 'AnotherStrongPass!2024'
  );

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

How to Enable Encryption at Rest in SQL Server Syntax


-- 1. Create master key in the master database
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPass!2024';

-- 2. Create a server certificate
CREATE CERTIFICATE TDE_ServerCert
WITH SUBJECT = 'TDE Certificate for EcommerceDB';

-- 3. Back up the certificate and private key
BACKUP CERTIFICATE TDE_ServerCert
  TO FILE = 'C:\tde\TDE_ServerCert.cer'
  WITH PRIVATE KEY (
      FILE = 'C:\tde\TDE_ServerCert_PrivateKey.pvk',
      ENCRYPTION BY PASSWORD = 'AnotherStrongPass!2024'
  );

-- 4. Create encryption key in target database
USE EcommerceDB;
CREATE DATABASE ENCRYPTION KEY
  WITH ALGORITHM = AES_256
  ENCRYPTION BY SERVER CERTIFICATE TDE_ServerCert;

-- 5. Enable encryption
ALTER DATABASE EcommerceDB SET ENCRYPTION ON;

-- 6. Verify status
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 = 'EcommerceDB';

Common Mistakes

Ignoring certificate backups – without the certificate and private key, you cannot restore or attach the encrypted database elsewhere. Always back up to an offline, secure location.
Creating the database encryption key but forgetting to run ALTER DATABASE ... SET ENCRYPTION ON. The database remains unprotected until encryption is explicitly enabled.