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

tde1

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.

tde2

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

tde3

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

tde4

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

tde5

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:

tde6

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

Hope this helps!

Advertisements
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:
    http://www.netlib.com/tde-sql-encryptionizer.asp

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s