Sql-server – Restore encrypted database to another server

backuprestoresql serversql-server-2008

I'm using a product which runs on SQLServer 2008. Understandably the company supplying it don't offer SQLServer support. When I installed the product I specified a password to encrypt the database. I'd like to run another copy of the product for testing on another server. I've restored the database to another server and installed the product on that other server. When I installed it I supplied the same password, and then restored a backup from the main server. However I'm getting the error message:

System.Data.SqlClient.SqlException: An error occurred during decryption.

from the product. I can access the tables using SQLServer Management studio.

I have tried this:

on first server:

CREATE CERTIFICATE cert1 WITH SUBJECT = 'Certificate for my stuff'

BACKUP CERTIFICATE  cert1 TO FILE = 'd:\backup\cert1.dat' 
WITH PRIVATE KEY 
(
ENCRYPTION BY PASSWORD = 'mypassword',
FILE = 'd:\backup\cert1_privatekey.dat'
)

on second server:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mypassword'

CREATE CERTIFICATE cert1 FROM FILE = 'd:\cert1.dat'
WITH PRIVATE KEY
(
FILE = 'd:\cert1_privatekey.dat',
DECRYPTION BY PASSWORD = 'mypassword'
)

I also tried this on the second server:

alter MASTER KEY regenerate with enCRYPTION BY PASSWORD='password'

but that gave an error message about an asymmetric key.

How can I restore the backup from the first server onto the second server?

Update:

There is an asymmetric and symmetric key in the database as well. If I open the symmetric key using the asymmetric key I get the same error, so I think that's why it's not working – somehow the keys aren't transferring in such a way that they can be used.

Best Answer

I wrote a blog entry about mirroring and TDE.

It took me a while to figure out that to get the db on the secondary server, I needed the code line:

 OPEN MASTER KEY DECRYPTION BY PASSWORD = 'SomePassword'
 ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
 GO

The password is the same one that I used to encrypt the master key file. After issuing the command on the 2nd SQL server, everything played nicely. Here is the db.stackexchange post I made also.

Related Solutions

Sql-server – Why use asymmetric keys in SQL Server

The proper key hierarchy is the Service Master Key protects the Database Master Key, the Database Master Key protects either an asymmetric key or certificate, the asymmetric key or certificate can be used for encryption or can be used to protect a symmetric key which is used for encryption. The keys are created in that order. The system creates and protects the SMK, and the DMK and subsequent keys are created by the user.

The design of the key hierarchy in SQL Server protects both the data and keys from compromise. Remember that with a symmetric key, the same key is used to encrypt and decrypt data, or in this case other keys. The main purpose of introducing an asymmetric key or certificate into the hierarchy protected by the Database Master Key is to prevent an attack on the DMK and SMK from inside of the database. If someone malicious wanted to obtain the DMK unencrypted, then that person would need to do one of two things. Either decrypt the service master key and use it to decrypt the DMK, which is not possible because only the database engine can use the Data Protection API to do this, or attempt to decrypt from the top of the hierarchy down, which would be possible if there were not an asymmetric key or certificate in the way.

All of the signed or encrypted bits of the symmetric keys in SQL Server are in a system table named sys.crypt_properties in each database, including the encryption of the Service Master Key in the master database. There is no system table that contains the private key for either of the asymmetric key types. If all keys in a hierarchy were symmetric keys, then the SMK would encrypt the DMK and the DMK would encrypt the symmetric key that would encrypt the data. Because of the way that symmetric keys work, that would also mean that, if someone opens the symmetric key for the data, then it can theoretically decrypt the DMK and the decrypted DMK can be saved by a malicious user or used to decrypt the SMK because the same key is used for encryption and decryption. This is why an asymmetric key or certificate is required to be an integrated part of the encryption hierarchy.

Sql-server – Restore db with encrypted column in SQL Server, without overwriting existing Service Master Key

Server1 running SQL Server 2012 with Service Master Key A, db1 with Database Master Key 1, symmetric key and certificate available.

I assume that the db1 master key is encrypted with the SMK. This makes everything encrypted by the database master key 'available' to applications, w/o having to explicitly open the database master key.

What you need to do is to restore the database, open the database master key using the DBMK password and then add the Server 2 SMK encryption to the DBMK:

RESTORE DATABESE ... FROM ...;
USE ...;
OPEN MASTER KEY DECRYPTION BY PASSWORD = ...;
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

Best Answer

Related Solutions

Sql-server – Why use asymmetric keys in SQL Server

Sql-server – Restore db with encrypted column in SQL Server, without overwriting existing Service Master Key

Related Question