Thank you!!Pamela ReinskouDir Web Services VersusLaw Inc. I have been trying to figure out this problem for weeks. I am not a guru but your solution saved me! I was having a problem with asymetric keys and I commented out you extra line. Also, initial tests demonstrate this works and any data encrypted previously can be decrypted just fine.Any opinions on if this is a viable approach or if there is a risk of data loss? So dropping and readding it back appears to have done the trick. We had to add one additional statement as outlined here: USE GOOPEN MASTER KEY DECRYPTION BY PASSWORD = ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY CLOSE MASTER KEY GOI suspect that issuing the ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY statement did not do anything since the server thought the DMK was already encrypted by the SMK. I think we found a solution but would like to validate it against some of you. Shameless bump to see if any encryption gurus are lurking out here today.

Development EnvironmentA standalone SQL Server database server - Windows Server 2003 Standard Edition SP2, SQL Server 2005 Developer Edition SP2 (.00) - 32 bit. Any suggestions? Production EnvironmentA 2 node active/passive failover SQL Server cluster – Windows Server 2003 Enterprise Edition SP2, SQL Server 2005 Enterprise Edition SP2 (.00) - 32 bit.

Once the backup file was restored to dev, the following statements where executed: USE GOOPEN MASTER KEY DECRYPTION BY PASSWORD = ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY CLOSE MASTER KEY GOThis concept appeared to work just fine during the development and testing stages but not in actual practice. The code that raises the error is OPEN SYMMETRIC KEY DECRYPTION BY CERTIFICATE The error generated is Msg 15466, Level 16, State 1, Line 1An error occurred during decryption.The server key, master key and certificate were created the same on all environments. Once the database is restored errors are generated on attempts to open the symmetric key. We are restoring a development database from a backup of our production database. We've got lots of great SQL ServerĮxperts to answer whatever question you can come up with.

Open master key decryption by password