Database encryption has been around for a long time but only through third party implementations until the introduction of SQL Server 2005.
SQL Server 2005 offers native database encryption in the form of certificates, asymmetric keys, and symmetric keys. All three options provide cell level encryption which means you can choose to encrypt individual columns. Where these encryption methods differ is in their encryption strength and performance. Certificates and asymmetric keys will provide you with the highest level of encryption strength at the cost of performance. On the other hand, symmetric key encryption offers better encryption performance than the other two options and is the recommended option when performance outweighs security.
Implementing any of these encryption methods will require modifications to your application and the underlying database structure. There are encryption functions associated with the particular type of encryption you choose to use. These encryption functions will need to be called within your code when inputting data that is to be encrypted and when decrypting data that has been retrieved from your database. In addition to the application code changes, the columns used to store encrypted data will need to be modified to use a variable length data type to hold the encrypted version of the data.
Indexing and referential integrity can also be impacted depending on your database design. For example, you have a column that stores salary information and an index exists for the salary column. Being that encryption functions are non-deterministic, a new value is generated every time. For this reason, your indexes are rendered useless. Where you once may have seen an index seek you now have a table scan. The non-deterministic nature of encryption also affects the use of foreign keys on encrypted data.
SQL Server 2008 brings a new database encryption method to the table called Transparent Data Encryption (TDE). The main advantages that TDE offers over the other encryption methods is that it requires no changes to your database design or application code and offers good performance. Due to the way that TDE encrypts data, your indexes and referential integrity are left intact. TDE encrypts the entire database at rest by encrypting the data files as opposed to the data itself. Encrypted database backups and snapshots are a nice side effect of performing encryption at the database file level. There is one caveat surrounding the encryption of the transaction log. Data that has already been written to the transaction log prior to enabling TDE will not be encrypted but data written to the transaction log after TDE has been enabled will be encrypted. TDE does come at a cost as it is only available in the Enterprise edition of SQL Server.
With all of these encryption methods, data in transit is not encrypted. Decrypted data is transmitted to clients in plaintext. SSL is recommended to protect this channel of communication. In addition, extreme care should be taken to ensure that all encryption keys and/or certificates are backed up and stored in a safe location. If backups of the encryption keys and/or certificates are not available in the event of a disaster recovery scenario, the encrypted data will be lost.
Deciding on the type of encryption that you will implement in your environment will depend on many factors. Your particular circumstance may require a combination of TDE with one or more of the cell level encryption method or just a single encryption method. Performance, cost, and level of security will ultimately be your primary deciding factors.
