SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Encrypting Data

By Steve Jones,

"Encrypting data is not the hard part"

That's a great quote from this article on data encryption and I think it's a great one to keep in mind. The actual process of encrypting the bits is probably the easiest part, which is why I think encryption is not that widely deployed.

Key management is a hard process to understand, much less implement. I've always been more concerned about that part, especially as we move to complex, multi-part keys such as public encryption. Managing the keys, ensuring they are distributed to the correct person, they can be revoked, they can be replaced if lost is a major issue.

With regards to the new transparent data encryption (TDE) feature in SQL Server 2008, it encrypts your data on disk, decrypting it as it goes into memory, but providing some protection for laptops that carry data as well as your physical servers. However there was this great quote on a blog regarding the certificates used for security: One major warning on that. Lose the certificate, say goodbye to your database..

Now that's a scary thought. You need to keep this certificate somewhere in case of DR. It's on your primary server, but if you need to restore this database somewhere else, even from backup, than you need a copy of the certificate or you can't perform the restore.

However if you keep the certificates or the certificate backups with your backup files (say on the same tape), then you've nullified a lot of the security that exists there. And I'm sure that lots of people will store the certificates with the backup since that way they always have it for the restore.

That does bring up the whole key management issue with disaster recovery. Where do you keep the backups of the keys? Do you have separate tapes? Are they stored in separate locations? They should be. But then how do you ensure the right key matches the right backup? I'd like to think that you wouldn't use the same key forever to encrypt something and it would change over time.

Over any long period of time you'd have the same key, but what about the week you change keys? I'm sure that's when you'll have to perform a restore? And what about if you have 20, 30, or more databases? Each has it's own key, right?

My head hurts just considering the possibilities. I think I'll file this one under "more work to be done."

Steve Jones

The Voice of the DBA Podcasts

Everyday Jones

The podcast feeds are now available at sqlservercentral.podshow.com to get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.

Overall RSS Feed: or now on iTunes!

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.

Total article views: 953 | Views in the last 30 days: 2
Related Articles

SMKs, DMKs, Certificates for TDE and Encrypted Backups

This article details SMKs, DMKs and certificates in SQL Server as they relate to Transparent Data En...


Encryption, Certificate

Encryption, Certificate management in database


Creating certificates




A new video setup is on the way!!!! Actually I'll do a couple podcasts on podcasting over the hol...


SQL Server Podcasts

Great news, I have decided to do some podcasts on the fundamentals of SQL Server, my aim is to help....