SQL Server TDE (Data at Rest Encryption)





What is TDE ?

Transparent Data Encryption (TDE)  is used by Microsoft, Oracle, and other Database provider companies to encrypt data files. This solves the compliance issue generally known as Data at Rest Encryption.

TDE was first introduced by Microsoft with SQL Server 2008. TDE is available with Evaluation, Developer and Enterprise Editions only. (Also Datacenter Editions in some older versions).

How TDE works ?

With TDE Database Files are encrypted, hence, prevents the Data Files from exploitation if data files or backups are copied out with bad intentions. 

Some Facts about TDE

  • During Backup, if compression is enabled, it cannot take much advantage from compression due to the encryption.
  • If any of the database is encrypted on an instance, TempDB is anyways encrypted.
  • TDE only encrypts Data at Rest. Data in Motion, Data in Transit or Data in Memory is not encrypted.
  • Any File groups which are Read-Only, won't be encrypted, hence TDE implementation will fail.
  • Once TDE is enabled, Mirroring or Log Shipped databases on the other side will also need be encrypted as TDE keeps Transaction Logs Encrypted.
  • Even though the Certificate Expires, it still encrypts database. Certificate Expiration is more of compliance related and you can set custom expiry date with EXPIRY_DATE option while creating the certificate, by default it is a year.


Encryption Hierarchy (https://msdn.microsoft.com/en-us/library/bb934049.aspx)

  • Encryption Hierarchy starts with Windows level Data Protection API (DPAPI). During the setup or installation of SQL Server Instance, the Service Master Key is encrypted with DPAPI. 
  • Service Master Encrypts the Database Master Key on Master database.
  • Using Database Master key, a Certificate on Master Database is created. 
  • The Certificate on Master Database is used by all other databases on same instance to create Database Encryption Key (DEK). 
  • Finally the DEK is used to Encrypt the entire User Database.

Implementing TDE

  1. Create Master Key on Master Database
  2. Create Certificate protected by Master Key on Master Database
  3. Create Database Encryption Key using Certificate on User Database that has to be encrypted
  4. Change the Database Option setting to Encryption
  5. Backup Certificate and Private Key at safe place (optional but recommended)


Demo

Create Master Key

Use Master
Go
Create Master Key
Encryption By Password ='@$trongPa$$word@'
Go


Create Certificate

use master
Go
create Certificate TDE_CERT
WITH SUBJECT ='TDE Certificate'
Go



Create DEK (Database Encryption Key) using the certificate created in last step. This step has to be run on the database where the TDE has to be enabled

Use myDB
Go
Create Database Encryption Key
with algorithm = AES_256
Encryption By Server Certificate TDE_CERT;
GO



Finally Enable TDE for the database

use master
Go
Alter database myDB
Set encryption on
Go

You can check the status of encryption by using DMV: sys.dm_database_encryption_keys

select encryption_state, percent_complete, * from sys.dm_database_encryption_keys

Encryption_States:
0 - No Encryption
1 = Unencrypted
2 = Encryption in Progress
3 = Encrypted
4 = Key Change in Progress
5 = Decryption in Progress (Disabling TDE)
6 = Protection change in progress



Backup Certificate


Use Master
Go
backup certificate TDE_CERT
To FIle='C:\encryption\TDE_CERT.cer'
with private key (file='C:\encryption\pkey.key' ,encryption by password ='@$trongPa$$word@')
Go

Make sure to keep the Certificate and Private key at safe place. Without Certificate and Private Key, database recovery will be impossible.