The problem
The request sounds simple: let an AI assistant answer questions from a company's on-premises SQL Server. The hard part is what the assistant must not be able to do.
The threat model treats the assistant itself as the attacker: it can be prompt-injected, for example by text stored in a table it just read, or simply make a mistake. The goal is that three things still hold: it cannot change the database, it cannot read outside the connected database, and it cannot get data back from a call before that call is recorded.
First, what no gateway fixes: the rows the assistant reads leave the building, to the AI vendor that runs the model. Read-only does not change that. What may be sent, under which personal-data rules (KVKK in Turkey, others elsewhere), is the firm's decision.
The design
mssql-readonly-mcp is a free, open-source (MIT) MCP server in C# on .NET 10. The assistant's client starts it as a local stdio process. It offers three tools: list_tables, describe_table and run_query. Only run_query takes SQL.
Layer 1: a SELECT-only gate built on the parser
Before anything is sent, the SQL text is parsed with ScriptDom, the T-SQL parser library. It must be exactly one plain SELECT, and every node in its syntax tree must be on a hand-written allowlist of node types and function names. A refused call opens no connection.
It is an allowlist, not a keyword filter, because the publicly reported bypass classes are shapes, not words: stacked statements, GO batches, EXEC of a string, DML inside or after a CTE, nested comments. The regression table replays them (71 allowed and 247 refused rows), and a generated test makes sure a node type that a later ScriptDom version adds cannot pass silently.
The parser and the server do not always read text the same way: a GO on its own line is a batch separator to the parser, but SQL Server reads it as a column alias and runs both statements. 335 lexer cases go to a real SQL Server, and the test asserts the split exactly: 269 refused by the gate, 61 allowed and read the same by the server, 5 allowed and refused by the server.
Layer 2: the login, checked at startup
The gate is not trusted alone. At start, the server asks SQL Server what its login may do, and it will not start with a login that fails its permission check: exit code 3, no override. A setup script grants SELECT only on the tables and views the firm lists; a table without a grant is not even listed.
Every run_query call also runs in a transaction that is always rolled back, but that is not counted as a layer: a read can still leave an auto-created statistics object behind.
Limits on every call
Results are capped by rows (200 by default), characters (40,000) and time (30 s per call). Each session sets a lock timeout (5000 ms by default) and low deadlock priority, so the assistant's query loses a deadlock against a session at the default priority. A call that has not ended 0.5 s after its limit is answered as Timeout with no data; its clean-up goes on at SQL Server after the answer.
A tamper-evident audit log
No call returns data before its hash-chained audit line is written. A line is one JSON line with SHA-256 hashes and the hash of the previous line, so the file forms a chain, and it is flushed to disk before the result goes back. If the line cannot be written, the query has still run and the call returns an error and no data. verify-audit exits 0 on an intact file and 1 on a tampered copy.
How it was tested
Test suites. In the last full run before v0.1.1 (2026-11-13, on Windows) all tests passed: Gate 416 and Audit 90 unit tests, and Integration 259 of 259 on each of three SQL Server images (2019, 2022 and 2025). CI runs the suites on every push and pull request. An Extended Events trace of the reader login shows no batch, RPC or login event for refused statements. One integration test is still not stable: it failed in 4 of 19 runs on the 2022 image on earlier code, and the failures whose message was captured after v0.1.0 had a cause other than U-1R (below). It can fail in CI or locally.
Five attack-test runs. A harness spoke JSON-RPC over stdio to the built server, against SQL Server in local containers:
| Run | What it found | What was done |
|---|---|---|
| 1 | No gate bypass, no change to database state, no password in any output. X-1 (LOW): a malformed tool call got no audit line; no SQL ran. | X-1 fixed: such a call is audited before its answer. |
| 2 | 584 outcome rows per engine, same counts on all three; X-1 fix retested. 18 of 19 extra logins refused at start. | The one that passed owns another database: a documented limit. |
| 3 | 788 rows per engine. T-1 (LOW): a timed-out call ended up to about 5 s past its limit; T-2 (LOW): a frozen connection was not bounded. | Answer at the limit plus 0.5 s. |
| 4 | Every single-call Timeout at most 0.56 s past the limit. New: U-1 (MEDIUM), a session could keep a read transaction and a lock after the network recovered; U-2 (LOW). |
The server closes a broken connection the client library left open. |
| 5 | U-1 fixed for every frozen-link, cancel, cut and schema-tool case measured: no session 0.2 to 0.7 s after the release, where the pre-fix build still had it at 60 s. New: U-1R (MEDIUM). | U-1R fixed in v0.1.1, best effort (below). U-2 unchanged. |
A code security review found 1 MEDIUM, 3 LOW and 6 NOTE items; the MEDIUM one (C-1), a time limit per network read instead of per call, was changed to one limit per call. Scans on 2026-11-12, on an earlier commit than the last code changes: gitleaks no leaks, semgrep 0 findings, CodeQL 0 open alerts, no vulnerable NuGet package. The dependency scans reported three advisories, two of them HIGH, in packages under the build-time packaging tool (not in the server); since 2026-11-13 its lock file resolves versions outside their ranges.
The attack tests and the code review are my own, run apart from the writing of the code. They are not a third-party audit, not a penetration test by an outside firm and not a certification. They made no finding of severity HIGH or above. The attack tests were not run again for v0.1.1.
What v0.1.1 changes: U-1R
In attack test run 5, before v0.1.0, on a link throttled to 5 KB/s, 8 of 27 single calls and 21 of 450 calls repeated 50 in a row were answered Timeout but left the reader's session at SQL Server with an open read transaction and a lock. No data leaked, no audit line was missing.
The diagnosis found the case v0.1.0 missed: the client library broke the connection a second time, which left its parser Broken, and v0.1.0 closed only a connection whose parser was Closed. v0.1.1 also closes such a connection, best effort, under the conditions its release notes list. The fix was retested on SQL Server 2022 on Windows only, with the link throttled to 5 KB/s, a 3 s limit and the throttle released 10 s after the limit: in 100 of 100 runs the call was answered Timeout and no run left the session.
Not shown for the fix: SQL Server 2019 and 2025 in this case, the 50 calls in a row, a link throttled for 60 s, a real slow network, Linux and macOS.
Known limits
- Data leaves the building, as said above.
- U-2. Many calls frozen at once in the middle of a row delay every answer: with 60 or 120 such calls at a 5 s limit, the median answer came after 8.2 to 30.2 s.
- One unstable test, above.
- Error text can reveal that a table without a grant exists, and its column names; no row data.
- The login check runs once, at start, and does not inspect everything.
- Other SQL Server versions and Windows installations were not tested.
- The audit chain does not show removal of the newest lines or a full rewrite, unless the last hash is copied somewhere the writer cannot write; the hash has no key. On Windows a second server process on the same path writes a sibling file: verify every file.
How to try it
The release has builds and MCPB bundles for Windows x64, Linux x64 and macOS arm64: https://github.com/oguzhanpisgin/mssql-readonly-mcp/releases/tag/v0.1.1
The README's "Five-minute start" has four steps: download the build and check it against SHA256SUMS; list your tables in scripts/create-readonly-login.sql, run it as an administrator, then check-login; connect your client; ask, then run verify-audit. The install page says which client blocks were run. Not shown yet: the Linux and macOS builds started on their systems, and a v0.1.1 bundle installed in any host. docs/demo.md records one Claude Code run, with a refused UPDATE.
Found a write or a call with no audit line? Please report it privately, as SECURITY.md describes.
Most of the code and the documentation of this project, and this article, were written with AI coding assistants, directed by me.
Free and MIT-licensed: https://github.com/oguzhanpisgin/mssql-readonly-mcp
Top comments (0)