Click here to Skip to main content
15,860,972 members
Articles / Programming Languages / C# 4.0
Tip/Trick

Transparent Database Encryption Tool

Rate me:
Please Sign up or sign in to vote.
5.00/5 (3 votes)
25 May 2012CPOL2 min read 26.1K   823   9   5
This tool is used for Encryption of Database in SQL Server 2008 Enterprise Edition

Introduction

Recently I had a requirement of implementing a security guideline which stated "Encryption At Rest". After much of goggling and reading articles I was able to find a new feature called "Transparent Database Encryption " in SQL Server 2008. This feature basically allows encryption of database at rest .

Transparent Data Encryption Tool is created to protect data by encrypting the physical files of the database, rather than the data itself. Its main purpose is to prevent unauthorized access to the data by restoring the files to another server. These physical files include the database file (.mdf), the transaction log file (.ldf) and the backup files (.bak).

However Transparent Database Encryption [TDE] is a 6 step process as below:

  1. Creating a Service Master Key.
  2. Creating a Database Master Key.
  3. Creating a Certificate.
  4. Creating a Back Up of the Certificate.
  5. Creating a Database Encryption Key.
  6. Turn Encryption On.

Guys, don't panic after viewing the above steps. Well my TDE Tool will perform all these internally under one stored procedure with just a click on "Encrypt Database" button.

Basic Architecture of Transparent Database Encryption

Image 1

Transparent Database Encryption Tool

Below snap-shot shows the TDE Tool. Initially when this application runs, there is a minute delay before popping up this below screen. Technically, this is because on form load i m getting the local SQL Server instance using the method GetDataSources() method of "SqlDataSourceEnumerator" class.

But trust me this TDE tool wont run a sick dog !!!

  • Select Server Name or key-in the Server Name.

Image 2

  • Now select the Database Name to Encrypt.

Image 3

  • Enter the Master Key Password and Certificate Password. Enter the password according to password policy. The back up will of the certificate will be created in the "Install" folder under the application's root directory.

Image 4

Lastly, when user clicks on the Encrypt Database button, the selected database gets encrypted and a message will be displayed as below:

Image 5

NOTE:

Technically, On click of the "Encrypt Database " button , a SQL script named "TDEQuery.sql" present under the application root folder with name "Stored Procedure" gets executed. This sql script will create a stored procedure named "usp_DatabaseEncryption" under the local sql server -> master database.

Image 6

Constraints

  1. There can be only one master key password . so only for first database encryption the master key password field and the certificate backup password field in the form is enabled
  2. Back up of master key and certificate is also created only once.
  3. Works only in SQL Server 2008 R2 Enterprise Edition.
  4. Stored procedure is also created internally only ones under the master database of local server.

Using the Code

One stored procedure named "usp_DatabaseEncryption" complete the whole TDE process.

Conclusion

Hope you have enjoyed reading this article and the content in the article has helped in your assignments. Any suggestions or feedback are welcome.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
India India
I am a MCP Certified Developer with 3 years experience in .net Technology. I love writing technical articles whenever i find leisure time. According to me, "Giving a deadly desired storm to your brain might yield to an 'invention." Think about it Smile | :)

Comments and Discussions

 
Questionexpress edition Pin
Sharath279011-Sep-12 21:45
Sharath279011-Sep-12 21:45 
AnswerRe: express edition Pin
Tauseef M25-Sep-12 0:14
Tauseef M25-Sep-12 0:14 
GeneralTransparent Database Encryption Tool Pin
Hemant Thaker15-Apr-12 21:10
Hemant Thaker15-Apr-12 21:10 
This is Awesome !!!...

Good work.
By:
Hemant Thaker

QuestionNice Article Pin
tanveersmk10-Apr-12 21:12
tanveersmk10-Apr-12 21:12 
QuestionWhere is the code?! Pin
Blue_Boy10-Apr-12 4:29
Blue_Boy10-Apr-12 4:29 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.