SQL Server 查询服务

MCP SQL Server

A secure, production-ready Model Context Protocol server that lets Claude Desktop and Claude Code inspect and query SQL Server databases — read-only by design, with multi-layer SQL injection prevention, table/schema access controls, and a self-learning semantic dictionary.

You:    How many orders did Mario Rossi place this year?
Claude: (explores schema → finds anagra + ordini → runs a validated SELECT)
        Mario Rossi placed 47 orders in 2026, totalling €12,350.
        I saved to the dictionary that "customer by name" maps to anagra.cognome + nome.

Highlights

• Read-only by design — only SELECT statements pass validation; six ordered defence layers block everything else
• Access control — table blacklist with wildcards (sys_*, *_audit) and schema whitelist, enforced on every tool including free-form queries
• 11 inspection tools — tables, schemas, indexes, foreign keys, statistics, views, stored procedures, execution plans, column search
• Flexible output — query results as Markdown, CSV, or JSON
• Semantic dictionary — Claude auto-accumulates business-term → schema mappings per database and reloads them each session
• Connection pooling — thread-safe pool with dead-connection detection and automatic reconnect
• Web manager UI — add, edit, test, and register database connections without touching JSON

Table of Contents

• Quick Start
• Configuration
• Tools
• Resources
• Semantic Dictionary
• SQL MCP Manager
• Security
• Development
• Troubleshooting

Quick Start

Prerequisites

Install

git clone https://github.com/Attilio81/MCP-Sql-Server.git
cd MCP-Sql-Server

Windows: run setup.bat  ·  Linux/macOS: run ./setup.sh

Or manually:

pip install -e .
python test_connection.py   # verifies driver, connection, and MCP install

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql17

brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update && brew install msodbcsql17

Configuration

One server process serves all your databases. Databases are defined in a JSON file passed via --databases; every tool then takes a database parameter (with autocomplete of the configured names). The Manager UI maintains this file for you — including automatic migration of old per-database config entries.

Multi-database mode (recommended)

~/.mcp_sqlserver/databases.json — one block per database, each with its own limits and security rules:

{
  "sales": {
    "connection_string": "Driver={ODBC Driver 17 for SQL Server};Server=srv1;Database=Sales;Trusted_Connection=yes",
    "max_rows": 100,
    "allowed_schemas": "dbo",
    "blacklist_tables": "sys_*,*_audit"
  },
  "warehouse": {
    "connection_string": "Driver={ODBC Driver 17 for SQL Server};Server=srv2;Database=WH;UID=user;PWD=password"
  }
}

Claude Desktop — single entry in claude_desktop_config.json (%APPDATA%\Claude\ on Windows, ~/Library/Application Support/Claude/ on macOS, ~/.config/Claude/ on Linux):

{
  "mcpServers": {
    "sqlserver": {
      "command": "python",
      "args": ["-m", "mcp_sqlserver.server", "--databases", "C:\\Users\\you\\.mcp_sqlserver\\databases.json"]
    }
  }
}

Claude Code:

claude mcp add sqlserver --scope user -- python -m mcp_sqlserver.server \
  --databases "C:\Users\you\.mcp_sqlserver\databases.json"

Connection pools are created lazily — only databases actually used in a session open connections.

"On the warehouse database, show me the Stock table" — Claude passes database: "warehouse" on each tool call.

Single-database mode (legacy)

Still fully supported — pass --connection-string instead of --databases; the database tool parameter becomes optional:

"args": [
  "-m", "mcp_sqlserver.server",
  "--connection-string", "Driver={ODBC Driver 17 for SQL Server};Server=SRV;Database=DB;Trusted_Connection=yes",
  "--allowed-schemas", "dbo"
]

Claude Desktop and Claude Code use separate configuration stores. The Manager UI keeps the Desktop config in sync automatically and registers the server on Claude Code with one click. Details in CLAUDE_CODE_USAGE.md.

Parameters

Per-database keys in databases.json: connection_string (required), max_rows, query_timeout, pool_size, pool_timeout, allowed_schemas, blacklist_tables, dictionary_file (default: <name>_dictionary.md next to databases.json). Lists accept both JSON arrays and comma-separated strings.

CLI arguments (single-db mode / global):

Connection string variants:

# Windows integrated authentication
Driver={ODBC Driver 17 for SQL Server};Server=SRV;Database=DB;Trusted_Connection=yes

# SQL authentication
Driver={ODBC Driver 17 for SQL Server};Server=SRV;Database=DB;UID=user;PWD=password

# Azure SQL with Entra ID
Driver={ODBC Driver 17 for SQL Server};Server=srv.database.windows.net;Database=DB;Authentication=ActiveDirectoryInteractive

Tools

Notes:

• In multi-database mode every tool takes a required database parameter (enum of configured names) selecting which database the call targets.
• execute_query injects TOP {max_rows} when missing and runs under READ COMMITTED isolation. format: csv / json return untruncated values — use them for real data extraction; the default Markdown table truncates long values for readability.
• explain_query uses SET SHOWPLAN_ALL: SQL Server returns the optimizer's plan (operations, estimated rows, subtree cost) without touching data. Useful to diagnose slow queries and verify index usage.
• get_procedures defaults to include_definition: false; combine with name_filter (e.g. sp_orders*) to fetch specific definitions without flooding the context.
• Every tool enforces the blacklist and schema whitelist — including tables referenced inside free-form queries via FROM, JOIN, APPLY, comma-joins, and subqueries.

Example Session

You: Show me all the tables — Claude calls list_tables

You: Why is this report slow? — Claude calls explain_query, spots a table scan, suggests an index

You: Export all 2026 orders as CSV — Claude calls execute_query with format: "csv"

You: Run: SELECT * FROM users; DROP TABLE Orders--

Claude: 🔒 Query not valid: stacked statements (semicolons) are not allowed

Resources

MCP Resources provide read-only context that clients load automatically:

In single-database mode the unnamed legacy URIs (db://schema/overview, db://dictionary, ...) keep working.

Semantic Dictionary

Every business database has a vocabulary the schema alone can't reveal: anagra means nothing, customers does. The semantic dictionary is a per-database Markdown file where Claude accumulates this knowledge as you work — no manual documentation required.

The loop:

1. You ask a business question ("how many sales did Mario Rossi make?")
2. Claude explores the schema and finds the answer
3. Claude saves the non-obvious mapping via update_dictionary and tells you it did
4. Next session, db://{name}/dictionary is loaded automatically — Claude starts already informed

The file has three sections — business entities (term → table), filter aliases ("active" → stato = 'A'), and notable join relationships. It is plain Markdown: edit it by hand or through the Manager's 📖 editor whenever you want.

Each database gets its own dictionary automatically (<name>_dictionary.md next to databases.json) — different domains, different vocabularies. Override per database with the dictionary_file key.

Full guide: docs/manuale-dizionario-semantico.md

SQL MCP Manager

A local web UI to manage all your SQL Server MCP connections from one page.

start-manager.bat        # Windows — or: python -m manager.server

Then open http://localhost:8090. Requires pip install -e ".[manager]" (done by setup.bat).

• Add / edit / delete databases in databases.json; the single sqlserver entry in claude_desktop_config.json is kept in sync automatically (other entries untouched)
• Auto-migrates old per-database config entries into databases.json on first load
• Test any connection before saving; on page load every database is probed in parallel (status rail per card)
• Duplicate a database to onboard a new client on the same ERP layout, copy its connection string, filter the list by name/server/database
• "Registra su Claude Code" registers the multi-database server on Claude Code (claude mcp add --scope user) with one click
• 📖 button opens the semantic dictionary editor for that database
• Auto-detects the Claude Desktop config path on Windows, macOS, and Linux

Security

Defence in depth, applied in order on every query:

1. Length cap (MAX_QUERY_LENGTH, default 100000 chars; --max-query-length / env MAX_QUERY_LENGTH) — payload DoS prevention
2. Null-byte rejection
3. Unicode normalisation — full-width lookalikes folded before matching
4. SELECT-only enforcement
5. Injection patterns — semicolons, comments, UNION, EXEC(), hex/CHAR() encoding, timing attacks
6. Dangerous keywords — word-boundary match on DML/DDL/admin commands (INSERT…SHUTDOWN, xp_*)

Plus table-level enforcement on every tool: identifier format validation, schema whitelist, blacklist wildcards — including tables referenced inside free-form SELECTs (FROM/JOIN/APPLY/subqueries).

Recommended setup:

• Connect with a dedicated read-only SQL login (validation is a second line of defence, not a substitute for DB permissions)
• Prefer Windows integrated auth or Entra ID over passwords in config files
• Whitelist only the schemas Claude needs (--allowed-schemas dbo)
• Blacklist sensitive tables (--blacklist-tables *_audit,password*)

Details and threat model: SECURITY.md

Development

src/mcp_sqlserver/       # the MCP server
├── server.py            # MCP app, tool registration and dispatch, entry point
├── config.py            # CLI args + env vars
├── security.py          # SecurityValidator: query validation, table extraction, access rules
├── pool.py              # thread-safe connection pool with auto-reconnect
├── helpers.py           # Markdown / CSV / JSON result formatting
├── resources.py         # MCP Resources
└── tools/               # one module per tool handler
manager/                 # FastAPI web UI (config manager + connection tester)
tests/                   # unit tests, no database required

pip install -e ".[dev,manager]"
pytest tests/ -v          # 128 unit tests, no DB needed
ruff check src/

All security logic is covered by unit tests (tests/test_security_validator.py). Integration testing requires a real SQL Server — use python test_connection.py.

Contributions welcome — see CONTRIBUTING.md.

Troubleshooting

python -c "import pyodbc; print(pyodbc.drivers())"

Roadmap

• [x] CSV / JSON export
• [x] Execution plan analysis
• [x] Stored procedure inspection
• [x] Single-server multi-database mode (one process, many databases)
• [ ] Query audit log
• [ ] PostgreSQL / MySQL support

License

MIT — see LICENSE.

Built with the Model Context Protocol and pyodbc.