Enabling Transparent Data Encryption on EngageOne Designer Databases

Product Feature: Software Vulnerability

Operating System: Windows

Database: MSSQL

 

To enable Transparent Data Encryption (TDE) for use on EngageOne produces the following steps will need to be completed. The TDE feature is only available on the following versions of SQL Server:

  • SQL 2019 Developer, Enterprise
  • SQL 2016 Developer, Enterprise
  • SQL 2014 Developer, Enterprise
  • SQL Server 2012 Developer, Enterprise
  • SQL Server 2008 R2 Datacenter, Developer, Enterprise, Datacenter
  • SQL Server 2008 Developer, Enterprise

Create Master Key

First a key will need to be created and it must be created in the 'master' database. Run the following to do this:

 
USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='yourpasswordhere';
GO

Create Certificate

After the key has been created, the certificate need to be made and this can be done with the following SQL:

 
CREATE CERTIFICATE E1_TDE_Cert
WITH
SUBJECT='Database_Encryption';
GO
 

The certificate will be made with the name 'E1_TDE_Cert'. The name of this can be changed to better suit the environment 

Create Database Encryption Key

The database needs to now be encrypted. Change the <DB> to the name of the database that is being encrypted, for example 'Doc1_Repository'.
 
USE <DB>
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE E1_TDE_Cert;
GO
 

Enable Encryption

Now the encryption needs to be set to ON to take affect.

 
ALTER DATABASE <DB>
SET ENCRYPTION ON;
GO
 

Backup Certificate

Now a backup of the certificate needs to be done in the event that the database needs to be restored to a different server. To do so, use the following SQL:
 
BACKUP CERTIFICATE E1_TDE_Cert
TO FILE = 'C:\temp\E1_TDE_Cert'
WITH PRIVATE KEY (file='C:\temp\E1_TDE_CertKey.pvk',
ENCRYPTION BY PASSWORD='yourpasswordhere')
 

This key file needs to be safe for future use.

Restoring a Certificate

To restore an encrypted database, a new master key will need to be created on the new SQL server.

 
USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='yourpasswordhere';
GO
 

Now the key that was backed up from the first server will need to be imported

 
USE MASTER
GO
CREATE CERTIFICATE E1_TDE_Cert
FROM FILE = 'C:\Temp\E1_TDE_Cert'
WITH PRIVATE KEY (FILE = 'C:\E1_TDE_Cert_Key.pvk',
DECRYPTION BY PASSWORD = 'yourpasswordhere' );
 

Now the database can be restored and used.

UPDATED:  June 24, 2020