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: understanding what the vendor’s rotation produces, re-importing the new material correctly, and verifying the result before it is needed rather than after.
An encrypted backup you cannot decrypt is not a backup. It is a file. And the failure is silent: backups keep arriving on schedule, storage fills normally, every monitoring signal stays green, and nothing points at a problem until someone attempts a restore, and nobody restores a production database on a good day.
Two questions did the real work here. First, what the vendor’s rotate and unlock option does, which the documentation did not make clear: it produces a new certificate, a new private key, and a new password all at once, invalidating the installed certificate immediately. Second, the vendor’s setup guidance says to create a Database Master Key, and following it would not have visibly failed, yet it would have been wrong for our environment: our private keys are password-protected, so the master key would have been an encryption layer protecting nothing, with its own backup, recovery, and rotation obligations added for no benefit.
The measure of success is one thing only: a real backup file restores. That was confirmed by a test restore, not by checking that the certificate exists, because a certificate can be present and still not match the key material the backup was encrypted with. This is ongoing operational work with no end date. It recurs whenever the vendor rotates the key pair, and the value of having documented it is that the next rotation is a known procedure rather than a fresh investigation.
The technical detail
Our nightly database backups arrive from Blackbaud already encrypted, and a restore works only when SQL Server holds the certificate and the private key that matches it, a chain the vendor’s periodic key rotation breaks until the new material is re-imported correctly. This is the smallest piece of work described on this site, and it is here because scope and consequence are not the same thing.
Why a small task carries outsized risk
A backup that cannot be decrypted has stopped being a backup; it is a file that resembles one.
The failure is silent. Backups keep arriving on schedule, file sizes look correct, and any monitoring watching for missing or undersized files reports everything healthy, because from its perspective everything is. The only signal that the chain is broken is a restore returning a decryption failure, and that attempt happens on a day when something has already gone wrong.
Several other problems on this site belong to the same class: a step that produces no error when it fails. 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 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 must be re-imported, and in what form?
Rotation produces all three at once: fresh certificate, fresh private key, and a fresh password protecting it. 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, not 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 matter.
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. It is an easy way to believe 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 own setup instructions say to create 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 for, not following the setup steps as written. Vendor documentation is written for the most common configuration, and the most common configuration is often 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 unable to decrypt the backups it was installed for, if the key material does not match. Every check short of restoring a real backup file confirms something next to what matters, not the thing itself.
The Job Scheduler project carries a related rule: never restore a production database from another environment. Both come from the same discipline: recovery procedures must 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 instead of 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 the kind of question where a wrong answer is expensive and the documentation is ambiguous.
Where it stands now
Ongoing operational work, not a project with an end date; it comes back each time the vendor rotates the key pair. Documenting it means the next rotation follows a written procedure with a written verification step, instead of a fresh investigation under that week’s time pressure.