locked
Database restore & Symmetric keys RRS feed

  • Question

  • Hi,

    SQL Server 2005


    Have a database which utilises a symmetric key / certificate to secure some of the database columns.  I have a need to
    move this to a new server but am running into problems when it comes to decrypting the data on the new server.

    Steps I've taken so far

    • Backup database and restore to new server/instance.
    • Recompile all the procedures which use symmetric keys & certificates.  By recompile I mean edit the code, add some whitespace and excute using alter ... .
    • Create the database master key using the same password
    • Create a certificate
    • Create a symmetric key based upon that certificate

    The error i... 
    Msg 15581, Level 16, State 3, Procedure usp_GetFile, Line 6
    Please create a master key in the database or open the master key in the session before performing this operation.

    The stored procedure I'm running opens the symmetric key, see below and theis definately worked on the original server.


    OPEN SYMMETRIC KEY TransfersKey
    DECRYPTION BY CERTIFICATE TransfersCertificate

    Pretty sure I'm missing something really obvious.

    Anyone got any ideas?

    Thanks
    Dave











     


    Thursday, November 19, 2009 3:04 PM

Answers

  •   A better alternative for your scenario may be to OPEN MASTER KEY using the password (after all, you know the DBMK password) and then run ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY in order to re-enable the automatic DBMK access. This option should work without any false-negative warnings.

      The warning in the restore DBMK case is because the DBMK from the restored DB cannot be opened, but since you are replacing the DBMK with a copy of itself, it should be OK for the demo above; but you have the risk of ignoring a real failure.

      I hope this information helps.

      -Raul Garcia
       SDE/T
       SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, November 19, 2009 7:31 PM

All replies

  • The other instance will have a different Server Master Key (SMK), but the Database Master Key (DMK) is tied to the database you are trying to move.  In order to move it to another machine, you need to have a backup of the DMK.  You restore/recreate the database to the new instance and then restore the DMK, encrypting it with the new instance's SMK.  That should be all that you need in order to access the encrypted data.
    Thanks, Leks
    Thursday, November 19, 2009 4:07 PM
  • The other instance will have a different Server Master Key (SMK), but the Database Master Key (DMK) is tied to the database you are trying to move.  In order to move it to another machine, you need to have a backup of the DMK.  You restore/recreate the database to the new instance and then restore the DMK, encrypting it with the new instance's SMK.  That should be all that you need in order to access the encrypted data.
    Thanks, Leks
    Hi,

    Can I use anything for the password or do I need to use the password that was used to create the key?

    On the source database I've backed up the DMK using this command:

    BACKUP MASTER KEY TO FILE = 'C:\Transfers.key' ENCRYPTION BY PASSWORD = 'secretkey'
    

    On the restored database I've

    RESTORE MASTER KEY FROM FILE = 'C:\Transfers.key'
         DECRYPTION BY PASSWORD = 'secretkey'
         ENCRYPTION BY PASSWORD = 'secretkey'
    FORCE

    Which gives the following warning:
    The current master key cannot be decrypted. The error was ignored because the FORCE option was specified
    

    Are these the correct steps to do in order to restore a database which employs encryption?

    Thanks
    Dave









    Thursday, November 19, 2009 6:14 PM
  • Should add that I can now run stored procedures to decrypt the data but I do wonder if I should be concerned about the warning message.


    Thursday, November 19, 2009 6:16 PM
  •   A better alternative for your scenario may be to OPEN MASTER KEY using the password (after all, you know the DBMK password) and then run ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY in order to re-enable the automatic DBMK access. This option should work without any false-negative warnings.

      The warning in the restore DBMK case is because the DBMK from the restored DB cannot be opened, but since you are replacing the DBMK with a copy of itself, it should be OK for the demo above; but you have the risk of ignoring a real failure.

      I hope this information helps.

      -Raul Garcia
       SDE/T
       SQL Server Engine


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, November 19, 2009 7:31 PM