Database Master Key Error After Database Restore

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

  • 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.


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


  • 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.

    The three biggest mistakes in life...thinking that power = freedom, sex = love, and data = information.

  • nice article!

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

  • I attached the sample code from the article as requested.


  • 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