As far as I can see: No, it is not possible to use a Certificate that is protected by a password with the
BACKUP command; the Certificate or Asymmetric Key needs to be protected by the Database Master Key (DMK).
The syntax of the
BACKUP command statement does not have means of specifying a password for either a Certificate or Asymmetric Key. No, you do not
OPEN a Certificate (or even an Asymmetric Key). You only
OPEN Symmetric Keys and Database Master Keys (which are symmetric keys).
When attempting to use a Certificate that has been protected with a password, the following error occurs (emphasis added):
Msg 33101, Level 16, State 1, Line 123
Cannot use certificate 'BackupEncryption', because its private key is not present or it is not protected by the database master key. SQL Server requires the ability to automatically access the private key of the certificate used for this operation.
I had high hopes that creating a Certificate that was protected by the DMK which had its protection by the Service Master Key (SMK) removed would have worked since that usually forces you to first execute
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'; in order to do anything with the DMK, and I did have to do just that in order to create the Certificate, BUT, once the Certificate was created (and yes, I did execute
CLOSE MASTER KEY;), the
BACKUP statement still worked without the DMK needing to be explicitly opened. That is odd since the private key of the Certificate shouldn't be usable without executing
OPEN, and the private key is definitely required for encrypting stuff (trying to do an encrypted Backup with a Certificate that has had its private key removed will fail).
Sooooo, it looks like you will need to handle this outside of SQL Server. As in:
- Execute the backup
- encrypt the .bak file
You can accomplish this in a few ways:
Via stored procedure:
xp_cmdshell to encrypt
SQL Agent Job
- T-SQL step executing
- OS command OR PowerShell step doing the encryption
- SQLCMD.EXE executing