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

Secure Storage

By Steve Jones,

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

Everyday Jones

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:

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: 99 | Views in the last 30 days: 1
Related Articles

The Backup Passwords

Today Steve Jones asks how you handle those passwords across time for backups in this week's Friday ...


Database Backup Encryption – SQLServer 2014 CTP2

SQL Server 2014 CTP2(All new,shiny shiny !) availability was announced yesterday at #SQLPASS Summit....


Backup with password

Backup with password


SQL Server 2017 Encrypted Backups And Compression

Let’s work through some code to do an encrypted backup. This feature is available to you if you are ...


Using Encrypted Backups in SQL Server 2014

This article details encrypted backups and how they are implemented in SQL Server 2014.