4

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)
9

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

0

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.

  • 1
    Please refer that answer. – peterh Apr 5 at 19:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.