Transparent Database Encryption

Turning on Transparent Data Encryption for a database takes exactly four steps: create a master key in master, create a server certificate to protect it, create the database encryption key encrypted by that certificate, and set the database to ENCRYPTION ON. The commented-out section shows how to back up the certificate with its private key - essential before you ever need to restore this database anywhere else.

USE master
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ThisIsMyTestPassword!'
GO

CREATE CERTIFICATE TDE_Certificate
WITH SUBJECT = 'TDE Encryption Server Certificate'
GO

/* --Backup the certificate, the symmetric key does not require backup and can simply be recreated.
BACKUP CERTIFICATE TDE_Certificate
	TO FILE = 'C:\TDE_Certificate.cer'
	WITH PRIVATE KEY
	(
		FILE = 'C:\TDE_Certificate.pvk',
		ENCRYPTION BY PASSWORD = '!!Certificate!!Backup!!Password!!'
	)
*/


USE JMP_Encryption_Testing
GO

CREATE DATABASE ENCRYPTION KEY
	WITH ALGORITHM = AES_256
	ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate
GO

ALTER DATABASE JMP_Encryption_Testing
SET ENCRYPTION ON
GO