Australian cloud for business software — built and run in Sydney since 2003

+61 2 8282 5656
support@netleverage.com
Talk to an engineer
Talk to an engineer
NetLeverage
NetLeverage

SQL Server backup encryption: keeping the certificates your backups need

Technical article
Backup & Recovery

Originally published:

About this article: AI-assisted writing, reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.

A SQL Server backup file can be encrypted as it is written, so that a copy left on a share, a USB drive or an offsite store is unreadable to anyone who finds it. That protection works in both directions. Without the right certificate, the people who need the backup cannot read it either.

Microsoft's documentation is blunt: "Without the certificate or asymmetric key, you can't restore the backup, rendering the backup file unusable." This guide focuses on certificate-encrypted backups: where the certificate and private key must be when you restore, what expiry and renewal do, and how to audit their dependencies. Asymmetric keys held by an Extensible Key Management (EKM) provider use a different recovery path and are identified separately below.

Why it matters

Encrypted backups are usually adopted for good reasons, often because backups leave the building (our reading). The failure comes later: the server holding the certificate fails, or is rebuilt, or the certificate is renewed, and the only copy of the certificate needed by last year's backups has gone (our reading). Because the backup itself looks normal and the backup job reports success, nobody notices until a restore is attempted.

How native backup encryption works

Microsoft Learn's backup encryption overview says: "Starting in SQL Server 2014 (12.x), SQL Server has the ability to encrypt the data while creating a backup." To do so, "you must specify an encryption algorithm, and an encryptor to secure the encryption key." The supported algorithms are listed as "AES 128, AES 192, AES 256, and Triple DES", and the encryptor is "A certificate or asymmetric Key". For asymmetric keys, "only asymmetric keys residing in the EKM provider are supported", meaning an Extensible Key Management provider.

The prerequisites are a database master key in the master database, which "is a symmetric key that is used to protect the private keys of certificates and asymmetric keys that are present in the database", and the certificate or key itself. Microsoft's sample statement is:

BACKUP DATABASE [MYTestDB]
TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\MyTestDB.bak'
WITH
 COMPRESSION,
 ENCRYPTION
 (
 ALGORITHM = AES_256,
 SERVER CERTIFICATE = BackupEncryptCert
 ),
 STATS = 10

This is shown so you can recognise the option in your own jobs; it writes a backup, so it is not one of the read-only checks below.

Limitations worth knowing before you rely on it

The same page lists restrictions:

  • "SQL Server Express and SQL Server Web don't support encryption during backup. However restoring from an encrypted backup to an instance of SQL Server Express or SQL Server Web is supported."
  • "Previous versions of SQL Server can't read encrypted backups."
  • "Appending to an existing backup set option isn't supported for encrypted backups."

The second point matters for recovery planning: an encrypted backup can only be restored to a version that can read it, which constrains where an emergency restore can go (our reading).

Not the same as Transparent Data Encryption

Transparent Data Encryption (TDE) encrypts the database files at rest. Backup encryption encrypts the backup file. They are separate features with separate keys (our reading of the documentation). Microsoft notes that "Encryption can also be used for databases that are encrypted using TDE", and recommends: "If the database is TDE enabled, choose different certificates or asymmetric keys for encrypting the database and the backup to increase security."

If both features use certificates, a restore needs both corresponding certificates and usable private keys on the target instance. EKM-backed encryption instead requires the relevant provider and key access; certificate imports do not replace that dependency. The overview says: "If you're restoring a backup from a TDE encrypted database, the TDE certificate should be available on the instance you're restoring to." So the custody problem in this guide doubles for TDE databases (our reading).

What a restore needs

The overview describes the restore side: "SQL Server restore doesn't require any encryption parameters to be specified during restores. It does require that the certificate or the asymmetric key used to encrypt the backup file is available on the instance that you're restoring to." It adds that "The user account performing the restore must have VIEW DEFINITION permissions on the certificate or key", and that when restoring to a different instance, "you must make sure that the certificate is available on that instance."

For the certificate-based path, its sequence for restoring to a new location is:

  1. BACKUP CERTIFICATE in the old database;
  2. CREATE MASTER KEY in the new location's master database;
  3. CREATE CERTIFICATE from the backup certificate imported to the new server;
  4. restore the database.

To decrypt, the new instance needs the certificate's private key, not just its public part (our understanding). The BACKUP CERTIFICATE documentation shows the WITH PRIVATE KEY clause, and says "When you back up the private key to a file, encryption is required." The password protecting the private key file "isn't the same password that is used to encrypt the private key of the certificate in the database." That file password becomes something else you must keep (our reading).

Two further details from that page:

  • "When performing a backup, the files will be ACLd to the service account of the SQL Server instance. If you need to restore the certificate to a server running under a different account, you'll need to adjust the permissions on the files so that they're able to be read by the new account."
  • From SQL Server 2022 (16.x), "certificates with private keys can be backed up or restored directly to and from files or binary blobs" in PKCS #12 or PFX format.

Where to keep the certificate

Microsoft's caution: "It's very important to back up the certificate or asymmetric key, and preferably to a different location than the backup file it was used to encrypt." Its recommended practices add: "Create a backup of the encryption certificate and keys to a location other than your local machine where the instance is installed", and "consider storing a backup of the certificate or key to an off-site location."

The guide to creating an encrypted backup states the retention rule: "Save the certificate used to encrypt the database encryption key for as long as you need to save the backup." If you keep backups for seven years, you keep that certificate, its private key file and its password for seven years (our reading).

Expiry, renewal and rotation

Certificates have an expiry date. The CREATE CERTIFICATE page says that if none is specified, "EXPIRY_DATE is set to a date one year after START_DATE." For backups specifically: "Backup with Encryption using certificates also checks the expiration date and won't allow a new backup to be created with an expired certificate, but will allow restores with an expired certificate."

So expiry stops new encrypted backups but does not by itself block restoring old ones (as the page states). The danger is in how people respond to expiry. The overview warns: "the certificate shouldn't be renewed on expiry or changed in any way. Renewal can result in updating the certificate triggering the change of the thumbprint, therefore making the certificate invalid for the backup file." It says the restore needs "the original certificate used when the backup was taken with the matching thumbprint".

A practical pattern, consistent with that guidance, is to create a new certificate for new backups when the old one nears expiry, and keep every old certificate with its private key for as long as any backup encrypted with it is retained (our recommendation). For each certificate-encrypted backup set, the header/history records its backup-encryptor thumbprint. This does not enumerate a separate TDE dependency, and an EKM-encrypted set maps to an asymmetric key instead. Record the encryptor type as well as its thumbprint. For EKM, retain and test the provider configuration, external key, credentials/access and provider-supported recovery procedure; exporting an unrelated SQL certificate is not sufficient.

Availability groups

The overview notes: "Availability Group database backups are typically performed on the preferred backup replica. If you restore a backup on a replica other than where the backup was taken from, ensure that the original certificate used for backup is available on the replica you're restoring to." A certificate that exists only on one replica is a single point of failure (our reading).

Read-only checks

These queries read catalogue and history information without changing it. Have the DBA run them in an adequately authorised audit context with visibility of the relevant master certificate metadata and msdb backup history. Catalogue views can silently hide objects the login does not own or have permission to see; incomplete output need not raise an error. Record the instance, audit identity, visibility scope and any errors before interpreting missing rows.

Which certificates exist on this instance, and when were their private keys last exported?

SELECT name, thumbprint, start_date, expiry_date, pvt_key_last_backup_date
FROM master.sys.certificates;

Microsoft describes thumbprint as "SHA-1 hash of the certificate", and pvt_key_last_backup_date as "The date and time the certificate's private key was last exported." A NULL in that column for a certificate used by backups suggests its private key has never been exported from this instance (our reading); it may still exist elsewhere if it was imported.

Which encryptor type and thumbprint did each backup use?

SELECT database_name, backup_finish_date, type,
       key_algorithm, encryptor_type, encryptor_thumbprint
FROM msdb.dbo.backupset
ORDER BY backup_finish_date DESC;

The backupset documentation describes encryptor_thumbprint as "The thumbprint of the encryptor which can be used to find certificate or the asymmetric key in the database. In the case where the backup was not encrypted, this value is NULL."

Which certificate-encrypted backups have no visible matching certificate?

SELECT b.database_name, b.backup_finish_date, b.encryptor_thumbprint
FROM msdb.dbo.backupset AS b
LEFT JOIN master.sys.certificates AS c
  ON c.thumbprint = b.encryptor_thumbprint
WHERE b.encryptor_type = N'Certificate'
  AND b.encryptor_thumbprint IS NOT NULL
  AND c.thumbprint IS NULL;

Rows here are investigation candidates, not proof that a certificate is physically absent. First confirm metadata visibility and audit the correct instance; then locate the exact thumbprint in the protected certificate store if it is absent. A matching public certificate alone does not prove that its private key is present, usable, exported or recoverable elsewhere. Only a successful isolated restore verifies the full dependency chain. This query deliberately excludes Asymmetric Key rows, which require the EKM recovery audit described above.

What does a backup file say about itself?

RESTORE HEADERONLY FROM DISK = N'path\to\file.bak';

This reads the backup header and returns KeyAlgorithm, EncryptorThumbprint and EncryptorType for each backup set, without restoring anything. The documentation states that it "requires CREATE DATABASE permission". Use it on copies held offsite to identify both the encryptor type and thumbprint; route certificate and asymmetric-key results to the appropriate custody check.

These checks are partial. msdb history covers backups taken by this instance and may have been pruned; it does not prove the certificate files held elsewhere are complete or that their passwords are known (our reading).

Safe restore rehearsal

Run this in an authorised, isolated test environment with two test SQL Server instances and a synthetic database (our recommendation).

  1. Create a test database with sample rows, and record a row count and a checksum query result.
  2. Create a database master key and a test certificate in master on the first instance, then take an encrypted backup.
  3. Back up the certificate with its private key to files, protected by a password, and store them separately.
  4. Restore on the second instance without the certificate, and record the error. This shows what a missing certificate looks like in your version.
  5. Import the certificate on the second instance with CREATE MASTER KEY and CREATE CERTIFICATE from the files, then restore and compare row count and checksum.
  6. Rotate: create a second certificate, take another backup, and confirm both backups map to the expected thumbprints with the queries above.

Diagnosing what you find

  • Restore fails asking for a certificate: the certificate was never imported on this instance; a different certificate with the same name was imported (thumbprint differs, possibly after renewal); or the restoring login lacks VIEW DEFINITION.
  • Certificate import fails: wrong private key password; the files are not readable by the service account; or the target instance has no database master key.
  • Restore fails on version: the backup was taken on a newer version than the target can read; encrypted backups cannot be read by previous versions.
  • New backups stop: the certificate has expired; the backup job's login has lost permission on the certificate; or the edition does not support encryption.

Have a qualified database administrator review the plan before applying any of this to production, and keep certificate and password handling inside your normal secrets process.

Worked example (illustrative)

The following is invented. A company encrypts its nightly SQL Server backups and keeps them for three years offsite. The server fails and is rebuilt from installation media. The new instance has a new master key and no certificates.

The DBA runs RESTORE HEADERONLY against an offsite copy and records its encryptor thumbprint. The certificate store holds one certificate file under the expected name, but its thumbprint differs: the certificate was re-issued last year and the old one was not kept. Backups from the last ten months restore; older ones cannot. The DBA records the gap, adds every certificate thumbprint to the recovery inventory, and changes the procedure so that a new certificate is created on expiry and the old one is retained with its private key and password.

Risks and limits

  • Backup encryption protects backup files at rest; it does not protect a running database or replace access control.
  • Certificate files and their passwords are as sensitive as the data. Store them under your secrets process, separately from the backups.
  • The relationship between expiry and restore is as Microsoft documents it; check the behaviour on your exact version.
  • No query or restore in this guide was run in a lab. Test every step in an isolated environment first.

Sources

All accessed 14 November 2026.

Related future topics

  • The SQL Server service master key: what breaks on new hardware without it
  • TDE and backup encryption together: planning both certificates
  • Storing database certificates and passwords so they survive the server

This article was originally published on .