SQL Server – Restoring Encrypted Database on Another Server

backupencryptionsql serversql server 2014

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)

Best Answer

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