?
Solved

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

Posted on 2009-05-08
6
Medium Priority
?
674 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 15

Expert Comment

by:Faiga Diegel
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:
Faiga Diegel earned 1500 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

771 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