asked on

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

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.

Faiga Diegel

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.

nyphalanx

ASKER

I'm getting the following error message when trying to restore it

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

ASKER CERTIFIED SOLUTION

Faiga Diegel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nyphalanx

ASKER

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;

nyphalanx

ASKER

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';

nyphalanx

ASKER

Everything is fine now, had to also restore the master service key and restore the database again.