Sitemap

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

    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