I need to start encrypting several fields in a database and have been doing
some testing with a test database first.  I've run into problems when
attempting to restore the database on either the same server (but different
database) or to a separate server.

First, here's how i created the symmetric key and encrypted data in the
original database:

create master key
encryption by password = 'testAppleA3';

I also saw a posting here where it referenced running the below first:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'testAppleA3';

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

CLOSE MASTER KEY;

However when i then try to open the key it stil gives an error:

An error occurred during decryption.

create certificate test
  with subject = 'test certificate',
  EXPIRY_DATE = '1/1/2010';

create symmetric key sk_Test
with algorithm = triple_des
encryption by certificate test;

open symmetric key sk_Test decryption by certificate test;

insert into employees values (101,'Jane
Doe',encryptbykey(key_guid('sk_Test'),'$200000'));
insert into employees values(102,'Bob
Jones',encryptbykey(key_guid('sk_Test'),'$500000'));

select * from employees
--delete  from employees
select id,name,cast(decryptbykey(salary) as varchar(10)) as salary from
employees

close all symmetric keys

Next I backup up this test database and restore it to a new database on a
different server (same issue if restore to different database but on same
server).

Then if i attempt to open the key in the new database and decrypt:

open symmetric key sk_Test decryption by certificate test;

I get the error:  An error occurred during decryption.

Ok, well not unexpected, so reading the forums, i try doing the below first
in the new database:

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Then I try opening the key again and get the error again:

An error occurred during decryption.

So then it occurs to me, maybe i need to drop and recreate it so i do

drop symmetric key sk_test

then

create symmetric key sk_Test
with algorithm = triple_des
encryption by certificate test;

and then try to open it.

Same error!

So then i decide, let's drop everything, the master key, the certificate and
then symmetric key:

drop symmetric key sk_test
drop certificate test
drop master key

Then recreate the master key:

create master key
encryption by password = 'testAppleA3';

Restore the certificate from a backup i had made to a file:

CREATE CERTIFICATE test
  FROM FILE = 'c:\storedcerts\encryptiontestcert'

Recreate the symmetric key again:

create symmetric key sk_Test
with algorithm = triple_des
encryption by certificate test;

And now open the key only to get the error:

Cannot decrypt or encrypt using the specified certificate, either because it
has no private key or because the password provided for the private key is
incorrect.

So what am I doing wrong here?  In this scenario I would appear to have lost
all access to decrypt the data in the database despite restoring from a
backup which restored the symmetric key and certificate and i obviously know
the password for the master key.

I also tried running the command

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

again but this does not resolve the issue.