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

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply