2005-Problem restoring database with encrypted columns to diff ser
- From: Robert <robertv@xxxxxxxxxxxxxx>
- Date: Mon, 23 Jan 2006 11:01:06 -0800
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.
.
- Follow-Ups:
- Prev by Date: win2k/sql2k to win2k3/sql2k5 dtc problems
- Next by Date: RE: 2005-Problem restoring database with encrypted columns to diff ser
- Previous by thread: win2k/sql2k to win2k3/sql2k5 dtc problems
- Next by thread: RE: 2005-Problem restoring database with encrypted columns to diff ser
- Index(es):
Relevant Pages
|
|
