Skip to main content
Delphix

How to set up SQL Server Transparent Data Encryption (TDE) for use with the Delphix Engine

Applicable Delphix Versions

  • 5.1
  • 5.0
  • 4.3
  • 4.2
  • 4.1
  • 4.0
  • 3.2

Summary

The Delphix Engine can be used with SQL Server TDE dSources and VDBs. TDE itself is transparent to the Delphix Engine when set up correctly. If you experience problems with TDE using the Delphix Engine, then it is likely that non Delphix managed database restore operations for TDE databases to the same Staging or Target hosts would also fail.

This article explains how SQL Server TDE works and describes the SQL Server server set up steps for Source, Staging, and Target hosts in a way that shortcuts some of the more complex official SQL Server documentation. The procedures here provide the most basic and standard set up for TDE. Many variations are possible which are usable but out of scope.

Generic Frequently Asked Questions

  • Does the Delphix Engine support MSSQL 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 MSSQL 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 symmetrical 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.
     
  • Database Encryption Key (DEK)
    Is the public certificate for encrypting other user keys for the user databases. It is paired with the DMK and is used to encrypt user keys.You can in theory create lots of these and use one for each database but as they use the same underlying key, there is no benefit for doing so. This key itself is encrypted by the SMK.
     
  • User Database Encryption Key
    This is a certificate that is part of the database itself. It goes where the database goes and is included in the backups. This key itself is encrypted by the DEK.

Functional Flow

  1. Data written to the database is encrypted using the User Database Encryption Key.
  2. Data read from the database is decrypted using the DMK.
  3. Therefore, the database cannot be read/written to without the DMK even though the User Database Encryption Key is part of the database.Thay key needs the DMK to unlock (decrypt) it for use and and the DMK for reading data out of the database.
  4. Because the DMK and DEK themselves are encrypted by an instance specific SMK, they are not portable as-is. They need to be unlocked (decrypted) for export on one instance and then imported and locked (encrypted) by the unique SMK on the target instance.

Setting Up TDE on a Database

  1. Create the Database Master Key (DMK)
USE master;  
GO  
CREATE MASTER KEY ENCRYPTION BY PASSWORD='<PASSWORD_1>';  
GO
  1. Create the Database Encryption Key (DEK)
    (more commonly called the "certificate")

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

CREATE CERTIFICATE MyServerCert WITH SUBJECT='My DEK Certificate';  
GO
  1. Create the User Database Encryption Key
    (another type of certificate embedded in the database itself)
USE <DATABASE_NAME>;  
GO  
CREATE DATABASE ENCRYPTION KEY  
WITH ALGORITHM = AES_128  
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;  
GO  
  1. Encrypt the database
ALTER DATABASE <DATABASE_NAME>  
SET ENCRYPTION ON;  
GO 
  1. Export the DMK and DEK
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. Import the DMK and DEK
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD='<PASSWORD_3>';
GO
CREATE CERTIFICATE MyServerCert FROM FILE='<CERT_BACKUP_PATH>' WITH PRIVATE KEY (FILE='<KEY_BACKUP_PATH>', DECRYPTION BY PASSWORD='<PASSWORD_2>');
GO

What happens if you skip this step?

If the DMK and DEK are not properly restored on the staging or target instances, SQL Server will throw an error that will be passed back the Delphix Engine UI. You may see the following error message:

Changed database context to 'master'.
RESTORE DATABASE [VmyDatabase_54F] WITH RECOVERY;
Msg 33111, Level 16, State 3, Server 10-43-12-70, Line 1
Cannot find server certificate with thumbprint '0x4AD40E8A82A54842F39E73FFEE12CE995114B034'.
Msg 3167, Level 16, State 1, Server 10-43-12-70, Line 1
RESTORE could not start database 'VmyDatabase_54F'.
Msg 3013, Level 16, State 1, Server 10-43-12-70, Line 1
RESTORE DATABASE is terminating abnormally.
Msg 50000, Level 11, State 127, Server 10-43-12-70, Line 3
error_restoring_database
die : Error: recover_vdb failed
At C:\Program Files\Delphix\DelphixConnector\564dfc5c-3c2f-d5d9-e381-7230a6260b
e6-vdb-2\SCRIPT\Provision.ps1:23 char:12
+         die <<<<  "$($args[0])"
    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorExcep 
   tion
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorExceptio 
   n,die

Getting TDE related information out of SQL Server

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

Listing databases that use TDE
USE master;
GO
SELECT name,
    database_id,
    is_master_key_encrypted_by_server FROM [sys].[databases] 
WHERE is_encrypted=1;
GO
Listing certificates on the SQL Server instance
USE master;
GO
select * FROM [sys].[certificates];
GO
Listing database encryption states
USE master;
GO
SELECT * FROM [sys].[dm_database_encryption_keys];
GO
Getting a consolidated list of databases, with used certificates, keys, and validity periods
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

The encryption_state is a enumerated value that indicates the current real state of the database, not just whether it is designated as using TDE or not. Some states have transitions which are noted by percent_complete. Where not applicable, percent_complete will be "0". The values are as follows:

0 Not encrypted at all and no key assigned
1 Unencrypted
2 Encryption in progress
3 Encrypted
4 Key change in progress
5 Decryption in progress
6 Decryption being updated with new keys