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:
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.