While I think that the one click database encryption that Microsoft has built into SQL Server 2008 is a good idea, but I’m not sure how useful it actually is. It’s touted as giving you data encryption of the entire database without any code change.
What this actually means is that if someone stops the SQL Server and copies the data files to another server and attaches the files to that SQL Server the files won’t be of any use. This however isn’t the common way that data is stolen from a SQL Server. A much more common method is someone logs into the database using a perfectly legit account and exports the table or tables which contain the useful information to Excel, Access, csv, etc. This method of database encryption does nothing to protect against this as if you query the table directly you are displayed the data in clear text.
All this database encryption does (as it appears to me) is that it protects the data at rest (ie. when the SQL Server service is stopped) and does nothing else to protect the data. If all you are looking for is a compliance check off saying that you data is encrypted for SOX, HIPAA, the California Online Privacy Protection Act of 2003, etc then this will probably do the trick. If you want actual data encryption where the data is not viewable when you query the table unless you know the key to unlock the data this isn’t it.
If you do want that quick and easy encryption for a compliance check off be sure to check your data storage requirements and IO requirements. Encrypted data is typically larger than unencrypted data and you may need to increase your allocated storage or IO requirements in order to have the encryption completed. Also remember that the higher level of encryption you use the more CPU time is required to decrypt the data.
Feel free to share your opinions below. I’m interested to here what others have to say on the topic.
Denny
2 Responses
Hi, Denny. The other reason TDE is useful is in SAN environments. The SAN administrator can take a snapshot of an array at any time without stopping the application. Granted, the snapshot will be dirty, but it’s usually recoverable, and even if it isn’t, they can retake the snapshot until they get a decent copy. Then, they can mount that snapshot on another server and they’ve got a working copy of the company’s databases, bypassing security. TDE is great for that.
SAN-to-SAN replication makes this an even bigger issue: if you’re doing SAN-based replication to your DR site, and especially if your DR site is a colo, someone else can take the snapshot at your DR site and pull the copied drives.
Brent,
Keeping in mind that I haven’t had time to really beat on TDE that much. But if your SAN admin took a snap of the LUN that the encrypted database was on, they probably also have a snap of the master database, and all the various keys which are stored in the master database, and the folder that the master database is sitting in. They can probably just attach that database to another machine, fire it up with the old master database and have the system decrypt the database as all the keys are there.
(Again I haven’t played with it to try this yet.)
If as a DBA you can’t trust your SAN admin, you’ve got bigger problems than the SAN admin swiping the database.
Denny