Set up MS SQL TDE

Transparent Data Encryption (TDE) is the MS SQL feature to encrypt database data and transaction log without changing an application. The database engine encrypts or decrypts data during execution of queries.

info

TDE is supported only in the SQL Server Enterprise Edition.

To configure the TDE on the database, do the following:

1. Create a master key in the master database. You can create only one master key per SQL Server instance.

   To generate the master key, run the following query and remember to specify the password for it:

   USE master
   go
   CREATE MASTER KEY ENCRYPTION
   BY PASSWORD='<your master password>';   --Use strong password here
   go

2. Create the certificate to encrypt the Database Encryption Key. The previously created master key will protect the Certificate.

   USE master
   GO
   CREATE CERTIFICATE TDE_CERT
   WITH SUBJECT='TDE_certificate';
   go

3. Copy the certificate to replicas.

   • If you don't use AlwaysON, skip this step.

   • If you use the AlwaysON feature of SQL Server, you need to have the same certificate installed on all servers.

     For that, do the following:

     1. On all other servers, create master keys described in Step 1. It's not necessary to have the same password on all servers.

     2. Back up the previously created certificate:

        USE master
        GO
        BACKUP CERTIFICATE TDE_CERT
        TO FILE = 'C:\Temp\TDE_CERT'                         --Use the directory with access from SQL Server
        WITH PRIVATE KEY (file='C:\Temp\TDE_CERT_Pirvate',
        ENCRYPTION BY PASSWORD='<backup file password>');    --Use your password here

   3. Copy the certificate and private key files to all your servers to a folder where SQL Server can access it.

   4. Restore the certificate on all the servers:

      CREATE CERTIFICATE TDE_CERT
      FROM FILE = 'C:\Temp\TDE_CERT'
      WITH PRIVATE KEY
      (
          FILE = 'C:\Temp\TDE_CERT_Pirvate',
          DECRYPTION BY PASSWORD = '<backup file password>' --The password used to create cert backup
      );

4. Run the following queries to create database encryption key.

   The database encryption key uses a certificate to encrypt the database data.

   USE workfusion;
   GO
   CREATE DATABASE ENCRYPTION KEY
   WITH ALGORITHM = AES_128
   ENCRYPTION BY SERVER CERTIFICATE TDE_CERT;
   GO

   If you use AlwaysON, run the above command and perform the next step on the PRIMARY server. As these changes are related to the database, they are replicated on all replicas.

   To look for the encrytion key, use the following query:

   SELECT DB_NAME(database_id) AS DatabaseName,
       encryption_state AS EncryptionState,
       key_algorithm AS Algorithm,
       key_length AS KeyLength
   FROM sys.dm_database_encryption_keys
   WHERE DB_NAME(database_id) = 'workfusion';
   GO

   

5. Enable TDE in the WorkFusion database by running the following query:

   ALTER DATABASE workfusion SET ENCRYPTION ON;

   To check if the TDE is turned on for the database, use the following query:

   SELECT name,
       is_encrypted
   FROM sys.databases
   WHERE name = 'workfusion';