2

I'm simulating the situation where I take a backup from instance A and restore it on instance B. I followed the doc to Backup to Disk with Encryption. Since I'm using only my desktop to simulate the whole process, after taking the backup of the database I took a backup of the certificate that was used as follows:

BACKUP CERTIFICATE MyTestDBBackupEncryptCert
TO FILE = N'C:\Databases\MyTestDBBackupEncryptCert.cert';

Then I dropped the MyTestDBBackupEncryptCert certificate and the database from my instance to simulate it is now instance B. I restored the certificate using the command from the Creating a certificate from a file doc with a slight modification where I commented the lines related to the PRIVATE KEY and DECRYPTION options since the certificate was encrypted by the MASTER KEY (well, that's what I understood from the first link):

CREATE CERTIFICATE MyTestDBBackupEncryptCert   
    FROM FILE = N'C:\Databases\MyTestDBBackupEncryptCert.cert'   
    --WITH PRIVATE KEY (FILE = 'c:\Shipping\Certs\Shipping11.pvk',   
    --DECRYPTION BY PASSWORD = 'sldkflk34et6gs%53#v00');  
GO 

The certificate was created, but when I try to restore the database, I get this error:

Msg 15507, Level 16, State 30, Line 33
A key required by this operation appears to be corrupted.
Msg 3013, Level 16, State 1, Line 33
RESTORE DATABASE is terminating abnormally.

The documentation doesn't say how to get the private key needed to properly restore the certificate. How do I get it from SQL Server?

2
  • 1
    Take a look at the BACKUP CERTIFICATE syntax, including the WITH PRIVATE KEY clause
    – AMtwo
    Commented Apr 22, 2022 at 2:54
  • ¬¬ I can't believe I missed that. The syntax says TO FILE = 'path_to_file' for the certificate backup, but only FILE = 'path_to_private_key_file' for the private key instead of TO FILE = 'path_to_private_key_file'. That made me think I should use it to refer to a file that already existed. 3 days gone.... Thanks a lot, @AMtwo. You can write the answer to be accepted if you want to.
    – Ronaldo
    Commented Apr 22, 2022 at 9:41

1 Answer 1

4

The private key is used as part of the BACKUP CERTIFICATE syntax. Per the docs, the syntax is:

BACKUP CERTIFICATE certname TO FILE = 'path_to_file'  
    [ WITH PRIVATE KEY   
      (   
        FILE = 'path_to_private_key_file' ,  
        ENCRYPTION BY PASSWORD = 'encryption_password'   
        [ , DECRYPTION BY PASSWORD = 'decryption_password' ]   
      )   
    ]  

As you discovered, A "simple" BACKUP CERTICIATE without a private key isn't sufficient to be restored for use to decrypt. To make your certificate backup useful for restoring encrypted backups you must backup the certificate WITH PRIVATE KEY.

Once you know this, it's easier to remember, because you need the key to unlock the encryption---but the fact that the "simple" certificate backup is possible without any warning does represent a big pitfall, and also demonstrates the importance of testing your restore strategy, particularly when encryption keys are involved.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.