none
Question on restoring encrypted databases on different servers

    Question

  • I have run into problems trying to use backed-up encrypted databases. I have an ASP.Net/SqlServer 2005 website for which I have production, test, and development environments, each on its own box. I have set up encryption using Master Keys, Certificates, and Symmetric Keys using the following commands:

     

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

     

    CREATE CERTIFICATE CERTIFICATE_NAME WITH SUBJECT = 'My Website'

     

    CREATE SYMMETRIC KEY KEY_NAME WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE CERTIFICATE_NAME.

     

    All databases have the same certificate and symmetric key names. The Production database has a different password than the development and test databases.

     

    I am running into problems when I back up the development database and try to move it to the test environment. When I do the move, and then try to access the encrypted data, I get an error "An error occurred during decryption", even though they are the "same" database (I originally detached and copied the development database to the test box when I set up the test environment) with the same password.

     

    I then try the following commands, which I used when I brought the development database over to the test box the first time:

     

    OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'

     

    ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'password'

     

    This caused the following error:

    "An error occurred while decrypting certificate 'CERTIFICATE_NAME' that was encrypted by the old master key. The FORCE option can be used to ignore this error and continue the operation, but data that cannot be decrypted by the old master key will become unavailable."

     

    So, how do you set up encrypted databases so that you can backup from one database server and restore on another? This problem will be very important when I set up a backup production database server.

    Monday, July 03, 2006 3:48 PM

Answers

  • Also, the commands that you need to execute when moving a database to another server are:

    OPEN MASTE KEY DECRYPTION BY PASSWORD = 'password'
    ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

    The REGENERATE option should be used only if you want to have a new master key in the database.

    Thanks
    Laurentiu

    Wednesday, July 05, 2006 7:28 PM
  •    From the error message and your description, I am guessing that somehow while moving the DB from one environment to another the metadata for either the master key (DBMK) or the certificate got somehow corrupted, or rather out of sync.

     

       I am assuming that on the test environment the DB and an earlier copy of both the cert and DBMK (or only the DBMK) exist; based on that, this is my guess. On the dev environment you have DBMK_1 protecting cert_pvk_1, but when the DB is moved to the test environment, one of the following situations happened:

    1) DBMK_1 does not replaced DBMK_0, and cert_pvk_1 protected by DBMK_1 (which is not present)

    2) cert_pvk_1 metadata is not updated, and we have cert_pvk_1 protected by DBMK_0, but DBMK_1 replaced DBMK_0

     

      Can you please verify if my assumption is correct? If so, I can try to reproduce the scenario in my environment to verify. If my assumption is not correct we will need more information. What are the OS versions running in both environments? Is there any significant difference between both environments (i.e. OS language, any third party software that may affect the crypto-API calls, etc)?

     

      Can you also please send me a copy of the backup/restore commands you used in your scenario? I can use that information as well to try to reproduce this problem.

     

      Thanks a lot,

    -Raul Garcia

     SDE/T

     SQL Server Engine

     

     

    Monday, July 03, 2006 7:02 PM
  • You can use ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY.

    Thanks
    Laurentiu

    Friday, July 07, 2006 11:56 PM
  •   You can use the following statement:

     

    ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY

     

      Just to verify it:

     

    -- Should fail with error 15581:

    -- Please create a master key in the database or open the master key in the session before performing this operation.

    create certificate foo with subject = 'foo'

    go

     

    OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<Your DBMK p@ssw0rD>>'

    go

     

    -- Will succeed

    create certificate foo with subject = 'foo'

    go

     

    CLOSE MASTER KEY

    Go

     

      -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Saturday, July 08, 2006 12:03 AM

All replies

  •    From the error message and your description, I am guessing that somehow while moving the DB from one environment to another the metadata for either the master key (DBMK) or the certificate got somehow corrupted, or rather out of sync.

     

       I am assuming that on the test environment the DB and an earlier copy of both the cert and DBMK (or only the DBMK) exist; based on that, this is my guess. On the dev environment you have DBMK_1 protecting cert_pvk_1, but when the DB is moved to the test environment, one of the following situations happened:

    1) DBMK_1 does not replaced DBMK_0, and cert_pvk_1 protected by DBMK_1 (which is not present)

    2) cert_pvk_1 metadata is not updated, and we have cert_pvk_1 protected by DBMK_0, but DBMK_1 replaced DBMK_0

     

      Can you please verify if my assumption is correct? If so, I can try to reproduce the scenario in my environment to verify. If my assumption is not correct we will need more information. What are the OS versions running in both environments? Is there any significant difference between both environments (i.e. OS language, any third party software that may affect the crypto-API calls, etc)?

     

      Can you also please send me a copy of the backup/restore commands you used in your scenario? I can use that information as well to try to reproduce this problem.

     

      Thanks a lot,

    -Raul Garcia

     SDE/T

     SQL Server Engine

     

     

    Monday, July 03, 2006 7:02 PM
  • Also, the commands that you need to execute when moving a database to another server are:

    OPEN MASTE KEY DECRYPTION BY PASSWORD = 'password'
    ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

    The REGENERATE option should be used only if you want to have a new master key in the database.

    Thanks
    Laurentiu

    Wednesday, July 05, 2006 7:28 PM
  • This solves my problem. Thank you for your help.

    However,  it has opened up a new line of thought for me. If you don't use the ALTER MASTER KEY command in the backup database, you have to open the master key every time you want to access the encrypted data. I think I would like that requirement in the original database(it always bothered me that if you can decrypt at will if you know the name of the symmetric key and the certificate).

    So, how do you make the original database require the Open Master Key Statement?

    Friday, July 07, 2006 9:17 PM
  • You can use ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY.

    Thanks
    Laurentiu

    Friday, July 07, 2006 11:56 PM
  •   You can use the following statement:

     

    ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY

     

      Just to verify it:

     

    -- Should fail with error 15581:

    -- Please create a master key in the database or open the master key in the session before performing this operation.

    create certificate foo with subject = 'foo'

    go

     

    OPEN MASTER KEY DECRYPTION BY PASSWORD = '<<Your DBMK p@ssw0rD>>'

    go

     

    -- Will succeed

    create certificate foo with subject = 'foo'

    go

     

    CLOSE MASTER KEY

    Go

     

      -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Saturday, July 08, 2006 12:03 AM
  • Hi,

    I am also trying to restore a database which has an encrypted column.  I used methods from Larentiu's blog post - column encryption demo. 

    I execute this command and get:

    OPEN MASTER KEY DECRYPTION BY PASSWORD = '<PWD>'

    Msg 15313, Level 16, State 1, Line 1

    The key is not encrypted using the specified decryptor.

     

    I try this on my original database and I get the same result - what have i done?!  I'm guessing my password is wrong, but i do have the script I used to create it right in front of me, and written: and they match.

     

     

    Monday, November 27, 2006 10:09 PM
  • Hi Sam,

     

    I was browsing for answer on my problem which happen to be your previous problem on open master key command. I get the same error when opening the master key. How did you resolved this?

     

    thanks...

     

     

    Friday, May 04, 2007 12:09 AM
  • I just answered a similar problem in the following thread of this forum:

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1848415&SiteID=1

     

      Hopefully this information will help, but if you still have any problems let us know.

     

    -Raul Garcia

      SDE/T

      SQL Server Engine

    Thursday, July 12, 2007 12:45 AM