Database Master Key Error After Database Restore

  • Slevin

    Mr or Mrs. 500

    Points: 574

    Comments posted to this topic are about the item Database Master Key Error After Database Restore

  • //Ravi

    SSCrazy

    Points: 2612

    Good Post. But Next time, please paste the code instead of images. It helps to have the code and work thought example as reading the article.

    ------------
    ๐Ÿ™‚

  • Slevin

    Mr or Mrs. 500

    Points: 574

    Yes, good call! I will supply the code a bit later today once I get home. Thank you!

    Derek

  • IMHO

    Ten Centuries

    Points: 1007

    Good post, thanks. I would love to dive into encryption more deeply and understand why such a complex structure of Service Master Keys, Certificates, Database Keys, and Symmetric Keys are all necessary and how the microsoft system compares to other encryption schemes. But, in the mean time, I appreciate a good working overview of the pieces of the puzzle.

  • mauriciorpp

    Default port

    Points: 1472

    nice article!

    note: the first code sample is outside of the "brackets".

  • Slevin

    Mr or Mrs. 500

    Points: 574

    I attached the sample code from the article as requested.

    Derek

  • $JW

    Say Hey Kid

    Points: 712

    This solution did not work for me on SQL2008. Did the process change for 2008? On the restored database I still get the following error when trying to open the Symmertic Key after successfully "ALTERing" the DMK: An error occurred during decryption. [SQLSTATE 42000] (Error 15466).

    Never mind... found the issue:

    The solution on the โ€œrestoredโ€ database copy is to 1) open the DMK 2) ALTER the DMK with DROP ENCRYPTION BY SERVICE MASTER KEY, 3) ALTER it again with ADD ENCRYPTION BY SERVICE MASTER KEY, 4) Close it. Step 3) without Step 2) does not work by itself because SQL thinks it is already encrypted by the SMK and does nothing, reporting success.

    ๐Ÿ˜›

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply