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

Introduction

Transparent Data Encryption (TD) was introduced in SQL Server 2008. Its main purpose was to protect data by encrypting the physical files, both the data (mdf) and log (ldf) files (as opposed to the actual data stored within the database). TDE encrypts SQL Server, Azure SQL Databases, and Azure SQL Data Warehouse data files.

Instructions

step-by-step guide:

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 the encryption and decryption of the StackOverflow 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 StackOverflow;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256 
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
ALTER DATABASE StackOverflow
SET ENCRYPTION ON;
GO

The encryption and decryption operations are scheduled on background threads by SQL server. You can use the DMVs to query the cert and key information.

What happens when you enable TDE?

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.

Is Tempdb encrypted too when you enable TDE on any database in a SQL instance?

Yes, 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 instance.

Are you able to add an encrypted database to an AlwaysOn availability group?

Yes, you can. To encrypt databases that are part of an availability group, create the master key and certificates on all secondary replicas before creating the database encryption key on the primary replica.

If a certificate is used to protect the database encryption key, backup the certificate created on the primary replicate, and then create the certificate from a file on all secondary replicas before creating the database encryption key on the primary replica.

Here is the script to view all the encrypted DBs and their certificate names on a SQL server instance:

SELECT db_name(database_id) AS [Database Name],
	dek.key_length as [Key Length],
	case encryption_state when '0' then 'No database encryption key present, no encryption'
	when '1' then 'Not Encrypted' 
	when '2' then 'Encryption in Progress'
	when '3' then 'Encrypted'
	when '4' then 'Key Change in Progress'
	when '5' then 'Decryption in Progress'
	when '6' then 'Protection Change in Progress'
	end as [Encryption Status] ,
	key_algorithm as [Key Algorithm],
	Name as [Cert Name],
	pvt_key_encryption_type_desc as [Pvt Key Desc],
	[subject] as [Subject],
	[expiry_date] as [Expiry Date],
	[start_date] as [Start Date]
FROM sys.certificates c
INNER JOIN sys.dm_database_encryption_keys dek ON c.thumbprint = dek.encryptor_thumbprint