Restore Master Key

Do we need to know the database master key password inorder to restore the same from a Master Key backup ?

What if the scenario is like, I have Master Key backup file but do not have the password specified at the time of creation ?

Any help is appreciated.

satya

You need the password. If you don't have it, you cannot use the backup.

Thanks
Laurentiu

|||

Thanks

I needed just a clarification. We are actually trying to develop an implementation strategy for encrypting data in SQL 2005.

satya

|||

Quick Follow-up on this. This is what exactly I am trying to test.

1. Create Master Key with password

2. Create certificate (encrypted by master key)

3. Create symmetric key (encrypted by certificate)

4. insert encrypted data using encryptbykey built-in

5. Test decryption using built-in decryptbykeyautocert

Once this all is working fine, do these backups

1. Backup Master Key

2. Backup Certificate as .CER file and private key as .PVK file.

Drop the these following security objects

1. Drop symmetric key

2. Drop certificate

3. Drop master key

Restore keys from backups

1. Restore master key

2. Create certificate from backups

3. Create symmetric key

I receive no errors while performing these T-SQL statements, but I am no longer able to view previously encrypted data. Decrypted values showing as NULL

Do you think, I am trying to do something wrong here ?

thanks

satya

|||

To recreate the same symmetric key that you used for encryption, you need to use the same algorithm, identity_value, and key_source in the CREATE SYMMETRIC KEY statement. Also take a look at: http://blogs.msdn.com/lcris/archive/2005/10/14/481434.aspx, the last pargraph is relevant to this.

Thanks
Laurentiu

|||

Works like a charm !! Thanks.

Only concern, I have is that we need to remember little more things for recover process to work perfectly :)

Is there any white paper yet on "Recocering Encrypted databases".

thanks

satya

|||

You could store those pieces of information in a separate table encrypted by a certificate.

What exactly would you like covered under the title "recovering encrypted databases"?

Thanks
Laurentiu

|||

Most of it I can find it in your blogs. May be I am looking for a consolidated one.

Key Management - Best Practices in symmetric key encryption, asymmetric encryption (or using certificates) and hybrid approach (Symmetric Key getting encrypted by certificates)|||

For the FORCE option, see http://blogs.msdn.com/lcris/archive/2006/04/10/572678.aspx. It's not an option to bypass the password protection.

I'll transmit your suggestions to our documentation team, so they can look into adding new articles on these topics or update existing articles and add more information.

Thanks
Laurentiu