How Does an Audit Trail Become Tamper Evident?

"Our audit records cannot be altered" is a common line in product presentations. When an auditor hears it, the follow up question is always the same: how? This article covers the technical answer to that question and where the answer stops.

Immutable, or Tamper Evident?

The term needs correcting first. Someone with full access to the server can, in the end, do anything. The realistic goal is not to make change impossible under every circumstance but to make it detectable. The industry term for this is tamper evident.

The distinction matters because an immutability claim can be disproved in front of an auditor, and one disproved claim casts doubt on the rest. Saying that any change will be detected and can be verified in a specific way is both true and stronger.

The Hash Chain and Its Limit

The best known method is the hash chain. Each record is digested together with the digest of the record before it, and the result feeds into the next record. Whoever changes a record in the middle has to recompute every digest after it. If the digest is computed with a secret key (HMAC), someone without the key cannot produce a valid chain.

That is also where its limit lies. The application writes the chain, and more often than not the application also verifies it. The key stays inside the organisation; someone who reaches the key can rewrite the past, recompute the chain and get a perfectly self consistent result. To verify, the auditor has to either ask for the key or trust the product's own screen. Neither is independent verification.

A Ledger Enforced by the Engine

The append-only ledger table that comes with SQL Server 2022 moves the protection from the application to the database engine. On that table the engine refuses updates and deletes for everyone, the database administrator included. Every transaction is tied to a block, blocks are linked to each other by digest, and the ledger also keeps which database identity wrote each record and when.

The engine also does the verification: the sys.sp_verify_database_ledger procedure checks the ledger against the digests it is given. The auditor needs no key and no product software for this; a SQL Server copy and the digests are enough.

The Witness Outside

The ledger is inside the database too. Someone who rolls the database back to an older backup, or rebuilds it, can produce a new ledger that is consistent with itself. That is why the ledger digest should be written regularly outside the database, to a place that cannot be changed later: an object store with a retention lock, or a WORM share. The database team should have no delete rights on that store.

The digest file is small and carries no record content: database name, block number, digest and last commit time. If it is signed, it can also be verified that the file came from the organisation. Sending the same event to the security team's SIEM gives a second copy independent of the database team.

Verification: From Claim to Proof

The most important part of the design is a verification the auditor can run on their own. It should answer three questions:

• Did the digest files really leave the organisation at that hour? (the signature and the public key fingerprint the organisation publishes separately)
• Does the ledger in a copy restored from backup agree with those digests? (sys.sp_verify_database_ledger)
• Who wrote the records? (the writer identity kept by the ledger; only the application's own accounts should appear)

The result should not be a binary answer. The ledger verification names the first block that fails, and the writer identity query names the unexpected account. What matters in an audit is seeing what broke, where and by whom.

Limits Worth Admitting Honestly

The trust boundary is the application identity. The ledger shows a record has not changed since it was written; it does not show that what was written is true. Someone holding the application account's password can add a consistent looking record through the write path. The control against this is protecting the application accounts' secrets and matching against independent monitoring records on the target database (DAM or SQL Server Audit).

The lock on the external store is the organisation's responsibility. That digest files are not deleted rests on the store's retention lock. No application can provide that lock; the choice of store and its rights should be written down as part of the installation.

An unrecorded action cannot be protected. Even the strongest ledger cannot protect a record that was never written. A screen left out of scope or a direct intervention that bypasses the product stays outside the ledger. The list of which actions produce records deserves more discussion than the cryptography does.

Frequently Asked Questions

What is the difference between immutable and tamper evident?

Immutable claims the record cannot be changed under any circumstances; against someone with full access to the server that claim usually cannot be defended. Tamper evident is a more honest goal: if a change is made, it surfaces in a provable way. The second is what actually helps in an audit.

What happens if a database administrator deletes the audit trail?

On an append-only ledger table the engine refuses to delete rows. The administrator can roll the database back to an older backup or drop and recreate the table; but since the digests written out do not cover that older state, verification fails, and the dropped table shows up in the ledger history. It does not make deletion impossible; it stops it from going unnoticed.

Is a keyed hash chain not enough?

It stops the cheap interventions but has two weaknesses: the key stays inside the organisation, and verification usually needs either that key or the product's own screen. A ledger enforced by the engine, together with a digest written outside, lets the auditor verify without needing a key or the product.