Problem Restoring Database With Encrypted Columns To Different Database Or Server With Encrypted Columns.

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';

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:storedcertsencryptiontestcert'

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.



Thx.