SQL Server Data Encryption

With the introduction of transparent data encryption (TDE) in SQL Server 2008, users now have the choice between :

* cell-level encryption as in SQL Server 2005

* full database-level encryption by using TDE, or

* the file-level encryption options provided by Windows.

Here's a good MIcrosoft summary: http://msdn.microsoft.com/en-us/library/cc278098(SQL.100).aspx#_Toc189384682

Cell-level encryption

QL Server offers encryption at the cell level. Cell-level encryption was introduced in Microsoft SQL Server 2005 and is still fully supported. Cell-level encryption is implemented as a series of built-ins and a key management hierarchy. Using this encryption is a manual process that requires a re-architecture of the application to call the encryption and decryption functions. In addition, the schema must be modified to store the data as varbinary and then re-cast back to the appropriate data type when read. The traditional limitations of encryption are inherent in this method as none of the automatic query optimization techniques can be used.

TDE

TDE is the optimal choice for bulk encryption to meet regulatory compliance or corporate data security standards. TDE works at the file level, which is similar to two Windows® features: the Encrypting File System (EFS) and BitLocker™ Drive Encryption, the new volume-level encryption introduced in Windows Vista®, both of which also encrypt data on the hard drive. TDE does not replace cell-level encryption, EFS, or BitLocker. See more about TDE at Transparent Data Encryption.

Windows File Encyption (EFS and bitlocker)

BitLocker and EFS (and to a much more limited degree, cell-level encryption) can be used to protect system databases (master, model, resource, and msdb), which cannot currently be encrypted by TDE. EFS is also more generally available as TDE is restricted to SQL Server 2008 Enterprise Edition or SQL Server 2008 Developer Edition and BitLocker is available only with Windows Vista Enterprise, Windows Vista Ultimate, or Windows Server 2008.

The disadvantages to using EFS over TDE, are primarily in performance and administration. EFS is not designed for high-concurrency random access (it does not support prefetch or asynchronous I/O). Therefore, I/O operations may become bottlenecked and serialized. While this has a minimal impact in a normal user scenario, it has been a cause for concern in database usage.