External Digest: What Shows That a Record Is Old?

There are ways to protect an audit trail: a hash chain, a signature, or the append-only ledger table that comes with SQL Server 2022. They all work. But every protection that lives in the same place as the record has one gap it cannot close on its own, and an experienced auditor finds that gap in the first half hour. This article is about what the gap is and how a ledger digest written outside the organisation in advance closes it.

The Gap in Protection That Stays Inside

In a ledger table the database engine refuses updates and deletes for everyone. In a hash chain, change a record in the middle and every digest after it stops matching. That either blocks the cheap interventions or makes them noticeable: inserting a record, quietly correcting a single row, deleting one entry and putting something else in its place.

Here is the gap: someone in control of the whole database can roll back to an older backup, or rebuild the database and rewrite the past. The result is a perfectly self consistent record. A check run from inside reports it as intact, and it is telling the truth, because the question it asks is whether the record is consistent, not whether it is the same record as yesterday's.

This is not a theoretical possibility. It is precisely why audit exists: an organisation's own assessment of its own records is not accepted as sufficient. So a control has to have an answer to the rewrite the past scenario.

What an External Digest Is

An external digest is a very short sentence: "as of this time, the digest of the whole record is this." That sentence is signed and written outside the place where the record lives, at regular intervals.

Months later the auditor takes that old digest and verifies the record against it. If it matches, the record has not changed since the digest was written. If it does not, the past has been rewritten, and that is the strongest tampering indicator an audit trail can carry.

The most important property of this comparison is that it does not require trusting what the organisation says. The auditor compares two things that came into existence at two different times: the digest written out back then, and today's record.

Why It Has to Have Been Written Earlier

All of the external digest's strength comes from when it was written. An auditor who receives both the records and the digests from the organisation on audit day has verified nothing: both may have been produced at the same moment by the same party. Whoever rewrites the past can regenerate the digests as well. The digest that counts is one that can be shown to have sat in a locked store since the day it was written.

So the digest follows its own calendar rather than the audit calendar, and it is written to a place that cannot be changed later. The writing interval sets the resolution of the evidence: a digest written every hour leaves at most a one hour window with no outside witness yet.

Where the External Digest Should Live

The right place is one whose content the organisation's database administrator cannot later change. In practice there are a few options and they complement each other.

• An immutable store. An object store with a retention lock, or a WORM share. A file written there cannot be deleted by anyone until the retention period ends. The database team should have no delete rights on it.
• The SOC or SIEM. The event announcing a published digest also reaches the security team's collector. That is a second copy independent of the database team, and an alert can fire if a digest does not arrive on time.
• The internal audit archive. Period packages and the digest files of that period can be delivered to internal audit on a schedule. Inside the organisation but outside the IT line.

How SQL Change Guard Does It

Audit records live in a SQL Server 2022 append-only ledger table. The ledger digest is taken every hour by default; in an installation where a signing key and an external store folder are configured, the API signs the digest with the organisation's RSA key and writes it to the organisation's immutable store as two files: the digest itself and its signature. The Worker only starts the cycle and has no access to the signing key. Both are configured during installation; if they are not, no external file is written and the readiness screen shows it. At the same moment an event is written to the ledger and also goes to the SIEM; these events cannot be filtered out by the severity threshold, and closing the stream itself reaches the SOC as a separate event.

If a digest cannot be taken within the expected time, the product's readiness screen and the Ledger Status window show it. If the database is restored from an older backup and the restore appears in SQL Server's restore history, the product writes it as a separate event; a break in continuity with the last digest in the external store is a separate event too. The period package lists the digests published in the period and any gaps between them.

The auditor verifies independently of the product: they work on a copy restored from backup in the organisation's own environment (the backup does not leave the organisation), check the signature of the digest files collected from the immutable store with openssl, and verify the copy against those digests with SQL Server's own procedure, sys.sp_verify_database_ledger. The steps are on the evidence verification page.

Setting Up Your Own Routine

Setting up this routine raises five questions, and most of them are not technical.

• Where will the digest be written? Choose a store with a retention lock, with a retention period no shorter than your audit retention period.
• Who has rights on that store? The database team should not be able to delete; changes to those rights should be recorded too.
• Where will the public key fingerprint be published? To check the digest files' signatures, the auditor needs this value from a place independent of the packages.
• Who is alerted if a digest does not arrive? A rule on the SOC side should raise an alarm when no digest arrives within the expected time.
• What happens if there is a gap? A long gap between two digests is not tampering by itself, but it needs an explanation. The period package lists such gaps on its own.

With answers to those five questions, the weakest point in your audit trail is closed. Without them, the record you hold is consistent only with itself.

Frequently Asked Questions

What does the digest carry, does record content leave the organisation?

The digest file carries only the database name, the block number, the digest value, the last commit time and the installation identity. It contains no record content, no script text and no personal data. Keeping it in a store outside the database is therefore not a data disclosure.

Does an external digest replace a timestamp?

No, they solve different problems. An external digest shows that a record has not changed since a given moment and draws its strength from having been written earlier. A timestamp takes the fact that a file existed at that moment from an external authority rather than from the organisation's server clock. SQL Change Guard supports RFC 3161 timestamping on the evidence dossier.

What happens if a digest file is deleted?

In a store with a retention lock it cannot be deleted before the retention period ends; this part of the assurance rests on the organisation's store, not on the product. Without a lock deletion is possible, but the events sent to the SIEM and the digest list in the period package show the missing file. That is why the choice of store matters as much as the mechanism.

Is the product's own verification not enough?

When it produces an evidence dossier the product verifies the ledger against the external digest and writes the result into the file. But that is the product's own assessment. Independent verification is the check the auditor runs, or watches being run, on a copy restored in the organisation's environment, using digest files they collected themselves from the immutable store.