The SQL Server Ledger, introduced in v2022, gives you cryptographic tamper-evidence for table data changes. Once enabled, any change is recorded in a hash chain that no one can alter without leaving a detectable break in the chain. Repeat. No one.
This post walks through enabling the Ledger, the two flavors of ledger tables, generating and verifying the database digest, and the gotchas you'll hit if you don't think them through first.
Enabling the Ledger at the database level
The Ledger can be turned on at the database level, or table by table inside any database.
To create a database with the Ledger on by default for every new user table:
CREATE DATABASE LedgerDemo WITH LEDGER = ON; GO
Once the database has LEDGER = ON, you cannot turn it off. There is no ALTER DATABASE statement that removes it. I'd recommend first testing without that commitment by creating a regular database and enable the Ledger only on individual tables.
Updatable ledger tables
UPDATE and DELETE work normally. Behind the scenes, SQL Server maintains a hidden history table that captures every prior version of every row. The current row lives in the main table; the history table preserves what was overwritten or removed. Both are part of the cryptographic chain.
An updatable ledger table is built on top of a system-versioned temporal table - that's how the history is maintained. In a regular (non-ledger) database, you have to specify both SYSTEM_VERSIONING = ON and LEDGER = ON in the WITH clause:
CREATE TABLE dbo.Customer
(
CustomerID int IDENTITY(1,1) PRIMARY KEY,
CustomerName nvarchar(200) NOT NULL,
PaymentTerms nvarchar(50) NOT NULL
)
WITH
(
SYSTEM_VERSIONING = ON,
LEDGER = ON
);
GO
If you skip SYSTEM_VERSIONING = ON, the engine refuses with:
Msg 37349, Level 16, State 1 LEDGER = ON cannot be specified with SYSTEM_VERSIONING = OFF and APPEND_ONLY = OFF.
In a database created with LEDGER = ON, the database default supplies the system versioning for you, so the bare WITH (LEDGER = ON) form works there.
This is the right idea for application data that legitimately changes over time, but you still need a verifiable history.
Append-only ledger tables
UPDATE and DELETE statements are rejected at the engine level. INSERT is the only operation allowed. There is no permission grant that overrides this - not even sysadmin can update or delete a row in an append-only ledger table through normal SQL.
Append-only does not require SYSTEM_VERSIONING = ON. There is no history table because there are no prior versions to track.
CREATE TABLE dbo.AuditEvent
(
AuditID bigint IDENTITY(1,1) PRIMARY KEY,
EventUTC datetime2(7) NOT NULL DEFAULT SYSUTCDATETIME(),
EventType nvarchar(100) NOT NULL,
EventData nvarchar(max) NULL
)
WITH
(
LEDGER = ON
(
APPEND_ONLY = ON
)
);
GO
This is the right idea for audit, evidence, and event logging - anywhere a row should never legitimately change once written.
Attempts to UPDATE return Msg 41887:
Msg 41887, Level 16, State 1 Updates are not allowed for the append only Ledger table 'dbo.AuditEvent'.
Attempts to DELETE return Msg 41888:
Msg 41888, Level 16, State 1 Deletes are not allowed for the append only Ledger table 'dbo.AuditEvent'.
Microsoft's append-only ledger tables overview documents the behavior in detail.
The database digest
The Ledger's tamper-evidence claim depends on the database digest. The digest is a cryptographic hash that represents the state of every ledger table in the database at a given point in time. It is generated from the chain of ledger blocks SQL Server maintains internally and returned as a single JSON column by sys.sp_generate_database_ledger_digest.
EXEC sys.sp_generate_database_ledger_digest; GO
The digest is database-scoped. Even if only one table has Ledger enabled, the digest still represents the database's ledger state as a whole.
To get evidence-grade tamper detection, the digest must be stored outside the database. If both the data and the digest live in the same place, anyone who can corrupt the data can also corrupt the proof. Microsoft's recommended targets are Azure Blob Storage with an immutability policy, Azure Confidential Ledger (which signs and timestamps digests automatically), or on-premises WORM drive. The digest management documentation covers the export options.
Verification
Verification is what turns the chain into evidence. It's a separate, deliberate step you run on a schedule - typically a SQL Agent job - that compares the current state of your ledger tables against the digests you previously exported to tamper-proof storage. If anything has been altered since those digests were captured, the verification fails and tells you which transaction broke the chain.
The system stored procedure is sys.sp_verify_database_ledger. It takes the previously exported digest JSON as input. Below is what a verification call looks like. You'd pull the digest JSON from your tamper-proof storage location rather than declaring it inline, but the rest of the call is the same:
DECLARE @Digests nvarchar(max) =
N'[
{
"database_name": "LedgerDemo",
"block_id": 0,
"hash": "0x...",
"last_transaction_commit_time": "2026-04-27T17:00:00.0000000",
"digest_time": "2026-04-27T17:05:00.0000000"
}
]';
EXEC sys.sp_verify_database_ledger @digests = @Digests;
GO
The hash, last_transaction_commit_time, and digest_time values come from whatever sys.sp_generate_database_ledger_digest returned when you originally exported the digest. You don't construct that JSON yourself - you save what was generated and feed it back in here.
The procedure returns nothing on success. If data tampering has occurred, sys.sp_verify_database_ledger will return an error message, such as:
Failed to execute query. Error: The hash of block xxxx in the database ledger doesn't match the hash provided in the digest for this block.
How often you run verification depends on your audit requirements. Daily is common but for more high-stakes data you might do hourly. The whole point is to detect tampering close to when it happens, not weeks later when you fall across it by chance.
Ledger system views
SQL Server exposes the Ledger's internal state through a small set of system views. I believe these four are the most useful for day-to-day work:
- sys.database_ledger_blocks — one row per ledger block, with the block hash and the previous block hash. This is the chain itself.
- sys.database_ledger_transactions — every transaction that touched any ledger table, with the principal, commit time, and block id.
- sys.ledger_table_history — metadata about ledger tables, including when each was created and dropped.
- sys.ledger_column_history — column-level schema history for ledger tables.
Each updatable ledger table gets a hidden history table and an automatically created ledger view that joins current and historical rows together. Once you create an updatable ledger table, you'll find both with this query:
SELECT
t.name ledger_table_name,
h.name history_table_name,
v.name ledger_view_name,
t.ledger_type_desc ledger_type
FROM sys.tables t LEFT JOIN sys.tables h
ON t.history_table_id = h.object_id LEFT JOIN sys.views v
ON t.ledger_view_id = v.object_id
WHERE t.ledger_type <> 0;
My data is pretty minimal, but at least you can see the Ledger tables with their history tables and views:
The history table holds every prior version of every row that was updated or deleted, but you'd rarely query it directly. The auto-created ledger view is the easier way in. Selecting from the view returns one row per change with the current and historical values side by side, plus columns showing what kind of operation produced each row.
To produce some history to look at, we can run a few changes against the Customer table:
INSERT dbo.Customer (CustomerName, PaymentTerms)
VALUES (N'Acme Corp', N'Net 30'),
(N'Globex Inc', N'Net 45'),
(N'Initech', N'Net 30');
GO
UPDATE dbo.Customer
SET PaymentTerms = N'Net 60'
WHERE CustomerName = N'Globex Inc';
GO
DELETE FROM dbo.Customer
WHERE CustomerName = N'Initech';
GO
That's three INSERTs, one UPDATE, and one DELETE. Now let's look at our auto-created ledger view:
The view has three columns worth knowing: ledger_transaction_id, ledger_sequence_number, and ledger_operation_type_desc (which contains values like INSERT and DELETE). For an UPDATE, you'll see two rows in the view, a DELETE for the old row version and an INSERT for the new one. That's how the engine represents the change internally, and it's how you reconstruct the timeline of any given row.
Common gotchas
Updatable ledger tables require SYSTEM_VERSIONING.
An updatable ledger table is a system-versioned temporal table underneath. In a regular database, you have to specify both SYSTEM_VERSIONING = ON and LEDGER = ON in the WITH clause. In a database created with LEDGER = ON, the system versioning default is applied for you.
You cannot turn it off.
Once a database has LEDGER = ON, the property is permanent. Once a table has the Ledger enabled, the property is permanent. There is no ALTER that removes it. If you decide you don't need it, you're going to be recreating databases and/or tables. Test in a non-production database first.
The digest must live outside the database.
A digest stored in the same database it's verifying is not evidence. Anyone with the means to alter the ledger data can alter the locally stored digest. Export to external storage or the tamper-evidence claim is moot.
Append-only really is append-only.
There is no purge, no retention policy, no archive-and-delete pattern that does not break the chain. Plan capacity. The realistic operational plan is to archive whole digest periods to cold storage rather than delete individual rows.
Ledger does not encrypt.
Ledger guarantees tamper-evidence, not confidentiality. The data is still readable by anyone with SELECT permission. If you need encryption, that's TDE, Always Encrypted, or column-level encryption. Different feature, different problem.
One ledger table still produces a database-wide digest.
The digest covers the ledger state of the entire database, even if only one table has the Ledger enabled. There is no per-table digest. Plan your verification accordingly.
Wrapping up
The Ledger is a small feature surface for what it gives you: cryptographic tamper-evidence with no application changes, no third-party tooling, and no separate audit infrastructure. Enable it on the right tables, export the digest somewhere external, and verify regularly. That's the pattern.
More to Read
Database Ledger internals
Ledger digest management
Database verification
MSSQLTips: Azure SQL Database Ledger getting started
No comments:
Post a Comment