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.

up vote 5 down vote accepted

You need to fix the NTFS permissions on the certificate. By default SQL, when it creates the backup will set the NTFS permissions so that only the account running the SQL Service can read the certificate backup file.

This is why it works with a single account, and doesn't work with two accounts.

  • Awesome, that did the trick, thanks Denny. Never considered restricted ACL on a file in a share where both service accounts had full control. – Nic Jul 10 at 16:38

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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