SQL Server Certificate Restore Issue with Different Service Account

backupencryptionsql serversql-server-2017

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.

Best Answer

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.

Related Solutions

SQL Server 2008 – Decrypting Previously Encrypted Data Returns NULL

This sounds like a case where the key_guid in the encrypted data does not match the key_guid for the symmetric key used to encrypt it.

When data is encrypted using a symmetric key and is then saved in a varbinary column, the first 16 bytes are the Key_guid of the symmetric key used to encrypt the data, which explains how DECRYPTBYKEYAUTOCERT can automatically locate the correct symmetric key.

Can you run the script below and verify that this is the case? And, did you encrypt and decrypt the data using the same database?

select 
    name,cast(key_guid as varbinary(max))
from sys.symmetric_keys
where
    name ='key_dbKeys'

select distinct table.data from table
where 
    table.data is not null

After attempting to recreate the problem, there could be a problem opening the symmetric key prior to inserting the data or, after reviewing the documentation for DECRYPTBYKEYAUTOCERT, you need to cast the return, which is varbinary, to varchar. I'm also posting the working script that is based on what you have developed. I had to use a different password.

declare  @clean_data varchar(100)

set  @clean_data='Hello World'

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssword!';

CREATE CERTIFICATE cert_dbKeys
   ENCRYPTION BY PASSWORD = 'P@ssword!'
   WITH SUBJECT = 'Database encryption key', 
   EXPIRY_DATE = '20201031';

CREATE SYMMETRIC KEY key_dbKeys 
   WITH ALGORITHM = AES_128
   ENCRYPTION BY CERTIFICATE cert_dbKeys;
create table EncryptedData
( secretmessage varbinary(max))

OPEN SYMMETRIC KEY key_dbKeys
DECRYPTION BY CERTIFICATE cert_dbKeys WITH PASSWORD = 'P@ssword!'

insert into EncryptedData (secretmessage) values(EncryptByKey(Key_GUID('key_dbKeys'), @clean_data))

CLOSE SYMMETRIC KEY key_dbKeys;

SELECT cast(DecryptByKeyAutoCert(cert_id('cert_dbKeys'), N'P@ssword!', secretmessage) as varchar(100)) FROM EncryptedData

SQL Server – Restoring Encrypted Database on Another Server

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

Best Answer

Related Solutions

SQL Server 2008 – Decrypting Previously Encrypted Data Returns NULL

SQL Server – Restoring Encrypted Database on Another Server

Related Question