I am setting up an experiment where I only have the public key portion of a certificate on the server. The certificate was generated on another server and I did not restore the private key.
When I try to backup a database with encryption using that certificate I get this error:
Msg 15556, Level 16, State 1, Line 15 Cannot decrypt or encrypt using the specified certificate, either because it has no private key or because the password provided for the private key is incorrect. Msg 3013, Level 16, State 1, Line 15 BACKUP DATABASE is terminating abnormally.
That's SQL Server clearly telling me it needs the private key for encryption.
But why is that?
My understanding is that BACKUP generates a symmetric key on the fly, protects the symmetric key using the public key in the certificate and stores it alongside the backup contents. The symmetric key is then used to encrypt the actual backup contents, since symmetric encryption is usually (always?) faster than asymmetric encryption.
In order to restore the backup I would then need the private key that corresponds to the public key used in encryption. That's expected.
I just don't get why the private key is needed during the backup operation. Didn't find an answer on docs or anywhere else. Can someone explain that or point me to some documentation on why that is the case?