HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

Restoring encrypted database on another server (using Backup Encryption)

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
encryptionencryptedrestoringdatabaseanotherusingserverbackup

Problem

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:

-
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


-
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'
);


-
Creating the backup:

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


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

Solution

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

Code Snippets

CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'MasterKey_Password';
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'
      )

Context

StackExchange Database Administrators Q#149776, answer score: 16

Revisions (0)

No revisions yet.