Date: May 2, 2016
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.
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.
To enable TDE in MS SQL for use with ERPM perform the following steps:
- Create a TDE encryption certificate:
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
- Encrypt the ERPM database using the Master TDE Cert above:
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
- Backup the Master encryption cert, including the private key: [Best to remove these from the server and store in a secure place.]
USE master; GO BACKUP CERTIFICATE MyServerCert TO FILE = 'C:\tmp\MyServerCert' WITH PRIVATE KEY
- View all certs on the server:
USE master GO SELECT * from sys.certificates
- List the databases that are encrypted:
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;
- 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.
- Enterprise Random Password Manager (ERPM)
- Random Password Manager (RPM)