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?



Short Answer

That's how it's currently implemented.

Longer Answer

I didn't look at all the dependencies to see if it would be required (logically it wouldn't be needed to encrypt the data, just decrypt as you've already stated) for all of the other calls, but it is checked and hence the error given how it is currently written and implemented.

If you're worried about having the keys on the same server or multiple servers in order to do backup operations, you may want to look into using a HSM which does integrate with backup encryption.


Your Answer

