Solved

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

Posted on 2009-05-08
6
650 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Date from a string 4 70
How to disable/enable multiple sql jobs in efficient way 11 117
Error when saving to sql table a '/' 5 29
tempdb log keep growing 7 33
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

840 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