posted on Thursday, May 17, 2007 1:44 PM by marathonsqlguy

Considering Encrypted Data Requirements into your Disaster Recovery Plan

I have the biggest problem trying to get a handle on data encryption. So, when it came time to test a log shipping solution I'd implemented for one of our websites the initial testing failed because I hadn't done a couple of basic, but very important steps.

First, and this is really important, back up the service master key and database master key for each database which uses SQL Server encryption. It's not hard - here are the steps:

use master
GO
BACKUP SERVICE MASTER KEY FROM FILE = 'C:\MyDirectory\ServerSMK.key' ENCRYPTION BY PASSWORD = 'UD58ss6r'
GO
use MyDatabase
GO
BACKUP MASTER KEY FROM FILE = 'C:\MyDirectory\MyDatabaseMK.key' 
    ENCRYPTION BY PASSWORD = 'UD58ss6r'
GO

Copy these files to somewhere safe, where you know you'll be able to find them should you find you need to recover the data in these databases.

Now, should you find that you need to recover the database, you can restore the database from backup, then issue the following commands to restore full access to the encrypted data:

use master
GO
RESTORE SERVICE MASTER KEY FROM FILE = 'C:\MyDirectory\ServerSMK.key' DECRYPTION BY PASSWORD = 'UD58ss6r'
GO
use MyDatabase
GO
RESTORE MASTER KEY FROM FILE = 'C:\MyDirectory\MyDatabaseMK.key' 
    DECRYPTION BY PASSWORD = 'UD58ss6r'
    ENCRYPTION BY PASSWORD = 'ccH4QvQCp8Ry6nYSsVxZ5oU'
	FORCE
GO

As I mentioned, I learned this while testing a site where we've implemented log shipping to another city's data center in the event of a disaster in our Cleveland data center, and after restoring the service master key and database master key, the application on the alternate site worked as it does here in Cleveland. Of course, disaster recovery without testing doesn't amount to much, so it was a good exercise for many reasons.

I'm still not real clear on all the encryption technology, but I'm much more comfortable with it than I was a week ago.

Allen

Comments

# re: Considering Encrypted Data Requirements into your Disaster Recovery Plan @ Thursday, May 17, 2007 3:44 PM

Hi Allen,

You might want to look at the DROP ENCRYPTION BY SERVICE MASTER KEY option of the ALTER MASTER KEY statement. If the database master key is not encrypted by the service master key, you should not have to worry about backing either of them up separately from the database, in a DR scenario (I believe -- 95% certain of that <g>).

amachanic

# re: Considering Encrypted Data Requirements into your Disaster Recovery Plan @ Friday, May 18, 2007 9:06 AM

Thanks, Adam, I'll take a look at that. It's not a problem to encrypt with the Service Master Key in this case, as the DR site was set up specifically for this purpose, but I can envision a problem if I needed to support databases from multiple servers in one DR server.

See you in Orlando, I hope.

Allen

marathonsqlguy