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



  • I'm simulating the situation where I take a backup from instance A and restore it on instance B. I followed the doc to https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/create-an-encrypted-backup?view=sql-server-ver15#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 https://docs.microsoft.com/en-us/sql/t-sql/statements/create-certificate-transact-sql?view=sql-server-ver15#b-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?



  • The private key is used as part of the BACKUP CERTIFICATE syntax. Per https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-certificate-transact-sql?view=sql-server-ver15 , 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2