Restore Master Key

Restore Master Key

• Janus_42

  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

  
  
  

• Eindhoven

  Thanks

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

  satya

  
  
  

• B-me

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

  Thanks
  Laurentiu

  
  
  

• javaGurL

  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

  
  
  

• Gene B. 3

  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)
  • Encrypted Database backup strategy: At this point of time, I see a risk if a database backup & Master Key backup are in same location. I know, Master Key is protected with a encryption password and to be able to restore this, any body need to have the original password used. But what the FORCE hint is here for
  • Best Practices for maintaining encryption passwords - You mentioned all this can be stored in a table. That's a good hint and some thing to think about.
  • When to use FORCE hint: Practical scenarios. Why Microsoft kept this option
  • Recovering an encrypted database on a differant server.

  Sorry if this is a repeated question and you might have answered these kiind concerns already.

  thanks

  satya

  
  
  

• Drew P

  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

  
  
  

• rubenswise

  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

  
  
  

• StephanLotter

  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

  
  
  
• Restore Master Key