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.

nyphalanxAsked:
Who is Participating?
 
Faiga DiegelSr Database EngineerCommented:
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
 
Faiga DiegelSr Database EngineerCommented:
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
 
nyphalanxAuthor Commented:
I'm getting the following error message when trying to restore it

The master key file does not exist or has invalid format.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
nyphalanxAuthor Commented:
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
 
nyphalanxAuthor Commented:
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
 
nyphalanxAuthor Commented:
Everything is fine now, had to also restore the master service key and restore the database again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.