Two new things are running T-SQL in your SQL Server that you did not write. GitHub Copilot in SSMS turns natural language into a query - you click run, it executes. SQL MCP Server, introduced with SQL Server 2025, is an open-source engine that acts as a bridge between AI Agents and your SQL Server, letting chat-based LLMs decide which query to run and run it - no human-in-the-middle. Both are AI Agents acting under a SQL Server login with real permissions to your data.
This post is not about the AI Agent running queries. It's about what happens when something goes wrong. A hijacked prompt redirecting the agent, a tool used outside its intent, compliance or audit failures, insider threats and elevated privilege abuse, and more. When these things happen, we need to know exactly what the agent did. And we need a record of the event that the Agent itself could not have edited or concealed. See OWASP's Top 10 for Agentic Applications for more details.
A regular audit is not going to give us this level of detail - but the SQL Server Ledger will. The Ledger uses cryptographic hashing and chained transaction records — the same concept behind blockchain — to provide a tamper-evident audit trail of all data changes.
What the Ledger is
The Ledger is a security feature introduced in SQL Server 2022 that provides tamper-evidence for your data. It allows us to cryptographically verify data integrity, ensuring that it has not been altered or tampered with by malicious actors or high-privileged users like system administrators, cloud admins -- or even disgruntled DBAs.
How the Ledger works
The Ledger functionality can be enabled for an entire database or for individual tables. Once enabled, every row inserted into a Ledger-configured table gets a cryptographic SHA-256 fingerprint, and each new row's fingerprint is mathematically tied to the previous row's, forming a chain.
A 'database digest' is a cryptographic hash that represents the entire state of all ledger data in the database at a given point in time, whether it's enabled for a couple tables, or the entire database. The database digest is:
- Computed across the whole database — from every transaction across all ledger tables and their history, not table by table.
- Tamper-evident — any data change breaks the chain and changes the digest.
- Stored outside the database — in Azure Blob Storage with a tamper-protection policy, or on-prem WORM drive.
Ledger-enabled table data is not impossible to change, but the changes are said to be impossible to hide. Microsoft's Ledger Overview walks through the mechanics in detail.
Ledger functionality is introduced to tables in two forms:
- Updatable ledger tables allow UPDATE and DELETE while transparently maintaining a hidden, tamper-evident history of every prior version.
- Append-only ledger tables go further — they reject UPDATE and DELETE at the engine level. INSERT is the only operation allowed. No permission grant overrides this. Not even sysadmin can update or delete a row in an append-only ledger table through normal SQL.
Why this fits AI Agent activity
An audit record for an Agent action should never legitimately change. The logged event records what the Agent did, when, under which login, with what prompt and against which object. There is no scenario in which that row should be edited later - that I am aware of.
An append-only ledger table enforces exactly this. A successful INSERT joins the cryptographic chain immediately. UPDATE attempts return Msg 41887. DELETE attempts return Msg 41888. Even if the Agent's login is dramatically over-privileged or a prompt injection drives the Agent to misbehave — the Agent cannot rewrite its own history. Neither can the DBA or a compromised sysadmin without leaving evidence in the next digest verification.
The value is clear: an audit trail no one can edit after the fact -- not the Agent, not the DBA, or the hacker who may have gotten in around them both.
What the Ledger does not solve
It records damage. It does not prevent damage.
If an Agent executes DROP TABLE Customers, the Ledger captures the action, the principal, and the cryptographic proof. It does not stop the drop. Prevention is the job of permissions you've granted or of a wrapper procedure that limits what the Agent can call - or of Microsoft's Agent Governance Toolkit, released in April 2026, which adds a runtime layer that watches what Agents do and enforces policy on their actions. The Ledger is the event logging, not the lock on the door.
Append-only tables grow forever.
Truly forever. There is no purge, no retention policy, no archive-and-delete pattern that does not break the chain. If you put a high-volume Agent in front of an append-only ledger table, plan capacity. The realistic operational pattern is to archive whole digest periods to cold storage rather than delete individual rows.
An authorized destructive action is still authorized.
If your Agent's login is over-privileged, the Ledger logs the destruction faithfully and proves the Agent did what it did. That is useful after the fact, but it does not help much during the incident. Permissions remain the first line of defense. The Ledger does not change this.
A starting point
If Copilot in SSMS is touching production data, if you've deployed or are evaluating SQL MCP Server, or if any LLM-driven path issues T-SQL in your environment, your first two questions should be 'which tables can the Agent reach', and 'have we enabled the Ledger to protect the table data from any user alteration or direct manipulation of the database files'.
I've walked through Ledger setup in Setting Up SQL Server Ledger: From Enablement to Verification. We enable at the database and table level, touch on Updatable and Append-Only, review the digest, verification, and the gotchas. If your shop is putting AI tooling in front of production data and you'd like some help with security, let's talk.
More to Read
Database Ledger internals
Ledger vs. Azure Confidential Ledger
MSSQLTips: Azure SQL Database Ledger getting started
Simple Talk: SQL Server 2022 is a game changer
No comments:
Post a Comment