Solved

How can I move a database using Symmetric encryption to another server?

Posted on 2009-05-08
6
642 Views
Last Modified: 2012-05-06
I have a database that uses the built-in SQL Server 2005 SYMMETRIC encryption feature. I have deployed this database on another server, but I'm unable to decrypt any of the data. When I use the function DecryptByKey() all of the data comes back blank. The same queries work find on the development server.

0
Comment
Question by:nyphalanx
  • 4
  • 2
6 Comments
 
LVL 15

Expert Comment

by:faiga16
ID: 24339958
You have to sync up the master key.

Try these steps:

/*backup masterkey to file*/
backup master key to file = 'E:\Backup\Keys\MyDMK.key'
encryption by password = 'mysecretpassword'

/*restore master key from file */
restore master key from file = E:\Backup\Keys\MyDMK.key''
decryption by password = 'mysecretpassword'
encryption by password = 'mysecretpassword'
FORCE;

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

select * from sys.symmetric_keys
select * from sys.certificates
select * from sys.openkeys

Then try decrypting it again.
0
 

Author Comment

by:nyphalanx
ID: 24340094
I'm getting the following error message when trying to restore it

The master key file does not exist or has invalid format.
0
 
LVL 15

Accepted Solution

by:
faiga16 earned 500 total points
ID: 24340941
The file should be on the folder you specify. Or i mistyped it:

restore master key from file = 'E:\Backup\Keys\MyDMK.key'

make sure you have the folder name in that drive. You can specify your own drive and folder by the way.
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:nyphalanx
ID: 24353983
Restoring the master key works fine, but when I issue this command

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

I get the error

Please create a master key in the database or open the master key in the session before performing this operation.

Below is the original code I used when creating the master key on my development database.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mypassword'
GO

CREATE CERTIFICATE EncryptedFieldCertificate WITH SUBJECT = 'Encrypted Fields';
GO

CREATE SYMMETRIC KEY EncryptedFieldSymmetricKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE EncryptedFieldCertificate;
0
 

Author Comment

by:nyphalanx
ID: 24354855
Ok i've gotten a little further along now.

I use the following command to backup the keys

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mypassword'

BACKUP MASTER KEY TO FILE = 'c:\Backup\Website_MasterKey'
ENCRYPTION BY PASSWORD = 'mypassword'
GO

BACKUP CERTIFICATE EncryptedFieldCertificate TO FILE = 'c:\Backup\Website_EncryptedFieldCertificate'
GO

now on a separate server I use the following commands to restore the keys

RESTORE MASTER KEY FROM FILE = 'C:\Website_MasterKey'
DECRYPTION BY PASSWORD = 'mypassword'
ENCRYPTION BY PASSWORD = 'mypassword'
FORCE;

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mypassword'

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
CLOSE MASTER KEY;
GO

CREATE CERTIFICATE EncryptedFieldCertificate
    FROM FILE = 'C:\Website_EncryptedFieldCertificate'
GO

CREATE SYMMETRIC KEY EncryptedFieldSymmetricKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE EncryptedFieldCertificate;
GO


Now when I try to decrypt my data, it ends up being blanked. I noticed the in the sys.certificates table the value for pvt_key_encryption_type is 'NA' and for pvt_key_encryption_type_desc it's 'NO_PRIVATE_KEY', however on my development database the values are 'MK' and 'ENCRYPTED_BY_MASTER_KEY' respectively.

Now if I create a certificate, with the command below, instead of creating it from the backed up certificate file. The values under those two columns are correct, but my data is still blank when unencrypted.

CREATE CERTIFICATE EncryptedFieldCertificate WITH SUBJECT = 'Encrypted Fields';
0
 

Author Comment

by:nyphalanx
ID: 24356565
Everything is fine now, had to also restore the master service key and restore the database again.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now