Letting an AI assistant read SQL Server without letting it write
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…
The objective was to enable an AI assistant to query a company's on-premises SQL Server without granting it the ability to alter the database or access data beyond the connected database. The solution involves a free, open-source (MIT) MCP server written in C# on .NET 10 that the assistant runs as a local stdio process. The server offers three tools: list_tables, describe_table, and run_query, the latter taking SQL queries.
All incoming SQL text is parsed using ScriptDom, a T-SQL parser library, which strictly allows only one plain SELECT statement with nodes on a hand-written allowlist. Any attempt to bypass this gate results in no connection being established. At startup, the server checks the assistant's login permissions, granting SELECT access only to the specified tables and views.
Each call is limited by row count, character count, and execution time, with the server maintaining a tamper-evident audit log that records a hash-chained JSON line for each query, ensuring data integrity. Rigorous testing, including gate verification, login checks, integrations, and attack simulations, has confirmed the server's effectiveness in preventing unauthorized database access and modifications, with no findings of HIGH severity in code security reviews or external dependency scans.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
