Moving encrypted database

20 Jul 2006 3:48 PM

Gerhard

I have an encrypted 2005 database on my development server that I want to
move to a testing server.

Both of the servers were set up using the same CREATE MASTER KEY ENCRYPTION

Yet, when I use a backup and restore to move the database, I get a
decryption error on OPEN SYMMETRIC KEY

What is the proper way to move the development database to a different
server so that I don't have decryption issues?

Thanks.

21 Jul 2006 7:23 AM

privatenews

Hello,

Thank you for using Microsoft MSDN newsgroup.

I understand that you have an encrypted 2005 database and you want to move
it to a testing server. You received the error message on "Opne symmetric
key" when you use backup/restore to move database. If this isn't correct,
please let me know.

I was not able to reproduce the issue:

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

2. Backup/restore to the another SQL server.

3. 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 development/test server instances?

If this is not the method you use to encrypt the data, please let me know
the details how you encrypt the database and how you backup/restore
database.

Please let's know if you have further questions or concerns on this issue
and we look forward to your reply.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

25 Jul 2006 12:50 PM

privatenews

Hello,

Just want to check in if you have further questions on the issue. Please
feel free to post back if need any help.

Best Regards,

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

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

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