Transparent Database Encryption Tool

• Download TransparentDateEncryption - 21.1 KB

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

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.

• Now select the Database Name to Encrypt.

• 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.

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

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.

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.