What it is
Blackbaud delivers our nightly database backups encrypted, and restoring one requires a certificate and its matching private key installed inside SQL Server. Periodically the vendor rotates that key pair, which invalidates the installed certificate and breaks every restore until it is replaced.
This is the work of keeping that chain intact, and it is the smallest piece of work described on this site. It is here because scope and consequence are not the same thing.
Why a small task carries disproportionate risk
An encrypted backup you cannot decrypt is not a backup. It is a file that resembles one.
The failure mode is silent in every direction that matters. Backups continue arriving on their schedule. File sizes look correct. Storage consumption behaves normally. Any monitoring watching for missing or undersized backup files reports everything healthy, because from its perspective everything is. The only signal that the chain is broken is an attempted restore returning a decryption failure, and by definition that attempt happens on a day when something has already gone wrong.
This is the same class of problem as several others documented on this site, a step that produces no error when it fails, and it is worth naming as a class rather than an incident. Systems that fail loudly get fixed. Systems that fail silently get discovered.
The decision chain
flowchart TD
A["Vendor rotates key pair"] --> B["Installed SQL Server<br/>certificate is now invalid"]
B --> C{"Which private key<br/>protection is in use?"}
C -->|"Password-protected<br/>OUR CASE"| D["Import cert + private key<br/>with DECRYPTION BY PASSWORD"]
C -->|"Database Master Key"| E["Create DMK first,<br/>then import"]
D --> F["Test restore against<br/>a real backup file"]
E --> F
F --> G{"Restore succeeds?"}
G -->|Yes| H["Chain intact"]
G -->|No| I["Key material mismatch:<br/>certificate exists but<br/>does not match backup"]
Reading the diagram. The branch in the middle is the part vendor documentation gets wrong for environments like ours, and the test at the bottom is the step people skip because the certificate looks present.
What the vendor’s rotation actually produces
The vendor interface exposes a rotate and unlock option on the backup access account, and the documentation did not make clear what pressing it would do. Before touching anything in production I needed three answers:
- Does it generate only a new password, or new key material as well?
- Does the certificate already installed in SQL Server survive it?
- What exactly has to be re-imported, and in what form?
Rotation produces all three at once: a new certificate, a new private key, and a new password protecting that private key. The previously installed certificate is invalidated immediately. Both the certificate file and the private key file must be re-imported together, using the new password.
Knowing that in advance is the difference between a planned maintenance task and finding out mid-incident that the key material no longer matches the backups.
The re-import
The operation is a drop and recreate rather than an update, because a certificate’s key material cannot be replaced in place:
IF EXISTS (SELECT * FROM sys.certificates WHERE name = 'NightlyBackupCert')
DROP CERTIFICATE [NightlyBackupCert];
GO
CREATE CERTIFICATE [NightlyBackupCert]
FROM FILE = '<path>\NightlyBackupCert.cer'
WITH PRIVATE KEY (
FILE = '<path>\NightlyBackupCert.pvk',
DECRYPTION BY PASSWORD = '<new password from vendor rotation>'
);
GO
SELECT name, subject, expiry_date
FROM sys.certificates
WHERE name = 'NightlyBackupCert';
GO
Two details in that script are worth pointing at.
The private key is imported alongside the certificate, not separately. A certificate imported without its private key installs successfully and verifies successfully, and cannot decrypt anything. This is one of the more plausible ways to end up believing the job is done when it is not.
The final select is a check, not decoration. It confirms the certificate is present with the expected subject and expiry, which catches the wrong file being imported. It does not confirm the key material matches the backups, which is why it is not the last step.
Where the vendor documentation was wrong for our environment
The vendor’s setup guidance describes creating a Database Master Key. Following that instruction would not have produced a visible failure, and it would have been wrong here.
SQL Server can protect a certificate’s private key in one of two ways. A Database Master Key encrypts it at the database level, which suits an environment managing many certificates under one protection hierarchy. The alternative is a password supplied at import time, which is what the vendor’s own key material is built for and what our environment uses.
Adding a master key on top of a password-protected private key would have created an encryption layer protecting nothing, while adding its own obligations: it needs backing up, it needs its own password managed, and it becomes another thing to recover before a restore can proceed. In a recovery scenario, an unnecessary dependency in the decryption chain is a liability rather than defense in depth.
Catching that required understanding what the master key is actually for, rather than following the setup steps as written. Vendor documentation is written for the most common configuration, and the most common configuration is frequently not the one in front of you.
Verification, which is the only part that counts
The success criterion is not that the certificate exists. It is that a real backup file decrypts and restores.
A certificate can be present, correctly named, and show a valid expiry date while being entirely unable to decrypt the backups it was installed for, if the key material does not match. Every check short of an actual restore against an actual backup file confirms something adjacent to what matters.
This connects directly to a rule that appears elsewhere on this site, in the Job Scheduler project: never restore a production database from another environment. Both rules come from the same underlying discipline, which is that recovery procedures have to be exercised on purpose, under controlled conditions, before they are needed under pressure. A backup nobody has restored is a hypothesis.
Why this is on the site at all
This is a small piece of work next to a data warehouse or an extraction platform, and it represents a category the other projects do not: unglamorous operational maintenance where the entire value is that nothing ever goes wrong.
It is also where I first used AI assistance on real production infrastructure rather than on writing or on isolated code problems, which is described further in the journey behind these projects. The questions I worked through, what rotation produces, whether the existing certificate survives, whether the master key was necessary, are exactly the kind of question where a wrong answer is expensive and the documentation is ambiguous.
Where it stands now
Ongoing operational work rather than a project with an end date. It recurs whenever the vendor rotates the key pair. The value of having documented it is that the next rotation is a known procedure with a known verification step, rather than a fresh investigation conducted under whatever time pressure happens to apply that week.