A must read for those looking to protect their data
The whole point of encrypting data at rest is to protect the database if physical files or backups are lost. In the Books Online (BOL) page for Transparent Data Encryption (TDE), it notes that “…, in a scenario where the physical media (such as drives or backup tapes) are stolen, a malicious party can just restore or attach the database and browse the data. One solution is to encrypt the sensitive data in the database and protect the keys that are used to encrypt the data with a certificate. This prevents anyone without the keys from using the data…”
That’s what TDE is designed for, but the machines that are most likely to be stolen, laptops that contain Express Edition instances, can’t implement TDE. Why not? It’s an “Enterprise only feature”. Why? I assume this is a sales technique to force those companies required to implement data at rest protection will pay more for their instances, but in reality this results in less security for lots of SQL Server applications.
Recently a healthcare organization was in the midst of performing an encryption rollout to laptops and an unencrypted desktop was stolen with sensitive data on it. I know that TDE wouldn’t prevent this, but how many machines lose data that is unencrypted? How many applications built with Visual Studio store data, potentially sensitive data, on a local Express instance? I know that people can encrypt their entire disk (and they should), but what about their backups? What about copying a file to another machine? There are no built in protections, when there easily could be.
I can understand partitioning, Resource Governor, and a few other items being Enterprise only, but a security feature? That strikes me as a poor decision all around.
Steve Jones
The Voice of the DBA Podcasts
- Watch the Windows Media Podcast – 18.1MB WMV
- Watch the iPod Video Podcast – 13.9MB MP4
- Watch the MP3 Audio Podcast – 3.3MB MP3
Voice of the DBA 
I would argue that BitLocker is a better solution for laptops. Losing SQL data on a laptop is only a part of the story. If someone is handling sensitive data on a laptop they probably have it in Excel files on their desktop and emails in their Outlook inbox. In my opinion you are better off just encrypting the whole machine.
LikeLike
Bitlocker is great, as is all FDE and should be used. Not sure that TDE precludes Bitlocker or vice versa.
LikeLike
I would argue the examples presented of the laptop and desktop are not the best ones as BitLocker or another FDE is the right solution there and TDE doesn’t come into play.
However, your point is well made. In this era of data breaches right and left, it just doesn’t make sense to force one to buy EE to get TDE. Microsoft could actually turn this on its head and market the fact that all editions support TDE showing they get security while others don’t.
LikeLike