ERPM Data Store with Transparent Data Encryption (TDE)

Follow

Version: 1.1
Date: May 2, 2016

Problem

ERPM encrypts its the passwords it manages and stores prior to writing that encrypted value to the database. However, the rest of the data in the database is not encrypted. This means that certain data while at rest is not protected with encryption.

Supplemental Information

Microsoft SQL offers Transparent Data Encryption (TDE) to help protect the data at rest. TDE offers encryption at file level. TDE solves the problem of protecting data at rest, encrypting databases both on the hard drive and consequently on backup media. Enterprises typically employ TDE to solve compliance issues such as PCI DSS which require Data at rest be protected.

TDE does not protect Data in transit nor Data in use. To further protect data in transit, enable the use of SSL and/or IPSec for database communications.

Resolution

To enable TDE in MS SQL for use with ERPM perform the following steps:

  1. Create a TDE encryption certificate:
    Code:
    USE master;
    	GO
    	CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Enter_A_Strong_Password_Here';
    	GO
    	CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'MASTER_TDE_Cert_Subject_Name_Here';
    	GO
  2. Encrypt the ERPM database using the Master TDE Cert above:
    Code:
    USE ERPM_DB_NAME_HERE;
    	GO
    	CREATE DATABASE ENCRYPTION KEY
    	WITH ALGORITHM = AES_256
    	ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
    	GO
    	ALTER DATABASE ERPM_DB_NAME_HERE
    	SET ENCRYPTION ON;
    	GO
  3. Backup the Master encryption cert, including the private key: [Best to remove these from the server and store in a secure place.]
    Code:
    USE master;
    	GO
    	BACKUP CERTIFICATE MyServerCert TO FILE = 'C:\tmp\MyServerCert'
    	WITH PRIVATE KEY
  4. View all certs on the server:
    Code:
    USE master
    	GO
    	SELECT * from sys.certificates
    Click image for larger version

Name:	ListCerts.png
Views:	1
Size:	40.9 KB
ID:	641
  5. List the databases that are encrypted:
    Code:
    USE master
    	GO
    	SELECT
    	database_name = d.name
    	,dek.encryptor_type
    	,cert_name = c.name
    	FROM sys.dm_database_encryption_keys dek
    	LEFT JOIN sys.certificates c
    	on dek.encryptor_thumbprint = c.thumbprint
    	INNER JOIN sys.databases d
    	ON dek.database_id = d.database_id;
    Click image for larger version

Name:	EncryptedDB.png
Views:	1
Size:	29.6 KB
ID:	642
  6. No additional work is required within ERPM.

NOTE: If you export/import a TDE encrypted database to another SQL server, you first must import the Master TDE cert to the new server, or you won’t be able to use the DB on the new server.

Applies To

  • Enterprise Random Password Manager (ERPM)
  • Random Password Manager (RPM)
Was this article helpful?
0 out of 0 found this helpful

Comments

Powered by Zendesk