Encrytion - Moving Db From One Instance To Another

Hello,

I spent a lot of time yesterday reading all the threads and other information I can regarding this topic, and I thought I had it figured out but I don't think I quite understand it, so I am posting here for a little clarification.

I read the following links to begin with:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1848415&SiteID=1

http://blogs.msdn.com/lcris/


I set up encryption on SQL Server instance, then backed up and restored a database to another instance on another machine. From what I have read, I thought all I needed to do was the following:

OPEN MASTER KEY DECRYPTION BY PASSWORD = '<password>'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
CLOSE MASTER KEY

I then tried to read encrypted data by doing the following:




OPEN SYMMETRIC KEY <key>

DECRYPTION BY CERTIFICATE <cert>

SELECT CONVERT(varchar, DecryptByKey(<column>)) FROM <table>



CLOSE SYMMETRIC KEY <key>




But I get the following error on when opening the symmetric key:

"An error occurred during encryption".

I tried executing these statements in various order to see if i was running them out of order but still the same error.

Obviously I have overlooked something. Can someone please shed some light on what I have missed?

Greatly appreciated...

Scott