SQL Server 2008 - Transparent Data Encryption undecrypted access

Question

My question is a duplicate of - http://stackoverflow.com/questions/2230189/oracle-transparent-data-encryption-undecrypted-access - but for SQL Server 2008.

---

Can I set up a SQL Server 2008 database in a way that all of the following statements are true?

a) certain columns, potentially all columns are encrypted, so that direct file access to the database file wouldn't allow an attacker to retrieve any records

b) the encrypted columns are transparently decrypted for authorized user, where authorization happens e.g. by having a certain role or privilege

c) an admin who has suiteable privileges for doing 'normal' admin tasks (tuning, creating/droping schema objects, restarting database, selecting from data dictionary) can select the tables but will see only encrypted data in the encrypted columns.

If this is possible, how do I do it. If it is not possible, what are the options I have to at least get 'close' to these requirements?

I know a) is possible but I'm not sure about b) and c).

Thanks.

Answer 1

No, because you cannot perform Transparent Data Encryption on selected columns only:

Transparent data encryption (TDE) performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an EKM module. TDE protects data "at rest", meaning the data and log files. It provides the ability to comply with many laws, regulations, and guidelines established in various industries. This enables software developers to encrypt data by using AES and 3DES encryption algorithms without changing existing applications. Ref.

Here are some things you should consider about TDE (courtesy of Brad McGehee's blog):

• TDE does not protect data in memory, so sensitive data can be seen by anyone who has DBO rights to a database, or SA rights to the SQL Server instance. In other words, TDE cannot prevent DBAs from viewing any data they want to see.

• TDE is not granular. Then entire database in encrypted.

• TDE does not protect communications between client applications and SQL Server, so other encryption methods must be used to protect data flowing over the network.

• FILESTREAM data is not encrypted.

• When any one database on a SQL Server instance has TDE turned on, then the tempdb database is automatically encrypted, which can contribute to poor performance for both encrypted and non-encrypted databases running on the same instance.

• Although fewer resources are required to implement TDE than column-level encryption, it still incurs some overhead, which may prevent it from being used on SQL Servers that are experiencing CPU bottlenecks.

• Databases encrypted with TDE can’t take advantage of SQL Server 2008’s new backup compression. If you want to take advantage of both backup compression and encryption, you will have to use a third-party application, such as SQL Backup, which allows you to perform both of these tasks without penalty.

This might be of interest: How to: Encrypt a Column of Data