GilaMonster (3/4/2012)
kilkenny (2/27/2012)
My first inclination was to turn on TDE, but after reading several articles, I'm worried about losing performance. If we choose to encrypt at the column-level, it will require us to redesign some of the applications using these databases, which will probably be cost-prohibitive.TDE and column-level encryption are two completely different things for completely different purposes.
TDE protects the data at rest. It encrypts the database file on disk and the backups. It means that if a backup is stolen or someone manages to copy the data files, they cannot restore or attach the database without having the certificates, however anyone connecting to the database sees only unencrypted data.
Column encryption protects data (specific columns) from unauthorised queries. If someone queries the table without first having opened the key (which required specific database-level permissions), they see encrypted data only. Any sysadmin has permission automatically on the keys and hence can always decrypt data. For that reason this is no protection against someone stealing the backup or data file as they would have sysadmin permission on their own SQL instances and hence could decrypt the data.
Consider what you are protecting against and pick the appropriate method. Using the wrong one to protect against a thread is worse than useless.
GilaMonster mentions that someone can steal a backup and read the encrypted data if they have sysadmin access. I assume access to the encrypted data is only possible if they have sysadmin access to the source data, correct? Wouldn't they need to backup and restore they keys from the source to the destination server to have access? Or can they steal/borrow a backup from a SQL Server they don't have access to and restore on a server where they are sysadmin and read the encrypted data? If yes, what is the process?
Thanks