Transparent Data Encryption in SQL Server 2008

As a Database Administrator, security is one of the most important areas to consider when it comes to protecting the databases that you support. We use various mechanisms and technologies to secure our data and databases such as firewalls, certificates, and data encryption. Having said that although we have secured our environment, questions will always be raisesd regarding database security. Although we have protected our databases, what would happen if someone steals the mdf file or if someone steals the backup file. Although there are few ways to control this senario using third-party solutions up until SQL Server 2008 there has been no native way to handle this problem. SQL Server 2008 introduces a new feature that protects the database called Transparent Data Encryption - TDE which provides protection to the entire database. I recently tested TDE with SQL Server 2008 February CTP, and the result of the testing made me write this article. This article contains:



*What is Transparent Data Encryption?
*Implementation of TDE
*Is my database secure now, as it says?
*What needs to be considered before enabling TDE?
*What is the impact when TDE is enabled?
*What is Transparent Data Encryption?

Microsoft SQL Server 2008 introduces another level of encryption, Transparent Data Encryption. TDE is full database level encryption that is not limited to columns and rows but protects both the data files and log files. The implementation of TDE on a database is comparatively very simple and transparent to the applications that connects to the selected database. It does not requires any changes to the existing applications. The protection is applied on the data files and log files as well as the backup files. Once TDE is enabled on a database, the restoring of a backup to another SQL Server instance or attaching data files to another SQL Server instance will not be permitted until the certificate that was used to secure the database encryption key (DEK) is available.

The encryption feature of TDE is applied at the page level. Once enabled, pages are encrypted before they are written to the disk and decrypted before they are read into the memory. It is important to remember that the communication channel between SQL Server and the client application will not be secured and encrypted via TDE.

Transparent Data Encryption uses a Database Encryption Key (DEK) for encrypting the database that is stored in the database boot record. The DEK is secured by a certificate that is stored in the master database. Optionally, the DEK can be secured by an asymmetric key that resides in a Hardware Security Module (HSM) with the support of Extensible Key Management (EKM). The private key of the certificate is encrypted with the database master key that is a symmetric key, that is usually protected with a strong password. Note that although the certificate can be secured by a password, TDE requires that the certificate is secured by the database master key. The database master key is protected by the service master key that is protected by the Data Protection API.