Transparent Data Encryption (TDE) sounded like a really cool feature when I first heard of it. Encrypt your database without any changes to your application. It seemed too good to be true.
And it might be.
It's another feature that is well marketed, but really addresses only certain issues. In this case it addresses the issues of getting to your database on disk, and in some cases, preventing access to the data even if you have physical access to the server.
That's pretty cool. Someone could have access to your server, say in some data center, and if you've secured SQL Server, even if they were a local administrator, you potentially could have your data safe from access. The physical files are encrypted, so they can't be read.
Or can they?
As I dig through various documentation, trying to understand how it works for the book I'm tech editing, it seems that most things are covered. The log is encrypted, tempdb encrypted, mirrored databases encrypted, it's a good system.
However I did find one potential place where it could be bypassed. Now this wouldn't be for your everyday bad guy. We'd need a smart hacker, but I bet there's one out there.
First, the data is decrypted when it hits the buffer pool, so in memory it's decrypted, allowing indexes and other normal SQL Server operations to work with the data. However this means there are two places where this data can get to disk.
- Paged out as part of the normal memory paging to disk in the pagefile process.
- Written as part of a crash dump.
I don't know how big an issue this is, but I think a smart hacker could cause either of these things easier than poking through memory and reading what's going on. And if they could do this after somehow ensuring critical data was in memory, so through some normal interface to your application, they could potentially get to the data.
It's not a big hole, but it's a hole. I'm not sure how you get around something like this until you get a whole SQL Server OS that's a specialized version of Windows and encrypts everything.
In the meantime, I wouldn't worry about these holes. I'd worry more about keeping your keys backed up and safe!
