Setting Up SQL Server Transparent Data Encryption (TDE) For Use With The Delphix Engine (KBA1401)

Generic Frequently Asked Questions 

• Does the Delphix Engine support SQL Server TDE?
  Yes. On all versions. It is totally transparent to the Delphix Engine.
   
• Are there required Delphix Engine setup steps for TDE?
  No.
   
• Are there required Staging and Target host setup steps for TDE?
  Yes. However these are not Delphix specific. They need to be done on any host where an encrypted database will be restored to. That happens to include Staging and Target host SQL Server instances.
   
• Is there an impact on the Delphix Engine when using TDE?
  Yes. There is an impact on the compression ratio for storage of databases that use TDE. Therefore, a TDE dSource or VDB will use more storage on the Delphix Engine than a non encrypted database. To understand the impact of this, you should perform a test with a database of a similar size and frequency of transaction log change to do some forecasting.

Background 

Setting up TDE is easy, but managing it can be complex. While this article is useful for basic proof-of-concept set up and lab testing, customers should refer to official SQL Server documentation and refer TDE issues directly to Microsoft. 

It helps to understand some of some of the components of a TDE keystore and what they do.

• Server/Instance Master Key (SMK)
  You do not need to create this. It is created automatically on installation and is unique to the instance. This is symmetric in that the same key does both encryption and decryption. So there is no key/certificate pair.
   
• Database Master Key (DMK)
  Is a private decryption key for the database/s which is itself encrypted by by the SMK. This belongs to the "master" database. You need to create this. This key itself is encrypted by the SMK.
   
• Server Certificate
  Several mechanisms are available to encrypt the Database Encryption Key (DEK). This most commonly uses a Server Certificate, though Extensible Key Management (EKM) modules and other configurations are possible (see Microsoft's Encryption Hierarchy document).
  
  Server Certificates are stored in the SQL Server instance, and are encrypted using the DMK. Server Certificates can be changed without completely re-encrypting the database.
   
• Database Encryption Key (DEK)
  This is a symmetric key that is part of each encrypted database, and is used to encrypt and decrypt the data in that database. It goes where the database goes and is included in the backups, but it cannot be decrypted without the Server Certificate (or other mechanism) that was used to protect it.

Setting Up TDE on a Source Database

1. Create the Database Master Key (DMK)

USE master;  
GO  
CREATE MASTER KEY ENCRYPTION BY PASSWORD='<PASSWORD_1>';  
GO

2. Create the Server Certificate

NOTE: You could create multiple Server Certificates and use different ones for different databases. For simplicity this is not recommended for a lab environment.

CREATE CERTIFICATE MyServerCert WITH SUBJECT='My Server Certificate';  
GO

3. Create the Database Encryption Key
   (this key will be embedded in the database, but will be encrypted by the Server Certificate)

USE <DATABASE_NAME>;  
GO  
CREATE DATABASE ENCRYPTION KEY  
WITH ALGORITHM = AES_128  
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;  
GO  

4. Encrypt the database

ALTER DATABASE <DATABASE_NAME>  
SET ENCRYPTION ON;  
GO 

5. Monitor the progress of database encryption, and wait for the encryption_state value to reach 3 (ENCRYPTED). In SQL Server 2019 and later, the encryption_state_desc column can be used for a more meaningful description.

SELECT DB_NAME(database_id) AS database_name, encryptor_thumbprint, encryption_state FROM [sys].[dm_database_encryption_keys];

6. Export the Server Certificate so that it can be transferred to another server

USE master;
GO
BACKUP CERTIFICATE MyServerCert TO FILE='<CERT_BACKUP_PATH>' WITH PRIVATE KEY (FILE='<KEY_BACKUP_PATH>', ENCRYPTION BY PASSWORD='<PASSWORD_2>');
GO

Setting Up TDE on Staging/Target Instances 

Note:

This must be done before attempting to link a dSource or provision a VDB.

1. Copy the exported certificate and key file to every Staging or Target host that could potentially be used for the TDE database.

2. Create a new Database Master Key (DMK) for each SQL Server instance, if one has not already been created.

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD='<PASSWORD_3>';
GO

3. Import the Server Certificate that was exported from the Source Server.

USE master;
GO
CREATE CERTIFICATE MyServerCert FROM FILE='<CERT_BACKUP_PATH>' WITH PRIVATE KEY (FILE='<KEY_BACKUP_PATH>', DECRYPTION BY PASSWORD='<PASSWORD_2>');
GO

4. Delete the exported Server Certificate, as it is now stored and secured by each SQL Server instance.

Getting TDE Related Information From SQL Server 

TDE configurations are stored in the master database. Therefore, you will need permissions to query master directly.

USE master;
GO
SELECT name,
    database_id,
    is_master_key_encrypted_by_server FROM [sys].[databases] 
WHERE is_encrypted=1;
GO

USE master;
GO
select * FROM [sys].[certificates];
GO

USE master;
GO
SELECT * FROM [sys].[dm_database_encryption_keys];
GO

USE master;
GO
SELECT a.name,
    a.database_id,
    b.encryption_state,
    b.percent_complete,
    b.key_algorithm,
    b.key_length,
    c.name as DEK,
    c.issuer_name,
    c.cert_serial_number,
    c.subject,
    c.start_date,
    c.expiry_date FROM [sys].[databases] a 
JOIN [sys].[dm_database_encryption_keys] b ON (a.database_id=b.database_id) 
JOIN [sys].[certificates] c ON (b.encryptor_thumbprint=c.thumbprint) 
WHERE a.is_encrypted=1;
GO

Rotating Server Certificates

In some environments, it will not be permitted to copy the Server Certificate from the Source SQL Server instance to non-production SQL Server instances.

When this happens, you can create an intermediate VDB in a secure environment, and use this to change the Server Certificate used by that VDB.

dSource [SourceCert installed on source and staging instances]
\_ Intermediate VDB [SourceCert and NonProdCert installed on instance]
  \_ Non-Prod VDB [NonProdCert installed on instance]

Each time the intermediate VDB is Provisioned or Refreshed, its encryption key (DEK) will be secured by the Source Certificate.

To rotate the key on the intermediate VDB each time it is Provisioned or Refreshed, a Configure Clone hook such as the following query can be used:

$ErrorActionPreference = "Stop";

Invoke-Sqlcmd -Database "$env:VDB_DATABASE_NAME" -Query "ALTER DATABASE ENCRYPTION KEY ENCRYPTION BY SERVER CERTIFICATE NonProdCert;" -ServerInstance "$env:VDB_INSTANCE_HOST,$env:VDB_INSTANCE_PORT"

Alternatively, if not running a hook, run the ALTER command manually on the SQL Server instance, using the VDB, then run a snapshot on the VDB upon completion. Use this snapshot to provision/refresh the Non-Production VDB.

For more information on this SQL Server command, see Microsoft's documentation on ALTER DATABASE ENCRYPTION KEY. For more information on Hook Operations for VDBs, see our document Hooks for SQL Server.

Once the Server Certificate has been changed in this way, snapshots of this VDB can be provisioned to servers with only the NonProdCert Server Certificate installed. Note that the underlying Database Encryption Key remains unchanged.

To validate that the certificate has been changed, you can compare the thumbprints in the [sys].[certificates] and [sys.dm_database_encryption_keys] views.

• Due to DLPX-86195: Is_encrypted property is not true after provisioning VDBs from TDE enabled SQL Server Dsource
• The VDB is_encrypted flag may be 0 and the VDB may not list in the consolidated list of encrypted databases. However, the VDB will be encrypted but if monitoring VDBs for such a flag a false negative will be returned.
• If this occurs, run the command to turn off encryption:

ALTER DATABASE <DATABASE_NAME>  
SET ENCRYPTION OFF;  
GO 

• Then turn it on again - Be forewarned - on a large VDB this might take some time to "re-encrypt".

ALTER DATABASE <DATABASE_NAME>  
SET ENCRYPTION ON;  
GO 

Related Articles

• Move a TDE Protected Database to Another SQL Server
• ALTER DATABASE ENCRYPTION KEY (Microsoft SQL Server Documentation)
• sys.dm_database_encryption_keys (Microsoft SQL Server Documentation)
• Encryption Hierarchy (Microsoft SQL Server Documentation)
• Hooks for SQL Server