I've been editing an encryption book, and one of the things stressed in the book is the need to back up and safely store copies of the encryption keys. That makes sense, if there's an error, you need to then ensure that you can recover the data, which means the keys.
But where do you store the keys?
I've struggled with this question before with backups. Years ago we bought Litespeed to perhaps backups at JD Edwards. We had the chance to not only compress the backups, which was our main concern, but also encrypt the data. At first it seemed like a good idea, but with mandates to rotate all our administrative keys every 30 days, I declined to implement encryption.
The problem wasn't with assigning passwords, it was with the rotation and management of them. These passwords were essentially the keys to our data, and ensuring that we could restore a backup from anytime during the last year meant that we would need to not only track the keys, but somehow ensure they were available with our backups for DR situations. We didn't want to store them with the backups, and we didn't want them to be in something as low tech as an envelope in the tape box.
Without a good solution for management of backups and the corresponding keys, we never implemented encryption. Whether that was a good or bad thing I'm not sure, but I remember this being a problem that wasn't easily solved.
If you have many keys, all for different types of access, how do you keep them safe? How do you make them available for DR situations if you have to bring data back from a completely different system, and still protect the keys in case the media is lost? Many highly secure systems recommend keeping the keys separate from the actual data, something I'm not sure many IT departments are ready to do.
SQL Server makes a lot of it's encryption easy, storing the keys in the database, available with the backup, but there are a couple of important keys, like the Service Master Key (SMK) and the Database Master Key (DMK), that you really should have a back up of. Those backups will have passwords as well, and with the need to rotate your passwords, how do you keep them safe?
I don't have a great solution, but I'm open to suggestions.
Steve Jones
The Voice of the DBA Podcasts
The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there.
You can also follow Steve Jones on Twitter:
or now on iTunes!
- Windows Media Podcast - 26MB WMV
- iPod Video Podcast - 25.1MB MP4
- MP3 Audio Podcast - 5.1MB
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.