none
Getting: The key is not encrypted using the specified decryptor after DB restore.

    Question

  • We are using SQL Server 2005

     

    A client of ours  did a back up and restore of a database which we have created a db master key as well as asymmetric, symmetric keys and certificates.

     

    After the restore we issue:

    OPEN MASTER KEY DECRYPTION BY PASSWORD = 'ourpwd'

    go 

    ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

    go

     

    We get the following error:

    Msg 15313, Level 16, State 1, Line 1

    The key is not encrypted using the specified decryptor.

    Msg 15581, Level 16, State 3, Line 2

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

     

    I had the client issue the Open Master Key statement in the prod DB and got the same MSG 15313 error.  But, the encryption functions still work on their DB.

     

    I am not able to reproduce the errors in house on our own DB's.

     

    What does the error:

    Msg 15313, Level 16, State 1, Line 1

    The key is not encrypted using the specified decryptor.

    Indicate?

     

    Have they been using the wrong password to try to open the key?

     

    I have been searching all morning and can not find any good documentation regarding the error.

     

    Thanks in advance for any help.

    Elizabeth

     

    Wednesday, July 11, 2007 6:50 PM

Answers

  •   Unless the master keys are different, there shouldn't be any data loss as you are replacing the DBMK with a copy of the same key, but I prefer to err on the safe side.

     

      Thanks.

      -Raul Garcia

      SDE/T

      SQL Server Engine

    Wednesday, July 11, 2007 11:07 PM
    Moderator

All replies

  •   Correct, in this case the error means that the password is incorrect. Because the DBMK is still available at least on one of the systems (the production system), it may be possible to recover from this scenario if the DBMK has not been regenerated between the backup that you are using and the production system that still is working.

     

      On the production system (where the DBMK is protected by the SMK) use BACKUP MASTER KEY (http://msdn2.microsoft.com/en-us/library/ms174387.aspx) to create a backup of the DBMK.

      On the system where the DBMK cannot be used, you will need to use RESTORE MASTER KEY (http://msdn2.microsoft.com/en-us/library/ms186336.aspx) with FORCE option to replace the DBMK. Because you would need to use the FORCE option, make sure you have backed up any important data you have modified.

     

       I would also strongly recommend to add a new password on the production system using ALTER MASTER KEY (http://msdn2.microsoft.com/en-us/library/ms186937.aspx) in case the original password is truly lost.

     

      BTW. Remember that the passwords in SQL Server 2005 are case sensitive.

     

      Let us know if this information helps, or if you have any additional questions or feedback.

     

    -Raul Garcia

      SDE/T

      SQL Server Engine

    Wednesday, July 11, 2007 10:30 PM
    Moderator
  • Thank you for your response.

    The ultimiate goal of this test backup/restore is to move the prod DB from the current Server to a New Server.

    So, we may have to deal with some data loss if the password can't be recovered!

     

    I will let you know the results.

     

    Thanks,

    Elizabeth

     

     

    Wednesday, July 11, 2007 11:03 PM
  •   Unless the master keys are different, there shouldn't be any data loss as you are replacing the DBMK with a copy of the same key, but I prefer to err on the safe side.

     

      Thanks.

      -Raul Garcia

      SDE/T

      SQL Server Engine

    Wednesday, July 11, 2007 11:07 PM
    Moderator