RE: 2005-Problem restoring database with encrypted columns to diff ser



Hello Robert,

I was not able to reproduce the issue on my side.

On a SQL Express instance, create a database/table, Master key,
Certificate, symmetric key and insert the record with encrypted column.

Backup/restore to the SQL Enterprise edition instance. I could run the
following query successfully on the restored database:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'testAppleA3';

open symmetric key sk_Test decryption by certificate test;

It seems the issue is related to SQL service account. Is the service
account the same on both SQL Express and SQL Enterprise instances?

Master key protected by old service account credentials may need to be
protected by the current one. You can use the alter service master key
statement to enter the old credentials and have it be protected by the
current account.

Also, you may want to use same Windows account to start SQL instances to
test the situation.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================

Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.

This and other support options are available here:

BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469

Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/

If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.

=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------
>Thread-Topic: 2005-Problem restoring database with encrypted columns to
diff ser
>thread-index: AcYgUZjYietJilQUTrOvZ82WtyHCWw==
>X-WBNR-Posting-Host: 159.37.7.48
>From: =?Utf-8?B?Um9iZXJ0?= <robertv@xxxxxxxxxxxxxx>
>References: <4CB8E9AA-99DF-44A1-8375-BDAB8B69A586@xxxxxxxxxxxxx>
>Subject: RE: 2005-Problem restoring database with encrypted columns to
diff ser
>Date: Mon, 23 Jan 2006 11:17:05 -0800
>Lines: 156
>Message-ID: <E0B161AC-6911-45DE-8100-C42FA028BB1F@xxxxxxxxxxxxx>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.security
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.security:26209
>X-Tomcat-NG: microsoft.public.sqlserver.security
>
>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.
>>
>>
>

.