SQL Server 2008 – Transparent Data Encryption

Does it sound too good to be true: transparent data encryption? Well, it kind of is and kind of isn’t. Let me explain. Transparent means that the application developer doesn’t do anything (and if you have a third party app in which you can’t do anything anyway, then this is even more important). Once enabled, the data is seemlessly protected.

But, protected from what? That’s the part that needs a bit of explaining…

We all talk about the importance of physical security (I hope this doesn’t sound new)? We all talk about the importance of protecting the physical media to which you’ve backed up your database(s)? We all talk about protecting the server/machine from physical access and of course, protecting the mdf/ldf files from unauthorized access if we copy them to other machines for attach. And, we all know that with all releases of SQL Server, up to and including SQL Server 2005 (with one exception – details coming up), that our databases are susceptible to unauthorized access if someone gains access to the physical files (whether the actual data files and/or the backup). The one exception is if data within the database has been encrypted using SQL Server 2005 column level encryption. As long as your database master key (created to allow encryption within the database) is strong and protected, then the data that is encrypted with it is also protected (you’re only as good as your key protection mechanism). However, any data that is not encrypted is fair game to any system administrator who solely attaches the database or restores it. This is not good. However, it is reality in general. Physical security is incredibly important.

Now, having said all of that, SQL Server 2008 is going to change the landscape a bit. SQL Server 2008 is going to offer a way of protecting this “resting” data – transparently. Basically, once this is turned on, your data is protected from an unathorized attach OR when backed up, unauthorized restore.

This feature is not in the current CTP but I hope to see it in the next publicly available CTP (which will hopefully be out soon). So, when we can finally see it, I’ll give you more details such as how to implement it, whether or not it negatively/positively impacts other features and the impact on performance.

Thanks for reading!

One thought on “SQL Server 2008 – Transparent Data Encryption

  1. Hi Kim, I’ve been lurking for a while and thought I’d de-lurk and make a comment…

    It may be interesting to some folks to know that there was a million dollar lawsuit against Microsoft regarding whether or not personal data (ie email address) stored on ms.com was secure. And, because database backups were not /actually/ encrypted (one needs a password to restore them, but they can still be groped by a clever tool), the lawsuit was lost. I had the honor of being involved in supplying information regarding the backups, through sheer coincidence of being in the right place at the right time.

    Although I do not know anything about the person who won the lawsuit, I believe they have done a great favor to the SQL Server community by making backups (or at least restores) more secure. This seems especially important in this age of application hosting.

    I would be interested in knowing whether the backup can be read by any third party tool without the password. Anyone know anything about that?

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Wow! Wow! Wow! THANK YOU!

I announced my retirement from SQL/tech here and your comments on my blog, on LinkedIn, and on Facebook were overwhelming and humbling! I’m so touched


Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.