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:
- I need to be able to create and restore ENCRYPTED database backups "locally", i.e. on servers, which I have full access.
- 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?