SQL Server TDE Encryption

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

Applies to:

  • SQL Server Versions 2008-2014 (All)
  • SQL Server Version 2016 (Enterprise)

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

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


Tag page
You must login to post a comment.