mcp-sql-readonly-dotnet

mcp-sql-readonly-dotnet

A Model Context Protocol server in C#/.NET 10 that lets AI agents explore and query SQLite and SQL Server databases, strictly read-only.

What is MCP?

The Model Context Protocol (MCP) is an open standard that lets AI applications discover and call external tools and data sources through a uniform JSON-RPC interface. This server implements it with the official MCP C# SDK, so any MCP client (Claude Desktop, VS Code in agent mode, and others) can inspect a schema and run queries without being able to change anything.

How it works

flowchart LR
    client["AI client<br/>Claude Desktop, VS Code, ..."]
    subgraph server["mcp-sql-readonly (.NET 10)"]
        direction LR
        tools["MCP tools<br/>list_tables, describe_table,<br/>run_query, sample_rows"]
        guard["SqlGuard<br/>one read-only SELECT,<br/>row and time limits"]
        provider["IDatabaseProvider<br/>SQLite | SQL Server"]
        tools --> guard --> provider
    end
    db[("Database<br/>read-only connection or login")]
    client <-->|"JSON-RPC over stdio"| tools
    provider --> db

The client starts the server as a child process and talks JSON-RPC over stdin/stdout. All logging goes to stderr, so the protocol stream stays clean. SQL from the model passes through SqlGuard, and the provider then runs it on a connection that cannot write.

Tools

Tool	Arguments	Returns
list_tables	none	Tables and views with row counts (exact COUNT(*) for SQLite files up to 100 MB, ~estimates from sys.partitions on SQL Server) and the SQL dialect to use
describe_table	table	Columns with type, nullability and default, the primary key and foreign keys
run_query	sql, max_rows = 100	Rows of a single SELECT / WITH ... SELECT as a Markdown table, with a note when truncated
sample_rows	table, n = 5	The first n rows of a table or view

Every tool is annotated readOnlyHint: true and destructiveHint: false. Expected failures (a rejected query, an unknown table, a timeout, a database error such as no such column) come back as tool results with isError: true, so the model can correct itself.

Quick start

Prerequisites: .NET 10 SDK; PowerShell 7 for the optional smoke test.

git clone https://github.com/fbsilvadev/mcp-sql-readonly-dotnet.git
cd mcp-sql-readonly-dotnet
dotnet build -c Release

# Create the fictional demo database (customers, products, orders, order_items and an order_totals view).
dotnet run --project src/McpSqlReadOnly -c Release --no-build -- --seed demo.db

# Optional: speak raw JSON-RPC to the server over stdio (initialize, tools/list, every tool, a rejected write).
pwsh ./scripts/smoke-test.ps1 -NoBuild

--seed never overwrites an existing file. Run the server with no arguments and it waits for an MCP client on stdin; --help lists the options.

Claude Desktop

Add the server to claude_desktop_config.json (Settings > Developer > Edit Config), using absolute paths. On Windows, write paths with forward slashes or escaped backslashes.

{
  "mcpServers": {
    "sql-readonly": {
      "command": "dotnet",
      "args": [
        "run", "--project", "/absolute/path/to/mcp-sql-readonly-dotnet/src/McpSqlReadOnly",
        "--configuration", "Release", "--no-build"
      ],
      "env": {
        "MCPSQL_PROVIDER": "sqlite",
        "MCPSQL_CONNECTION_STRING": "Data Source=/absolute/path/to/mcp-sql-readonly-dotnet/demo.db"
      }
    }
  }
}

For faster start-up, publish once with dotnet publish src/McpSqlReadOnly -c Release -o publish and use "args": ["/absolute/path/to/publish/McpSqlReadOnly.dll"] instead.

VS Code (.vscode/mcp.json)

This repository ships a .vscode/mcp.json that serves the demo database. Build and seed as above, then start sql-readonly-demo with the Start action shown in mcp.json or the MCP: List Servers command. For SQL Server, let VS Code prompt for the connection string so the secret never lands in a file:

{
  "inputs": [
    {
      "type": "promptString",
      "id": "sql-connection-string",
      "description": "Read-only SQL Server connection string",
      "password": true
    }
  ],
  "servers": {
    "sql-readonly": {
      "type": "stdio",
      "command": "dotnet",
      "args": ["run", "--project", "${workspaceFolder}/src/McpSqlReadOnly", "--configuration", "Release", "--no-build"],
      "env": {
        "MCPSQL_PROVIDER": "sqlserver",
        "MCPSQL_CONNECTION_STRING": "${input:sql-connection-string}"
      }
    }
  }
}

Configuration

Variable	Default	Meaning
MCPSQL_PROVIDER	sqlite	sqlite or sqlserver
MCPSQL_CONNECTION_STRING	Data Source=demo.db	ADO.NET connection string, required for SQL Server. SQLite is always opened with Mode=ReadOnly.
MCPSQL_MAX_ROWS	1000	Hard cap on rows returned by any tool; a larger max_rows is capped.
MCPSQL_COMMAND_TIMEOUT	30	Wall-clock timeout per database operation, in seconds.
MCPSQL_LOG_LEVEL	Information	Minimum log level. Logs always go to stderr.

Example: a real run_query call

Output captured from scripts/smoke-test.ps1 against the demo database. The client sends:

{"jsonrpc":"2.0","id":13,"method":"tools/call","params":{"name":"run_query","arguments":{"sql":"SELECT c.name AS customer, c.country, COUNT(DISTINCT o.order_id) AS orders,\n       ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue\nFROM customers AS c\nJOIN orders AS o ON o.customer_id = c.customer_id\nJOIN order_items AS oi ON oi.order_id = o.order_id\nWHERE o.status <> 'cancelled'\nGROUP BY c.customer_id, c.name, c.country\nORDER BY revenue DESC","max_rows":5}}}

And the text content of the result is:

5 rows (truncated) in 10 ms.

| customer | country | orders | revenue |
|---|---|---|---|
| Felipe Moreira | Portugal | 2 | 4917.8 |
| Ana Ribeiro | Brazil | 3 | 3145.2 |
| Daniel Costa | Brazil | 2 | 2456 |
| Julia Santos | Portugal | 2 | 1856.9 |
| Elisa Martins | Brazil | 1 | 1688 |

Note: The result has more than 5 rows and was truncated at max_rows = 5. Add WHERE filters or aggregates, or raise max_rows if you really need more rows.

run_query with DROP TABLE customers returns isError: true and never reaches the database:

Query rejected: Only read-only queries are allowed: the statement must start with SELECT or WITH, but it starts with 'DROP'.

Security model

The layers below are listed from strongest to weakest. SqlGuard is defense in depth; the real protection is a read-only connection or login.

1. Read-only at the database.

   • SQLite: every connection is opened with Mode=ReadOnly, overriding whatever the connection string says, and extension loading is disabled. Tests call the provider directly, bypassing SqlGuard, and show that DELETE, UPDATE, CREATE and DROP still fail with SQLITE_READONLY.

   • SQL Server: there is no read-only connection mode, so connect with a login that can only read, for example:

     CREATE LOGIN mcp_reader WITH PASSWORD = '<strong password>';
     USE Sales;
     CREATE USER mcp_reader FOR LOGIN mcp_reader;
     ALTER ROLE db_datareader ADD MEMBER mcp_reader;
     DENY SELECT ON SCHEMA::hr TO mcp_reader;  -- hide what the model must not see
     

     As a safety net, every SQL Server operation also runs in a transaction that is always rolled back. An integration test shows that an INSERT bypassing the guard is undone.

2. SqlGuard (defense in depth). A pure lexer that understands SQLite and T-SQL quoting and comment rules. It is not a SQL parser.
   • It strips comments and allows exactly one statement starting with SELECT or WITH, including T-SQL statements juxtaposed without ;.
   • It rejects write, DDL, permission and admin keywords (INSERT, UPDATE, DELETE, MERGE, INTO, CREATE, ALTER, DROP, TRUNCATE, EXEC, GRANT, ATTACH, PRAGMA, OPENROWSET, NEXT VALUE FOR, ...) outside literals.
   • Anything it cannot analyse safely is rejected: nested or unterminated comments, unterminated literals, control characters, numbers glued to words such as 1DROP.
   • The provider executes the normalized text that was analysed, never the raw input.
   • An identifier that collides with a blocked keyword can still be used when quoted ("set", [set]).
3. Identifiers are never trusted. A table argument must pass a character allow-list. It is then resolved against the catalog with bound parameters, and only the canonical name is quoted into SQL.
4. Resource limits.
   • Row cap: the reader stops after max_rows + 1 rows, and SQL Server cancels the command instead of draining it.
   • Wall-clock timeout: SQLite uses sqlite3_interrupt, because SqliteCommand.Cancel is a no-op; SQL Server uses CommandTimeout plus cancellation.
   • Size caps: queries up to 20,000 characters, values shortened after 200 characters, responses kept under about 50,000 characters.
5. Secrets. Connection strings come only from environment variables. They are never logged or echoed in error messages.

Out of scope:

• A read-only login can still read everything it is granted, and every result is sent to the AI model provider, so treat this as data egress and scope the login or views accordingly.
• An expensive query can load the server until the timeout fires.
• Entra ID / Managed Identity authentication is not wired up yet (see the roadmap).

Testing

dotnet test -c Release

• SqlGuard: 60+ allow/deny cases, including keywords inside string literals, SELECT ... ; DROP ..., statements hidden behind comments, nested-comment tricks, 1DROP, CTEs and Turkish-culture casing.

• Providers against a temporary SQLite database: schema discovery, truncation, forced read-only mode, writes that bypass the guard, the sqlite3_interrupt timeout and caller cancellation.

• Tools, formatter and CLI: culture-invariant Markdown output, error reporting and row caps.

• End to end: starts the real server over stdio with the SDK's StdioClientTransport and McpClient, then checks initialize, tools/list (names, annotations, JSON schema) and tools/call.

• SQL Server integration (opt-in): creates and drops a throw-away database. For example with LocalDB:

  $env:MCPSQL_TEST_SQLSERVER = 'Server=(localdb)\MSSQLLocalDB;Integrated Security=true;TrustServerCertificate=true'
  dotnet test -c Release
  

global.json runs dotnet test on Microsoft.Testing.Platform, which xUnit v3 requires on the .NET 10 SDK. scripts/smoke-test.ps1 checks the raw protocol without any SDK on the client side.

Project structure

mcp-sql-readonly-dotnet/
|-- .github/workflows/ci.yml      # build and test on Ubuntu and Windows
|-- .vscode/mcp.json              # VS Code MCP configuration for the demo database
|-- scripts/smoke-test.ps1        # raw JSON-RPC smoke test over stdio
|-- src/McpSqlReadOnly/
|   |-- Program.cs                # entry point
|   |-- Hosting/                  # CLI (--seed, --help) and host setup: stdio transport, stderr logging
|   |-- Configuration/            # MCPSQL_* environment variables
|   |-- Tools/                    # MCP tools and the Markdown result formatter
|   |-- Safety/                   # SqlGuard, identifier validation, query limits
|   |-- Data/                     # IDatabaseProvider with SQLite and SQL Server implementations
|   `-- Seeding/                  # fictional demo dataset (portable SQL) and seeder
|-- tests/McpSqlReadOnly.Tests/   # xUnit v3: unit, integration and stdio end-to-end tests
|-- Directory.Build.props         # nullable, latest C#, analyzers, warnings as errors
|-- global.json                   # dotnet test on Microsoft.Testing.Platform
`-- McpSqlReadOnly.sln

Roadmap

• PostgreSQL provider (Npgsql, read-only transactions and roles).
• Row- and column-level masking of PII (e-mail, phone, document numbers) before results reach the model.
• Streamable HTTP transport with authentication, using ModelContextProtocol.AspNetCore.
• Azure SQL with Managed Identity / Entra ID. In Microsoft.Data.SqlClient 7.x this authentication ships in the separate Microsoft.Data.SqlClient.Extensions.Azure package, which this project does not reference yet.
• SQL Server integration tests in CI with a service container.
• An explain_query tool, schema exposed as MCP resources, and schema/table allow-lists.

License

MIT © 2026 Fernando Batista da Silva

---

Fernando Batista da Silva — Computer Scientist, postgraduate in Artificial Intelligence · LinkedIn