Recovering Backup-Encryption Certificates

Document Type: KB

Released: 2024-10-25

Updated: 2026-01-07

Preface

While enabling Backup Encryption from within SQL Server is effectively trivial, care has to be taken to safeguard the 'keys' used to encrypt your organization's Database Backups because:

• Anyone with access to these keys can RESTORE backups of your databases, and
• Without these encryption details, _ you _ WILL NOT be able to RESTORE your encrypted backups. Period.

Restoring Backup-Encryption Certificates with the admindb

Recovery of Encryption Keys is accomplished in 3 main steps - when using the admindb.

A. Gather Security Information

Start by making sure you've got access to:

• the .cer and .key files created as part of the setup for Backup Encryption in your environment.
• the Password used to protect the .key file.

Without all three of the resources above, you will NOT be able to recover your encryption keys (or RESTORE your encrypted backups).

B. Restoring a Backup of your Encryption Certificates

The admindb simplifies restoring Backup Certificates by means of leveraging conventions with file-names and by automating the checks and steps necessary to execute a CREATE CERTIFICATE statement against the master database.

Restoration Template and Instructions

To restore your Backup Encryption Certificate:

1. Copy your .cer and .key files to a location accessible to your target (recovery) SQL Server.
2. Copy + Paste the following code-snippet into SSMS on/against your target (recovery) SQL Server

EXEC [admindb].dbo.[restore_server_certificate]
	@OriginalCertificateName = N'<A. Your Cert Name Here>',
	@CertificateAndKeyRootDirectory = N'<B. Backup Path>',
	@PrivateKeyEncryptionPassword = N'<C. Encryption Password>',
	@MasterKeyEncryptionPassword = N'<D. master key password>',
	@PrintOnly = 0;

3. Replace the following 4x parameter values:
   A. @OriginalCertificateName. Replace <A. Your Cert Name Here> with the original name of your certificate (OR with the NAME of your .cer file (minus the .cer)).
   B. @CertificateAndKeyRootDirectory. Replace <B. Backup Path> with the path to the directory where your .cer and .key files are located - e.g., D:\SQLBackups.
   C. @PrivateKeyEncryptionPassword. Replace <C. Encrryption Password> with the Password that was used to encrypt your Certificate. (Technically, this Password protects access to the .key file - which safegaurds access to the .cer and underlying details.)
   D. @MasterKeyEncryptionPassword. Replace <D. master key password> with, technically, ANYTHING (e.g., hotdog124!!) - or with the Master Key Encryption Password used by your organization (if you're using the same key across multiple servers).
4. Execute the modified/updated code when you're ready.

Note: Creation of a certificate using the code above should, typically, only take a second or two.

Once the operation above is complete, you can begin executing RESTORE operations against your encrypted backups.

Restoration Example

If we assume the following:

• My Encryption Certificate was named "MyEncryptionCert"
• I have copied my .cer and .key to the following location:
  • D:\SQLBackups\MyEncryptionCert.cer
  • D:\SQLBackups\MyEncryptionCert_PrivateKey.key
• and that the Password for my .key file was 8-o79*fbCecA8_8g
• and that I do NOT have an Encryption Master Key (which, again, is NOT a problem) and opted to use K4gpv@!iKAV@W as my NEW key,

I would execute the following code:

EXEC [admindb].dbo.[restore_server_certificate]
	@OriginalCertificateName = N'MyEncryptionCert',  -- i.e., the name of my cert
	@CertificateAndKeyRootDirectory = N'D:\SQLBackups',  -- note that N'D:\SQLBackups\' is fine too... 
	@PrivateKeyEncryptionPassword = N'8-o79*fbCecA8_8g',  -- the REALLY critical bit of the equation
	@MasterKeyEncryptionPassword = N'K4gpv@!iKAV@W', -- again: can be ANYTHING (technically). NOT critical. 
	@PrintOnly = 0;  -- SET this to 1 if you'd prefer to SEE the code this would run (vs executing code)

C. Cleanup + Restoring Database Backups

Once you've completed the steps above, you can start restoring encrypted backups - which should be your main priority when recovering during an actual disaster.

However, once you've COMPLETED disaster recovery (or while you're waiting on backups to restore), there are two additional steps to address.

Remove .cer and .key Files

To keep things tidy and remove the potential for disaster by potentially allowing (seemlingly) duplicate backups of your encryption keys, you should delete the delete the .cer and .key files used for your RESTORE operations (of the Backup Certificate).

Doing this will help ensure that your single, AUTHORITATIVE, backup of your Encryption Certificate stays within your Password Manager so that no one is ever forced to second-guess whether these backups (i.e., .cer and .key files) were left on-box as newer or somehow-authoritative backups of encryption certificate details. (i.e., don't ever provide a reason to doubt your centralized, authoritative, master for these details - your Password Manager).

Backup your (Newly-Created) Certificate (if it'll be used for Backups)

Once you've recovered from a disaster, one of your main priorities is to begin preparing for your NEXT disaster - as in: once you've recovered databases in an emergency scenario and are ready for them to be used in a production capacity again, you NEED to start a new set of FULL backups + a regular backup cadence to protect against other disasters.

If you're going to now use the RESTORED Encryption Certificate that you've just pulled into your new environment for future/ongoing backups, you'll need to create a backup of this Certificate - primarily to PREVENT errors/warnings from being thrown each time you execute a backup using this Certficate. (Or, in other words, SQL Server doesn't really know or 'care' whether you've actually got a legit backup of the Certificate you just deployed; it 'thinks' that the Certificate is 'brand new' and has NEVER been backed up, and - because this puts you in the perfect position for a disaster, it'll continue to 'nag' you each time you create a backup with this Certificate UNTIL the Certificate has been backed up.)

To backup your Certificate, you can execute the following code:

EXEC [admindb].dbo.[backup_server_certificate]
	@CertificateName = N'<name of your cert here>',
	@BackupDirectory = N'D:\SQLBackups',
	@EncryptionKeyPassword = N'Use a bogus pwd here!',
	@PrintOnly = 0;

Specifically:

• specify
  • the name of your cert for @CertifidateName
  • a path for @BackupDirectory
  • and a value for @EncryptionKeyPassword (which can/should be bogus),
• hit execute,
• then navigate to the location specified for @BackupDirectory, and delete the .cer and .key files. (Again: no one can use these for recovery without the password you specified but it would be a DISASTER if these files were somehow (later on) uploaded into your Password Manager as the authoritative master keys.)

Appendix A: Notes

• NOTE: While the instructions in this KB Article are written specifically towards addressing the steps to address when working through a Smoke-and-Rubble Recovery, these instructions can - and IDEALLY SHOULD - be used to set up a cold or even luke-warm recovery server where Encryption Certificates are restored BEFORE a disaster - and where REGULAR testing can then be executed to validate recovery times and expectations.

• NOTE: While the instructions above are specifically focused on restoring Encryption Certificates, admindb.dbo.restore_server_certificate can be used to restore ANY server-level SQL Server Certificates (Backup Encryption, TDE, Mirroring/AG Endpoint Encryption, etc.) using the convention of <CertificateName>.cer and <Certificate_Name>_PrivateKey.key for file-names.

  • Specifically, additional (optional) parameters for dbo.restore_server_certificate can also be used to BYPASS this convention by allowing for the full, explicit, path for both .cer and .key files AND to specify a NEW/DIFFERENT Certificate Name during creation.

• BEST PRACTICE: Recommendations for storage of your encryption keys are that they're stored in a safe, secure, password manager (ideally a password manager solution with auditing - to keep tabs on who has accessed keys) while simultaneously juggling the need to keep the number of people who have access to these keys to a minimum vs addressing the fact that, in an emergency, you'll need to have these keys/details accessible to WHOEVER ends up being tasked with Disaster Recovery.

• BEST PRACTICE: For (planned) migrations or situations where you know you're going to be on your new/recovery SQL Server for a long time, a better option than keeping your older Backup Certificate in use for Backups is to make a 'clean break' and create a NEW Backup Certificate for your new environment.

Appendix B: Key Concepts for Restoring SQL Server Encryption-Backup Certificates

General concepts for restoring a certifcate are pretty simple:

• After creating a new Encryption Certificate (i.e., on your production SQL Server(s)), you'll NEED to take a backup of your Backup Encryption Certificates for Disaster Recovery (and DR testing/validation) purposes.
• Backing up Certificates from within SQL Server is done (typically) by 'dumping' a .cer file + a .key file with security information that 'locks' access into the .cer file by means of a password (required to open the .key file - which, in turn is trusted with being able to open the corresponding .cer file that they .key file protects.)
• As such, to restore a Certificate within SQL Server, you simply need to:
  • Stage the .cer and .key file into a location that your target SQL Server can access (I typically put these in the Backups directory - i.e., right where backups will be created).
  • You'll also, obviously (and critically) need to have the PASSWORD that was used to seal/protect your .key file.
• Once you've assembled your .cer, .key, and password - you'll need to run some (fairly simple) T-SQL to create a Certificate on your target SQL Server.
  • However, before any NEW Certificate can be created on a given SQL Server, a Master Encryption Key will need to be created on the Server in question.
  • Importantly enough: while the notion of a 'Master Encryption Key' sounds security-critical and quite important (and is - in its own way), this Master Encryption Key does NOT a) need to be the same as the key on your ORIGINAL Server(s) (where your original Encryption Certificate and .cer/.key files were created) nor, b) does this key ever need to be backed up for recovery purposes. (While overly-simplified - to the point of being nearly inaccurate - you can think of this Master Encryption Key kind of like being the 'seed' for encryption on your SQL Server.)
• Once you have restored (i.e., CREATE'd from a .cer + .key) an Encryption Certificate on your new/target SQL Server, you'll want/need to execute a BACKUP of this certificate IF you plan on using the Certificate in question to CREATE NEW BACKUPs of databases on the server in question. IF you're only ever going to to use the Certificate to RESTORE encrypted backups, then you can skip this step. Otherwise, IF you use this newly restored Certificate for future backups, the BACKUPS will work (and be fully encrypted) BUT you'll see warnings about the Backup Encryption Certificate having NEVER been backed-up. (And, given how CRITICAL this Backup-Encryption Certificate is for Disaster Recovery, it's a GOOD THING that SQL Server warns you about this potential disaster-in-waiting - only, in our particular case, the Certificate in question HAS, actually, been backed up (just not on this particular SQL Server instance).)
  • Note that IF you do end up backing up this newly restored Certificate, you can use a 'bogus' password for .key file protection and should (strongly recommended) DELETE the .cer and .key files created by this backup process.
• Finally, once you have completed restoration processes for your Certificates, the .cer and .key files used for recreation of these files should be DESTROYED (i.e., removed) from the SQL Server they've just been restored on (arguably, no one can use these files to recreate the certificate elsewhere without the password but we're NOT trying to protect against this concern by removing these files; instead, removing these files helps ensure that the sole, only, copy of these files is in a single, centralized, location to help simplify DR operations (vs requiring admins to try and log into older/trashed machines that might no longer be functional as a means of trying to regain certificate details)).