SQL Server TDE Encryption

Website Documentation for your KeePass client and Pleasant Password Server

These steps apply setting up Microsoft SQL Server with TDE encryption: a seamless and efficient method of encrypting the database.

Applies to:  SQL Server, Versions 2008-2019 (Available in select editions)

Prerequisites:

• Ensure the hard drive has 1 GB of free space, or for larger installations see hardware requirements
• Create a new blank database instance

Steps

1. (Recommended) Set your Database Transaction Backup/Recovery model to "Simple".
   
   • This clears/automates Transaction Log maintenance

     USE master; 
     GO 

     ALTER DATABASE model SET RECOVERY SIMPLE;
     GO

   • However, if you do have trained knowledgeable staff, you can Backup the Transaction Logs now, and use the more advanced options.
2. Run SQL commands on your database:
   

   • First replace your values: database, password, Certificate name & subject, and encryption algorithm
     
     • Possible encryption algorithms include: AES_256, AES_192, AES_128, or TRIPLE_DES_3KEY
   • Run the following:

   USE master; 
   GO   
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>';
   go 
   CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate'; 
   go 
   USE MyDatabase; 
   GO 
   CREATE DATABASE ENCRYPTION KEY 
   WITH ALGORITHM = AES_256 
   ENCRYPTION BY SERVER CERTIFICATE MyServerCert; 
   GO 
   ALTER DATABASE MyDatabase
   SET ENCRYPTION ON; 
   GO 

3. Backup certificate and private key

   • The backups are also encrypted so the certificate and private key should also be backed up (see Examples): https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-certificate-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15 

   • For Example:

   BACKUP CERTIFICATE MyServerCert
   TO FILE = 'C:\Temp\PleasantPasswordCert.cer'
   WITH PRIVATE KEY
   (
   FILE = 'C:\Temp\PleasantPasswordPrivateKey.pfx',
   ENCRYPTION BY PASSWORD = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxx'
   )

   • These can be restored with a CREATE CERTIFICATE statement (see Remarks)

General Reference Links

Transparent Data Encryption (TDE)

SQL Server Encryption

To Revert Back To Normal

To bring the database back to normal, run these commands step by step.

This will remove the database encryption, will drop the database encryption key, drop the certificate, and drop the master key encryption:

Wait for decryption operation to complete. Then look for a value of 1 in the query below it.

• First replace the values: database, Certificate name
• Run the following, statements, step by step:

ALTER DATABASE MyDatabase;
SET ENCRYPTION OFF;
GO

/* Wait for decryption operation to complete */

SELECT encryption_state
FROM sys.dm_database_encryption_keys;
GO

/* Look for a value of 1 */

DROP DATABASE ENCRYPTION KEY;

USE master; 

GO 

ALTER MASTER KEY DROP ENCRYPTION;
GO

DROP CERTIFICATE MyServerCert; 
GO