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
MyTestDBBackupEncryptCertcertificate 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
DECRYPTIONoptions 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 CERTIFICATEsyntax. 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 CERTICIATEwithout 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.