April 29, 2010 at 10:16 am
I'm trying to get a solid recovery plan in place for a DB that will have encrypted columns. The DB will be on SQL Server 2005 (SP3) and the proposed plan calls for utilizing a Database Master Key to protect a Self Signed Certificate, which in turn protects the Symmetric Keys that will encrypt the data.
My concern lies in the possibility of having to restore this DB from backup onto (A) same server instance/different DB name and (B) different server instance.
I've seen lots of info regarding creating, backing up and restoring keys & certificates respectively but nothing which explicitly describes my situation. They usually describe restoring the Database Master Key but don't mention my Certificate and Symmetric Keys. Any idea if this means that once the Database Master Key is restored, the Certificate and Symmetric Keys are automatically restored and the business of encrypting/decrypting can resume immediately? Do scenario (A) and (B) require different solutions?
I appreciate your assistance or if you can point me in the right direction.
April 29, 2010 at 11:49 am
Backing up the SMK, DMK and certificates isn't required for a backup/restore situation.
A) No additional steps required after restoring the database.
B) Allow the SMK to open the DMK after the restore.
USE DB1
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password_when_created'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
If you're looking for more information see Laurentiu Cristofor's blog.
April 30, 2010 at 1:41 pm
Thanks for the reply. This seems much easier than I expected.
So, once I restore my DB (DB1) from backup on the same or different server, I run the script (you provided) with the password used to create the original DMK and that's it?
My certificate and symmetric keys can be used as before? I don't need to restore the certificate from backup or anything else?
April 30, 2010 at 2:54 pm
If your asymmetric keys were created from the database master key (they should be unless you specified otherwise), once you regain access to the database master key, yes, you should be good. I assume symmetric keys were encrypted with the asymmetric keys, so they would be good, too.
K. Brian Kelley
@kbriankelley
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply