Feb 25, 2017

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)


Create Master Key

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

Create Certificate

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

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
Create Database Encryption Key
with algorithm = AES_256
Encryption By Server Certificate TDE_CERT;

Finally Enable TDE for the database

use master
Alter database myDB
Set encryption on

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

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
backup certificate TDE_CERT
To FIle='C:\encryption\TDE_CERT.cer'
with private key (file='C:\encryption\pkey.key' ,encryption by password ='@$trongPa$$word@')

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


  1. Thank you Sir for taking effort to make this clear instructions as well as video.

  2. You ought to be a part of a contest for one of the highest quality websites on the internet. I am going to recommend this web site! onsite mobile repair bangalore A fascinating discussion is worth comment. There's no doubt that that you need to publish more on this subject matter, it may not be a taboo subject but usually folks don't speak about such subjects. To the next! All the best!! asus display repair bangalore Hi there! I simply would like to offer you a huge thumbs up for your great information you've got here on this post. I am coming back to your blog for more soon. huawei display repair bangalore

  3. Spot on with this write-up, I honestly believe this site needs a great deal more attention. I’ll probably be back again to read through more, thanks for the info! online laptop repair center bangalore You're so awesome! I don't suppose I've read through anything like that before. So wonderful to discover someone with a few original thoughts on this issue. Really.. thanks for starting this up. This web site is something that is required on the internet, someone with a little originality! dell repair center bangalore

  4. I really like reading through a post that will make people think. Also, many thanks for permitting me to comment! macbook repair center bangalore This is the right webpage for anybody who wishes to understand this topic. You realize a whole lot its almost hard to argue with you (not that I actually would want to…HaHa). You definitely put a fresh spin on a topic that's been discussed for ages. Excellent stuff, just great! acer repair center bangalore

  5. The company then launches new products based on the demands and needs of the customers as determined through data mining, a method of manipulating the raw data. artificial intelligence certification


Optimizing Indexes with Execution Plans

Contact Me


Email *

Message *