FAQ: SQL Server TDE (KBA1091)

Last updated
Save as PDF
Share
1. Share
2. Tweet
3. Share

KBA

KBA#1091

What is TDE?

Transparent Data Encryption, or TDE, performs real-time I/O encryption and decryption of the data and log files.

How do I check if TDE is used in SQL Server?

The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an EKM module.

TDE protects data "at rest", meaning the data and log files. It provides the ability to comply with many laws, regulations, and guidelines established in various industries. This enables software developers to encrypt data by using AES and 3DES encryption algorithms without changing existing applications.

The following views provide information about the encryption keys in use on a SQL Server instance:

sys.certificates
sys.dm_database_encryption_keys

What is the error faced while linking a dSource or Provisioning a VDB, if the certificate is not available on PPT/Target server?

The Delphix Engine will throw following error if a certificate is not available:

Cannot find server certificate with thumbprint '[HexKey]'.

To resolve this issue:

Before performing dSource creation or provisioning VDB of a TDE protected database, you must first copy the certificate or asymmetric key that is used to open the DEK to both PPT and Target server.
The certificate or asymmetric key must be installed in the master database of the destination server, so that SQL Server can access the database files.
One must retain copies of both the certificate file and the private key file in order to recover the certificate. The password for the private key does not have to be the same as the database master key password.
SQL Server stores the files created in C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA by default. The file names and locations might be different depending on your SQL Server version and configuration.

What is the compression expected with TDE enabled?

The Microsoft knowledgebase article on Backup Compression indicates:

Encrypted data compresses significantly less than equivalent unencrypted data. If TDE is used to encrypt a database, backup compression will not be able to significantly compress the backup storage. Therefore, using TDE and backup compression together is not recommended.

Based on the data collected from customer implementations, the compression ratio between database size and its compressed backup was 1:1 - that is, there was no saving offered by using backup compression. Similar results can be expected from the compression offered by the Delphix Engine.