Implementing Transparent Data Encryption Part-1

Transparent data encryption (TDE) is available only in SQL Server 2008 R2 Enterprise Edition. This mechanism is used to encrypt/decrypt data and log files in real time. The set up involves:

1) Creating a master key

2) Creating a database certificate

3) Creating a database encryption key

4) Enable encryption on the database

Master key (a symmetric key) is created as shown below


Querying the sys.symmetric_keys would retrieve all the symmetric keys in that database instance. In the below screenshot it shows us the master key has been created.


The next step after creating the master key would be to create the database certificate which is as shown below:


The certificate information can be retrieved by querying the sys.certificates which is as shown in the below screenshot:


Now the next step would be to create a database encryption key by the server certificate as shown below:


The database encryption key is created on the database that we are trying to encrypt and in this case this is AdventureWorks2008R2 database. Encryption on the database is enabled as shown below:


In my next post I will discuss how TDE  encryption works and how to perform the decryption.

Hope this helps!

This entry was posted in SQL Server DBA Stuff and tagged , , , , , , . Bookmark the permalink.

2 Responses to Implementing Transparent Data Encryption Part-1

  1. クロス ボールペン

  2. Neil Weicher says:

    I hope it would not be considered off-topic to itemize the differences between built-in SQL Server Encryption and NetLib Encryptionizer:

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s