SQL Server Encryption Options

One of the most difficult-to-understand options in SQL Server 2012 is the ability to encrypt data. This is mainly because of all of the different encryption capabilities offered.

Data encryption can be performed by the OS, by SQL Server, or by the application. I’ll help guide you through the different SQL Server encryption options.

Related: SQL Server Encryption

1. Transparent Data Encryption

Transparent Data Encryption (TDE) is the primary SQL Server encryption option. It was first available in SQL Server 2008, and as with the SQL Server 2012 release, it's available only in the SQL Server Enterprise edition, not in the Business Intelligence, Standard, or Express editions. TDE enables you to encrypt an entire database. Backups for databases that use TDE are also encrypted. TDE protects the data at rest, which means that the database’s data and log files are encrypted using the AES and 3DES encryption algorithms. TDE is completely transparent to the application and requires no coding changes to implement. For more information on TDE, check out "Transparent Data Encryption," on MSDN and my SQL Server Pro article "Using Transparent Data Encryption."

2. Column-level Encryption

Column-level encryption (aka cell-level encryption) was introduced in SQL Server 2005 and is available in all editions of SQL Server, including the free SQL Server Express edition. To use cell-level encryption, the schema must be changed to varbinary, then reconverted to the desired data type. This means the application must be changed to support the encryption-decryption operation; in addition, it can affect performance. Encryption of the database occurs at the page level, but when those pages are read to buffer pool, they're decrypted. Data can be encrypted using a passphrase, an asymmetric key, a symmetric key, or a certificate.  The supported algorithms for column-level encryption are AES with 128,196,256 bit keys and 3DES. To learn more about column-level encryption, see the MSDN article "Encrypt a Column of Data."

Related: Protecting the Database With Encryption

3. Encrypting and Decrypting Data with the .NET Framework

Another option for encrypting data stored in SQL Server is to perform the encryption and decryption from within the application. All editions of SQL Server support this style of data encryption. However, unlike TDE, encrypting data within the application requires that you specifically code the application to perform the encryption by calling the encryption and decryption methods. The .NET Framework supports encryption using the System.Security.Cryptography namespace to perform symmetric or asymmetric encryption. You can learn more about .NET-based encryption at "Encrypting and Decrypting Data." Application-level encryption can also be performed by other development frameworks such as Java.

4. Encrypting File Systems

Encrypting File Systems (EFS) is a file-encryption feature introduced in Windows 2000. Windows Server supports EFS for encrypting data at the file and folder level. EFS uses industry-standard encryption algorithms including AES, SHA, ECC, and smart card–based encryption.  Installing a SQL Server database in EFS isn't usually a recommended practice because of the added overhead. EFS isn't optimized for performance, and all I/O is synchronous. If you use EFS, the database files are encrypted under the identity of the account running SQL Server. If you change the account that runs the SQL Server service, you must first decrypt the files using the old account, then re-encrypt them using the new account. You can learn about the performance implications of running SQL Server and EFS at the Microsoft Support web page.

5. BitLocker

BitLocker Drive Encryption is a data protection feature available in Windows Server 2012, Windows 8, Windows 7, and Windows Server 2008 R2. BitLocker works at the volume level, and it protects data when it's at rest by using the AES algorithm. BitLocker doesn't have the same performance concerns associated with EFS. You can learn more about BitLocker at the Microsoft article, "BitLocker Driver Encryption Overview."