SQL Server – Creating Encrypted Backups for Client Restoration

backupcertificateencryptionrestoresql server

I am trying to understand the mechanics of database backup encryptions, but there are some issues, which I can't seem to wrap my head around. I have 2 requirements:

  1. I need to be able to create and restore ENCRYPTED database backups "locally", i.e. on servers, which I have full access.
  2. I need to be able to transfer the ENCRYPTED database backup to a client, who is responsible for the administration as well as performing the restore.

On my local server

I have created a Master Key and backed it up to a secure location:

USE MASTER
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKey_Password';

BACKUP MASTER KEY
TO FILE = '<Secure_Location>\MASTER_KEY.key'
ENCRYPTION BY PASSWORD = 'MasterKey_BACKUP_Password';

Next I have created a certificate and backed it up to a secure location:

CREATE CERTIFICATE MyBackupCertificate
WITH SUBJECT = 'MyBackupCertificate_Subject';

BACKUP CERTIFICATE MyBackupCertificate
TO FILE = '<Secure_Location>\CERTIFICATE_BACKUP.cer'
WITH PRIVATE KEY(
    FILE = '<Secure_Location>\CERTIFICATE_BACKUP_PRIVATE_KEY.key',
    ENCRYPTION BY PASSWORD = 'Certificate_BACKUP_Password');

Finally, I have backup up the database using the certificate to encrypt it:

BACKUP DATABASE MyDatabaseToBackup
TO DISK = '<Backup_Location>\BACKUP_FILE.bak'
WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = MyBackupCertificate)

On my client server

I have transferred the database backup file to a location accessible from the SQL Server Instance. Also the .cer file has been transferred to this location.

USE MASTER
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'Another_MasterKey_Password';

Created the certificate from the .cer-file:

CREATE CERTIFICATE MyDatabaseToBackup
FROM FILE = '<Client_Secure_Location>\CERTIFICATE_BACKUP.cer'

When trying to restore the file list, I am met with the message

RESTORE FILELISTONLY FROM DISK = '<Client_Backup_Location>\BACKUP_FILE.bak'

Msg 15507, Level 16, State 30, Line 11
A key required by this operation appears to be corrupted.

I have been around some other posts, which are trying (almost) the same, but I do not understand, why I have to transfer the certificates private key to my client. Doesn't that defeat the whole purpose of encryption?

Restoring encrypted database on another server (using Backup Encryption)

Best Answer

Created the certificate from the .cer-file […]

You created the certificate but you didn't include the import of the private key, so it makes the use of this certificate to decrypt the data impossible.

why I have to transfer the certificates private key to my client.

In this case, the certificate (which at its heart is just an asymmetric key) uses the public key to encrypt the data and the private key to decrypt the data. See my other response above.

Doesn't that defeat the whole purpose of encryption?

No, but it defeats the purpose of backup encryption through SQL Server for your use case. Since you want yourself (1st party) and some random 3rd party to be able to encrypt and decrypt the data, you either need to agree to a set of keys to be used (which would put your other internal items at risk) or use a different method outside of SQL Server backup encryption where you can specify other encryptor options.

The fact that they can't decrypt the data without the required information is the whole point of encryption. In this case, it's doing the job properly it's just your requirements aren't meant to use it in this manner.

Related Question