Restoring encrypted database on another server (using Backup Encryption)

Question

I have two SQL Server instances on same machine. I want to create an encrypted backup on one of the databases and then restore it on the second instance. I am doing the following steps:

1. Create and backup database master key in the master database which is going to be used to encrypt our certificates

   USE MASTER;
   GO
   CREATE MASTER KEY
   ENCRYPTION BY PASSWORD = 'MasterKey_Password';
   GO
   BACKUP MASTER KEY
   TO FILE = 'E:\GKKeys\MASTER_KEY.key'
   ENCRYPTION BY PASSWORD = 'MasterKey_BACKUP_Password';
   GO
   

2. Create and back up the certificate that is going to be used for encryption:

   USE MASTER;
   GO
   -- създаваме сертификат, който ще използвам за криптиране на backup-a
   CREATE CERTIFICATE BackupEncryptTestCert
   WITH SUBJECT = 'smGK_BackupCertificate'
   GO
   BACKUP CERTIFICATE BackupEncryptTestCert
   TO FILE = 'E:\GKKeys\SMGK_BACKUP_CERTIFICATE.cer'
   WITH PRIVATE KEY
   (
    FILE = 'E:\GKKeys\SMGK_BACKUP_CERTIFICATE_PRIVATE_KEY.key'
    ,ENCRYPTION BY PASSWORD = 'smGK_BackupCertificate_BACKUP_Password'
   );
   

3. Creating the backup:

   BACKUP DATABASE smGK
   TO DISK = 'E:\GKKeys\smGKFULLEncrtypted.back'
   WITH COMPRESSION, STATS = 10, ENCRYPTION (ALGORITHM = AES_256,SERVER CERTIFICATE = BackupEncryptTestCert)
   

4. Now on the second instance I want to restore the master key but I am not allowed:

   USE MASTER;
   GO
   RESTORE MASTER KEY
   FROM FILE = 'E:\GKKeys\MASTER_KEY.key'
   DECRYPTION BY PASSWORD = 'MasterKey_BACKUP_Password'
   ENCRYPTION BY PASSWORD = 'smGK_MasterKeyPassword';
   

   Msg 15317, Level 16, State 2, Line 4 The master key file does not exist or has invalid format.

Could anyone tell what is causing this?

Microsoft SQL Server 2014 - 12.0.4100.1 (X64) 
 Apr 20 2015 17:29:27 
 Copyright (c) Microsoft Corporation
 Enterprise Edition: Core-based Licensing (64-bit) on 
 Windows NT 6.3 (Build 9600: ) (Hypervisor)

• SQLPRODDBA: Let us continue this discussion in chat.

Answer 1

Create a brand new master key on your second instance. i.e. don't create it from backup you taken from 1st instance. Then restore certificate from the backup taken and then try. I guess you don't need master key and only certificate is required for restore purposes. Follow the below steps:

Step1: Create Master Key

CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'MasterKey_Password';

Step2: Verify permissions on cert and pvt key

Make sure SQL Server service account of second instance has FULL permissions on cert and pvt key that you created.

Step3: Create cert from backup

CREATE CERTIFICATE BackupEncryptTestCert
 FROM FILE = 'E:\GKKeys\SMGK_BACKUP_CERTIFICATE.cer'
 WITH PRIVATE KEY 
 ( 
 FILE = 'E:\GKKeys\SMGK_BACKUP_CERTIFICATE_PRIVATE_KEY.key' ,
 DECRYPTION BY PASSWORD = 'smGK_BackupCertificate_BACKUP_Password'
 ) 

Step4: Restore the DB

• variable: How to do the step 2 exactly? And on which instance did you do the step 3?
• MikeB: Are you sure it's necessary to mess with the Master Key? How does that affect other operations which might depend on the master key?

Answer 2

I can't comment but I'd like to add a bit of detail to this answer.

• Database master key passwords do not need to match between instances
• When you backed up the certificate you added a private key and password, this private key is independent of the existing database master key.
• This certificate can now be created using the .cert, .key, and private key password on any other instance that has a database master key (as long as the service account has permissions as SQLPRODDBA mentions)

SQLITY post on backing up certificates

in SQL Server all keys are at all times protected by either a password or by another key in the encryption hierarchy. That extends to the backup files too. As backup files are intended to be stored of site, the SQL Server encryption hierarchy is not available to protect them. Therefore, we have to provide a password to protect the key.

• peterh: Please refer that answer.
• Chaim Eliyah: "The encryption hierarchy": learn.microsoft.com/en-us/sql/relational-databases/security/…