This post should be under the security forum.I think the issue is that the database master key is encrypted with the service master key, which is then used to encrypt the certs and/or symmetric keys in the database. Even though the database master key may be the same they are not encrypted the same unless you are sharing the same service certs for both servers. Try opening the master key in the database that is not decrypting successfully and reencrypting and see if it works:OPEN MASTER KEY DECRYPTION BY PASSWORD = 'YourPassword'ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'YourPassword'CLOSE MASTER KEYopen symmetric key YourKeyNameDECRYPTION BY certificate YourCertname select top 20 cast(decryptByKey(YourEncryptedFieldName) as varchar(8000)) ,cast(decryptByKey(YourEncryptedFieldName) as varchar(8000)) ,*from YourTableName CLOSE ALL SYMMETRIC KEYS
Sorry to bring this thread back to life. I have a slightly similar issue.
Am I correct in assuming that you have assumed that the OP wanted to restore it to different server.
My question is this. If I am restoring a database on the same server with encrypted columns using symmetric keys, then I do not need to do any of that do I? as symmetric key is restored and so is DBMK? and I can just happily go and open key and decrypt....?????????