Problem restoring database with encrypted columns to different database or server with encrypted

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:\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.

Thx.

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.

|||

So now I'm confused.

Where this did not work is when i created the intial database using SQL Server Express edition on a Win XP workstation and then was restoring to a database located on a SQL Server Standard edition on a Windows 2003 Server.

I just tried creating everything from scratch on one WIndows 2003 Server with SQL Server Standard edition and then restoring that database to a different Windows 2003 Server also with SQL Server Standard and this time it worked as long as i did the below first:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'testAppleA3';

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

CLOSE MASTER KEY;

My question (comes to reliabilty concerns) is why did it not work when i did:

Orig database SQLSvr Express on Win XP

Restore to SQL Svr Std on Win2003Svr

But did work when I did

Orig database SQL Svr Std on Win 2003Svr

Restore to different SQL Svr Std on different Win 2003 Svr

What would the reason for this be?

Thx.

|||

See this thread:

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

There is an issue if you restore the database on a different time zone, but otherwise, the steps described in that thread are the ones that should be performed after restoring a database that has encrypted data. The time zone issue may also affect you if the times of the machines that you use are not in sync.

You should NOT drop and recreate the symmetric key, that will just create a different key and you'll have no way to decrypt the data in the database.

To figure out what's going on, I suggest the following:

1. Make sure this is not a time issue; check the times of the two machines. If there is a difference, wait for the amount of time before trying to decrypt.

2. If it's not a time issue, try protecting the symmetric key with a password as well as with a certificate. Try opening the key using both the password and the certificate. This should allow you to figure out if the problem is with the certificate or with the key. Also, please post the entire error message that you receive, including the error number and error state - the state may provide some additional information.

Thanks
Laurentiu

|||

Thanks. What you say makes sense and I'm going to try it again. Like I said, everything was fine when the both servers involved were Windows 2003 with SQL Svr Std.

But it didn't work when one was SQL Svr Express on WinXP. So that's the part I'm confused about.

But I will try it again in that scenario later today to see if it is reproducible.

The timezone is the same on all machines involved in these tests.

Thanks,
R-

|||

Have you been able to repro this issue. Have you tried using a password to isolate the problem?

Thanks
Laurentiu

sql