Skip to main content

SQL Server 2008 Transparent Data Encryption 

Posted By:  Joey Snow 
Publish Date: 2/28/2008

In an IT Infrastructure you can take steps to secure the data on the ‘wire’ via IP Sec, setup firewalls and putting servers behind locked doors. But what about the data on those backup tapes that you just handed to the offsite storage company? What about the one-off backup you did before the last big upgrade that you have sitting on your desk? How can we protect this data?


SQL Server Transparent Data Encryption!


Who’s it for? IT Professionals, DBA’s and Developers.


When does it ship? Transparent Data Encryption is currently available in the SQL Server 2008 CTP5 and will ship with SQL 2008.


What does it do? Transparent data encryption performs real-time I/O encryption and decryption of SQL Server data and log files protecting the data at rest.


But why is that interesting?

More and more IT Professionals are being tasked with ensuring compliance of data security including SQL Server. The cool factor of transparent data encryption is how simple it really is to deploy. Even if your SQL knowledge is limited.

Unlike traditional methods of data encryption that require the client application using the data to decrypt the data before it can be used, transparent data encryption performs all of the encryption and decryption on the database system. This allows for protection of your data without requiring any work from your application developers. This is particularly useful when using third party systems that store data in a SQL Server Database. You get the encryption without impacting the developer of your applications!

Okay you said it was easy, so prove it!

For those IT Professionals who are not fluent in T-SQL, there are only two commands that require T-SQL in order to get transparent data encryption up and running.

· Use the T-SQL code below to create a master key on the database server:


USE master;





· Use the T-SQL code below to create a certificate using the encryption key created above:





Okay that was not too bad. Now we can either use the GUI in SQL Server or another couple of T-SQL statements to get the database encrypted. I will cover the GUI version here since T-SQL methods are covered both by SQL Books Online as well as a great blog post by Andrew Fryer. (

Using the GUI in SQL Server Management Studio: Right Click the database that you want to encrypt, then Select TOOLS -->MANAGE DATABASE ENCRYPTION.

Next you are given the screen that allows you to select the encryption algorithm (I chose AES128 for the sake of this conversation). Next select the radio button next to USE SERVER CERTIFICATE and use the drop down to select the Certificate you created in the T-SQL statement above. Make sure to check the checkbox next to SET DATABASE ENCRYPTION ON and click OK.


You now have a database that is secured with transparent data encryption! Remember that in order to restore a database that is encrypted, you must have access to the certificate or asymmetric key that was used in the encryption. Without the key you cannot restore the database. It’s a good idea to backup the encryption keys and keep them as long as you need to retain your backups.



SQL Server 2008 Product Page