patternsqlModerate
Restoring encrypted database on another server (using Backup Encryption)
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
-
Create and back up the certificate that is going to be used for encryption:
-
Creating the backup:
-
Now on the second instance I want to restore the
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)
-
Create and backup database master key in the
master database which is going to be used to encrypt our certificatesUSE 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
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
Step4: Restore the DB
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.