How do I generate the private key needed to restore the encrypted backup?

April 22, 2022 Andy DBA Stack Exchange 0

clip art of a double-quote character

Question

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?

asked 2022-04-22 by Ronaldo

Answer

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.

answered 2022-04-22 by Andy Mallon