locked
Restoring database master key RRS feed

  • Question

  • I needed to take a backup of a database and use it on another server and followed the steps in the following article to do so:

    https://mattsql.wordpress.com/2012/11/13/migrating-sql-server-databases-that-use-database-master-keys/

    It restored fine on the destination but I still need to use the original and I get the following error from the application when trying to use it:

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

    This is after doing the following to try and restore the master key.

    USE <database>;
    GO

    open master key decryption by password = '<password>'


    RESTORE MASTER KEY
       FROM FILE = 'C:\exportedmasterkey'
       DECRYPTION BY PASSWORD = '<password>'
       ENCRYPTION BY PASSWORD = '<password>';
    GO

    After doing this I got the message:

    The old and new master keys are identical. No data re-encryption is required.

    What else do I need to do?

    Friday, August 14, 2015 9:48 AM

Answers

  • >> I don't think it is an issue.

    This is definitely causing issues! The application can't execute any queries against the database. I'm getting errors clicking on anything because of this.

    Thankfully the following commands from the second link solved it:


    use <database>;
    go
    open master key decryption by password = '<password>'

    ALTER MASTER KEY
    ADD ENCRYPTION BY SERVICE MASTER KEY

    Friday, August 14, 2015 3:34 PM

All replies