I am attempting to configure backup encryption in SQL 2017 and have run into some problems.
I have no issues in creating the master key and certificate on one machine. Nor do I have a problem getting the cert installed on another machine and reading the backup, however, I have an issue where this does not work should the other machine be running a different service account than the one where cert is created.
Here are the steps I am taking (I also tried restoring the master key but that also throws an error):
/* Server 1 */
/* Create the master key */
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SomeRandomSecureString';
GO
/* Create the certificate to be used for backups */
CREATE CERTIFICATE BackupCert
WITH SUBJECT = 'Backup Encryption Certificate';
GO
/* Backup the master key */
BACKUP MASTER KEY TO FILE = '\\FileShare\DatabaseMasterKey_Master.key'
ENCRYPTION BY PASSWORD = 'SomeRandomPwd';
BACKUP CERTIFICATE BackupCert TO FILE = '\\FileShare\BackupCert.cer'
WITH PRIVATE KEY (FILE = '\\FileShareBackupCert.pvk',
ENCRYPTION BY PASSWORD = 'RandomEncryptionPwd');
GO
/* Server 2 */
/* Create master key */
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SomeRandomSecureString';
GO
/* Restore the cert */
CREATE CERTIFICATE BackupCert FROM FILE = '\\FileShare\BackupCert.cer'
WITH PRIVATE KEY (FILE = '\\FileShare\BackupCert.pvk',
DECRYPTION BY PASSWORD = 'RandomEncryptionPwd');
--Msg 15208, Level 16, State 6, Line 32
--The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.
/* Try restoring the master key instead */
DROP MASTER KEY;
RESTORE MASTER KEY FROM FILE = '\\FileShare\DatabaseMasterKey_Master.key'
DECRYPTION BY PASSWORD = 'RandomEncryptionPwd'
ENCRYPTION BY PASSWORD = 'RandomEncryptionPwd';
--Msg 15317, Level 16, State 2, Line 39
--The master key file does not exist or has invalid format.
References I have looked at to try and figure out what is going on here:
- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/backup-encryption?view=sql-server-2017
- https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/create-a-database-master-key?view=sql-server-2017
- https://learn.microsoft.com/en-us/sql/t-sql/statements/create-certificate-transact-sql?view=sql-server-2017
- https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/create-an-encrypted-backup?view=sql-server-2017
I have also ensured that the SMK is the same between all machines, but still no luck in getting the cert restored.
I feel sure that I'm doing something wrong here, but I have not been able to figure out what.
Any ideas would be appreciated. Thanks.
