RE: 2005-Problem restoring database with encrypted columns to diff ser
- From: Robert <robertv@xxxxxxxxxxxxxx>
- Date: Mon, 23 Jan 2006 11:17:05 -0800
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.
"Robert" wrote:
> 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:
- RE: 2005-Problem restoring database with encrypted columns to diff ser
- From: Peter Yang [MSFT]
- RE: 2005-Problem restoring database with encrypted columns to diff ser
- References:
- Prev by Date: 2005-Problem restoring database with encrypted columns to diff ser
- Next by Date: Re: Linked server and Windows domain user.
- Previous by thread: 2005-Problem restoring database with encrypted columns to diff ser
- Next by thread: RE: 2005-Problem restoring database with encrypted columns to diff ser
- Index(es):
Relevant Pages
|
|
