The Polite Catastrophe of 'Read-Only': Connecting Coding Agents to a 300-Table Legacy SQL Database

A developer built a Model Context Protocol (MCP) database gateway that compiles metadata from sys.dm_db_partition_stats and sys.foreign_key_columns into a three-tier hierarchy, after discovering that naive read-only MCP wrappers can cripple a 312-table legacy SQL Server monolith without modifying a byte. The write-up documents second-order failures: an INFORMATION_SCHEMA dump of 433,876 bytes (108,469 tokens), a driver type-precedence trap that pinned all eight cores at 100% by up-converting 20 million VARCHAR rows to NVARCHAR(4000), and read-only lock escalation that locked 40 lab technicians out of cryo-vial records. The gateway's Zoom 0/1/2 design quarantines zero-row husk tables, emits keys, foreign-key edges, row counts and column encodings for a connected subgraph in one turn, and cuts a lazy describe_table loop that accumulated 346,060 bytes (86,515 tokens) over 12 round-trips.

There is a particular, quiet melancholy in watching a sixty-line script bring a fifteen-year-old enterprise database to its knees without modifying a single byte. It nearly always begins on a Tuesday morning. An enthusiastic developer downloads an open-source Model Context Protocol MCP database wrapper, connects it to a 312 -table SQL Server monolith whose original architects retired during the coalition government to press artisanal cider in Somerset, and reassures management that the experiment is entirely harmless. After all, the database login sits strictly inside db datareader . Every incoming query passes a cheerful regular expression checking that the string begins with SELECT . Within four minutes, three separate operational incidents unfold in stately, unblemished silence: INFORMATION SCHEMA , ingesting 433,876 bytes 108,469 tokens of Data Definition Language - roughly equivalent to tipping the entire contents of Companies House onto a barrister's desk and asking whether Tuesday's milk bill has been settled. 0 through 7 pin themselves at 100% utilisation because the Node.js driver bound a fourteen-character ASCII barcode as UTF-16 NVARCHAR 4000 , prompting SQL Server to up-convert twenty million indexed VARCHAR 50 rows at runtime with polite, unhurried malice. 5,000 shared page locks, triggers lock escalation to a table-wide freeze TABLOCK S , and locks forty laboratory technicians out of recording -80 deg C cryo-vial movements. Nothing was mutated. Nobody could work. First-order advice on database MCP tooling - splitting schema dumps into lazy list tables and describe table calls, enforcing SELECT TOP 100 , and revoking write privileges - is already documented across vendor blogs. Those measures prevent juvenile vandalism. What fells mature production estates are second-order database mechanics : archaeological husk tables, multi-turn discovery drag, driver type-precedence traps, and read-only lock escalation. Community tutorials routinely prescribe progressive lazy-loading: call list tables first, then describe table name only on relevant entities. On a greenfield SaaS app with twelve tidy tables named users and invoices , this behaves admirably. Mature institutional databases do not have twelve tidy tables. They resemble geological cliff faces. Alongside 267 active operational tables, a fifteen-year-old schema preserves 45 fossilised remnants of forgotten consultancy engagements: dbo.tbl Archive Dave DoNotTouch 2011 , dbo.tbl Specimen Backup PreMigration 2013 , and the ever-reassuring dbo.tmp Import LegacyBoxes FINAL v2 . These tables possess immaculate primary keys, plausible column names, foreign-key constraints, and precisely zero live rows. Nobody drops them because nobody remembers whether an overnight reporting job from 2014 still inspects their metadata. Handing an agent a flat list tables array of 312 cryptic names without row-cardinality telemetry triggers two expensive failures: tbl Specimen Backup PreMigration 2013 because its name looks clearer than tbl SPEC Aliquot Entity 014 , writes a syntactically flawless join, receives 0 rows , and burns four turns diagnosing an imaginary outage. describe table across eleven candidate tables emits 40,802 bytes of JSON. Because every subsequent turn replays prior tool outputs across the context window, that polite lazy loop accumulates 346,060 bytes 86,515 tokens 12 round-trips. Our gateway compiles metadata from sys.dm db partition stats and sys.foreign key columns or pg stat user tables and pg constraint on PostgreSQL into a three-tier hierarchy: Zoom 0 groups active tables by domain while quarantining zero-row husks out of sight; Zoom 1 emits primary keys, foreign-key edges, index flags, row counts, and character encodings VARCHAR vs NVARCHAR for an entire connected subgraph in one turn; Zoom 2 exposes view DDL only when explicitly authorised. ZOOM 1 SUBGRAPH: domain=INV Custody | active tables=6 | skipped zero row husks=45 dbo.tbl INV Custody Entity 001 rows=1250 :: Entity 001Id PK:INT , ExternalCode IX:VARCHAR 50 , VolumeUnit NVARCHAR 32 , CreatedUtc IX:DATETIME2 3 dbo.tbl INV Custody Entity 010 rows=336041 :: Entity 010Id PK:INT , ExternalCode IX:VARCHAR 50 , ParentRefId FK- Entity 009:IX:INT , VolumeUnit NVARCHAR 32 In 1,987 bytes 497 tokens , the agent receives the live join graph, spots multi-hundred-thousand-row tables before writing careless scans, and skips forty-five empty decoys entirely. CONVERT IMPLICIT Diplomatic Incident Suppose the agent now writes a clean, parameterised lookup against an indexed VARCHAR 50 barcode column: SELECT TOP 25 ExternalCode, VolumeUnit, CreatedUtc FROM dbo.tbl INV Custody Entity 001 WHERE ExternalCode = @code; Every security reviewer applauds the parameter binding. Every fan in the server rack immediately spins to takeoff thrust. JavaScript node-mssql / tedious , Python pyodbc , and most generic MCP wrappers bind string parameters as UTF-16 NVARCHAR 4000 whenever explicit column types are omitted. Under SQL Server's type-precedence rules, VARCHAR ranks below NVARCHAR . Forbidden from narrowing UTF-16 input down to 8-bit Code Page 1252 , the optimiser rewrites the predicate across the index: |--Index Scan OBJECT: dbo . tbl INV Custody Entity 001 . IX ExternalCode , WHERE: CONVERT IMPLICIT nvarchar 50 , ExternalCode , 0 = @code Rather than narrowing one parameter once, SQL Server widens every indexed row at runtime, degrading a 0.2ms Index Seek into a multi-million-row Index Scan . Conversely, forcing all strings to VARCHAR quietly vandalises UTF-16 scientific units NCHAR 181 / U+00B5 micro-litres or -80 deg C , breaking ALCOA+ data integrity. Holding the Zoom-1 column dictionary in memory allows the gateway to inspect target column metadata before dispatch and coerce each parameter to the exact schema encoding and length ceiling: js function coerceBoundParameters boundParams, columnSchemaMap { const coerced = {}; for const name, spec of Object.entries boundParams || {} { const col = columnSchemaMap spec.column ; const val = String spec.value ?? '' ; if col || val.length col.maxLen throw new Error PARAM BOUNDS BLOCKED: ${name} ; if col.isUnicode && / ^\x00-\x7F /.test val { throw new Error NON ASCII ON VARCHAR INDEX BLOCKED: ${name} ; } coerced name = { sqlType: ${col.isUnicode ? 'NVARCHAR' : 'VARCHAR'} ${col.maxLen} , value: val }; } return coerced; } Index seeks stay 0.2ms index seeks, Unicode columns retain full UTF-16 fidelity, and oversized payloads fail before touching the socket. db datareader Is a Polite Museum Guard Treating db datareader or pg read all data as a complete security boundary is rather like hiring a courteous museum attendant who forbids visitors from stealing the oil paintings, yet cheerfully permits them to wedge the fire exits shut on a bank holiday. Even with hardware DENY on INSERT , UPDATE , DELETE , and EXEC , a valid SELECT can still destabilise production via four side channels: READ COMMITTED SNAPSHOT RCSI , a poorly selective SELECT acquiring INSERT and UPDATE commits. OPENROWSET and OPENDATASOURCE coerce SQL Server into outbound network calls and NTLM relay inside internal VLANs. OPTION MAXRECURSION 0 passes naive VIEW DEFINITION allows SELECT OBJECT DEFINITION object id FROM sys.procedures , exposing legacy credentials foolishly hardcoded in 2016. Architectural Invariant: Pair row-versioned snapshot isolation READ COMMITTED SNAPSHOT ON , or PostgreSQL default transaction read only = on with SET LOCK TIMEOUT 2000 , SET DEADLOCK PRIORITY LOW so the agent is always sacrificed ahead of human operators, and pre-flight AST rejection of side-channel constructs. js const BLOCKED VECTORS = /;/, 'MULTI STATEMENT BATCH BLOCKED' , /^ ? SELECT\b|WITH\b /i, 'NON SELECT ROOT BLOCKED' , /\bINTO\s+ @A-Za-z \ /i, 'SELECT INTO MUTATION BLOCKED' , /\b ?:OPENROWSET|OPENDATASOURCE|OPENQUERY|xp dirtree \b/i, 'EXTERNAL ROWSET SSRF BLOCKED' , /\bWAITFOR\s+ ?:DELAY|TIME \b/i, 'WAITFOR POOL EXHAUSTION BLOCKED' , /\b ?:OBJECT DEFINITION|sys\.sql modules|sp helptext \b/i, 'PROC SECRET SCRAPE BLOCKED' , /\bWITH\s \ \s ?:TABLOCKX|UPDLOCK|XLOCK|HOLDLOCK|NOLOCK \b/i,'UNSAFE LOCK OR DIRTY READ BLOCKED' ; function gateReadOnlyQuery sqlText, params, schemaMap { const clean = sqlText.replace /-- ^\n |\/\ \s\S ?\ \//g, ' ' .replace /N?' ?:''| ^' '/g, "''" .trim .replace /;\s $/, '' ; for const pattern, code of BLOCKED VECTORS { if pattern.test clean return { allowed: false, code }; } if /\b ?:TOP\s \ ?\s \d+|FETCH\s+NEXT\s+\d+\s+ROWS \b/i.test clean { return { allowed: false, code: 'MISSING ROW CAP BLOCKED' }; } return { allowed: true, preamble: 'SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET LOCK TIMEOUT 2000; SET DEADLOCK PRIORITY LOW;', bindings: coerceBoundParameters params, schemaMap }; } Notice that NOLOCK is banned alongside TABLOCKX . Just as agents fabricate test passes without cryptographic execution receipts https://dev.to/tomzafred/all-42-tests-passed-they-didnt-why-coding-agents-lie-about-verification-and-how-to-force-32id , permitting dirty reads NOLOCK lets an agent ingest uncommitted numbers that subsequently roll back, polluting its reasoning context with phantom inventory. 312 -Table Schema & Evaluated deterministically on Node.js v24 across a 312 -table legacy schema 267 populated tables plus 45 zero-row husks and 60,000 adversarial pre-flight inspections 5,000 x 12 bypass and type-mismatch vectors : | Architectural Strategy | Turns | Transcript Payload Bytes / Tokens | Zero-Row Husks Exposed | Pre-Flight Gate p50 / p95 | Production Lock & Scan Safety | |---|---|---|---|---|---| | Raw INFORMATION SCHEMA Dump | 1 | 433,876 B / 108,469 tok | 45 100% noise | None | Vulnerable CONVERT IMPLICIT + TABLOCK S | | Naive Lazy describe table Loop | 12 | 346,060 B / 86,515 tok cumulative | 45 3 queried | None | Vulnerable OPENROWSET , CTE, WAITFOR open | | 3-Zoom Subgraph + Typed AST Gateway | 1 | 1,987 B / 497 tok -99.5% | 0 quarantined | 0.0007ms / 0.0011ms | Enforced VARCHAR coerced, LOCK TIMEOUT 2000 , 12/12 halted | Connecting coding agents to a decade-old relational estate requires treating the MCP server as a typed, isolation-aware firewall: quarantine zero-row schema archaeology at Zoom 0, coerce VARCHAR vs NVARCHAR bindings from column metadata, enforce RCSI with DEADLOCK PRIORITY LOW , and authenticate remote gateways via OAuth 2.1 PKCE rather than static plaintext bearer keys. biobanking.co.uk alongside turnkey read-only SQL Server and PostgreSQL MCP gateways for enterprise engineering teams. workbench-guard If your own production database still harbours a cherished DoNotDelete 2011 fossil table or a memorable NOLOCK misadventure, the comments below are open for archaeological comparison.