MSSQL MCP Python Server

<div align="center"> <img src="docs/logo.png" alt="logo"> </div>

MSSQL MCP Python Server

     

This is a MCP (Model Context Protocol) server implementation in Python that safely exposes SQL Server database capabilities to LLM clients.

• If you want a complete guide of how to use, click here!

Quick Start

1. Install Dependencies

cd mssql-mcp-python
pip install -r requirements.txt

# or:
uv sync

2. Configure Database

Create .env file: ```bash

For local SQL Server (Linux/Docker)

export MSSQL_CONNECTION_STRING="Driver={ODBC Driver 17 for SQL Server};Server=localhost,1433;Database=master;UID=sa;PWD=YourPassword123"

Or for Windows Auth

export MSSQL_CONNECTION_STRING="Driver={ODBC Driver 17 for SQL Server};Server=localhost;Database=master;Trusted_Connection=yes" ```

3. Run the Server

# With stdio transport (for MCP clients)
python -m mssql_mcp.cli

# With custom settings
MSSQL_QUERY_TIMEOUT=60 READ_ONLY=true python -m mssql_mcp.cli --log-level DEBUG

# Or with HTTP transport
python -m mssql_mcp.cli --transport http --bind 0.0.0.0:8080

# Build and run
docker build -t mssql-mcp:latest .
docker run -e MSSQL_CONNECTION_STRING="..." mssql-mcp:latest

# Or with Docker Compose (HTTP transport, reads .env)
cp .env.example .env   # then edit connection string
docker compose up -d

4. Test with curl (HTTP mode)

# Health check
curl http://localhost:8080/health

# Readiness check
curl http://localhost:8080/ready

# Server info
curl http://localhost:8080/info

# Prometheus metrics
curl http://localhost:8080/metrics

Available MCP Tools

The server exposes these tools to MCP clients:

1. execute_sql(sql, format="table", timeout=None, max_rows=None)

Execute SELECT queries (or write operations if enabled).

• format: "table", "json" or "csv".
• timeout: per-query timeout (seconds), overrides MSSQL_QUERY_TIMEOUT for slow queries.
• max_rows: per-query row cap, overrides MAX_ROWS_PER_QUERY.

Input: "SELECT TOP 10 * FROM users", format="json"
Output: JSON rows + summary; truncation is flagged explicitly.
        Write statements return the affected-row count.

2. list_schemas()

List all database schemas `` Input: (none) Output: Schema names list ``

3. list_tables(schema, limit=200)

List tables with optional schema filter `` Input: schema="dbo", limit=100 Output: Table list with metadata ``

4. schema_discovery(schema)

Get full schema metadata (tables, columns, types) `` Input: schema="dbo" Output: JSON with detailed column info ``

5. describe_table(table)

Describe a single table: columns, types, nullability, primary keys, descriptions `` Input: table="dbo.users" (schema prefix optional) Output: JSON column metadata for that one table ``

6. get_database_info()

Get server/database metadata `` Input: (none) Output: Database name, version, machine name ``

7. get_policy_info()

Get current security policy settings `` Input: (none) Output: Policy details (allowed operations, limits) ``

8. check_db_connection()

Health check for database connectivity `` Input: (none) Output: Connection status ``

Security Features

✅ Read-Only by Default

• Only SELECT queries allowed unless explicitly enabled
• Writes require ENABLE_WRITES=true + ADMIN_CONFIRM token

✅ SQL Injection Prevention

• Parameterized queries via pyodbc
• Multi-statement query blocking
• Banned keyword detection (DROP, ALTER, EXEC, etc.)

✅ Sensitive Data Protection

• Automatic log redaction (passwords, connection strings)
• Query hashing for safe logging
• No credentials in response bodies

✅ Resource Limits

• Query timeouts (default 30s)
• Row limits (default 50,000 rows)
• Query length limits (50KB)
• Connection pool limits

✅ Audit Trail

• Structured logging with request metadata
• Query metrics and statistics
• Client ID tracking (when provided)

Observability

Prometheus Metrics

Available at GET /metrics (HTTP mode):

• mssql_queries_executed_total — Total queries by tool and status
• mssql_queries_blocked_total — Blocked queries by reason
• mssql_query_duration_seconds — Query latency histogram
• mssql_query_rows_returned — Result set size histogram
• mssql_active_queries — Currently executing queries
• mssql_server_ready — Server readiness (0/1)

Structured Logs

All logs in JSON format (when LOG_FORMAT=json): ``json { "timestamp": "2024-01-15T10:30:00.123456", "level": "INFO", "logger": "mssql_mcp.tools", "message": "Query allowed", "module": "tools", "function": "execute_sql", "line": 42 } ``

Health Checks

• GET /health — Liveness probe (always 200)
• GET /ready — Readiness probe (200 if DB connected)

Common Tasks

Change Log Level

LOG_LEVEL=DEBUG python -m mssql_mcp.cli

Enable Write Operations

ENABLE_WRITES=true ADMIN_CONFIRM=secret python -m mssql_mcp.cli

The app-level ENABLE_WRITES switch is only the first line of defense. The ultimate authority is the permissions of the SQL login you connect as — see credential override below.

Use a Specific SQL Login (credential override)

Each deployment can run under its own SQL login without editing the base connection string. MSSQL_USER / MSSQL_PASSWORD take precedence over any UID/PWD embedded in MSSQL_CONNECTION_STRING: ```bash

Base string holds only driver/server/database; identity comes from these:

MSSQL_USER=reporting_ro MSSQL_PASSWORD=secret python -m mssql_mcp.cli ```

• MSSQL_USER, MSSQL_PASSWORD — override the SQL credentials (ideal for secrets).
• MSSQL_TRUSTED_CONNECTION=true — use Windows/Integrated auth instead (ignores user/password).

Because the connected login's own permissions govern access, connecting with a read-only login enforces read-only at the database level, regardless of ENABLE_WRITES. Conversely, allowing writes requires both ENABLE_WRITES=true and a login that has write permission.

Per-request credentials (remote clients, HTTP transport)

A remote client can authenticate as its own SQL login for the duration of a request by sending credentials as HTTP headers — no server reconfiguration, and it overrides the server's default identity just for that client. Set them in the MCP client config, e.g. .mcp.json: ``json { "mcpServers": { "mssql-mcp": { "type": "http", "url": "http://your-host:8080/mcp", "headers": { "X-MSSQL-User": "your_login", "X-MSSQL-Password": "your_password" } } } } ` Headers (all optional): X-MSSQL-User, X-MSSQL-Password, X-MSSQL-Trusted-Connection (true/false). When absent, the server's default credentials are used. Precedence: request headers → server MSSQL_USER/… → UID/PWD in MSSQL_CONNECTION_STRING`.

Non-ASCII values: HTTP header values must be Latin-1, so a value containing non-ASCII characters (e.g. an accented password) can't be sent raw. For those, send the base64 of the UTF-8 value in the -B64 variant of the header, which takes precedence over the plain one: X-MSSQL-User-B64, X-MSSQL-Password-B64. Example (encode the value): printf '%s' 'pÁsswŐrd' | base64.

Security: credentials travel in headers, so use HTTPS (or a trusted network). Access is still bounded by that login's own SQL Server permissions.

Increase Query Timeout

MSSQL_QUERY_TIMEOUT=120 python -m mssql_mcp.cli

Fix Garbled Non-ASCII Characters (accents, etc.)

Results are decoded using explicit encodings. The defaults work for most SQL Server setups (NVARCHAR is UTF-16LE, VARCHAR is read as UTF-8). If VARCHAR columns use a legacy code-page collation, override the narrow encoding: ```bash

e.g. Central-European legacy VARCHAR data

MSSQL_ENCODING=cp1250 python -m mssql_mcp.cli ```

• MSSQL_ENCODING (default utf-8) — decoding of narrow SQL_CHAR/VARCHAR columns
• MSSQL_WIDE_ENCODING (default utf-16-le) — wide SQL_WCHAR/NVARCHAR decoding and the query/parameter send encoding (SQL Server expects UTF-16LE; sending UTF-8 corrupts accented literals in queries)

Allow External Access (HTTP transport)

By default the server only accepts requests whose Host is localhost or 127.0.0.1 (DNS rebinding protection). To allow access via an external hostname, set ALLOWED_HOST to that host (without port): ``bash ALLOWED_HOST=mcp.example.com python -m mssql_mcp.cli --transport http --bind 0.0.0.0:8080 `` This adds the host to both the allowed hosts and the CORS origins list; local access keeps working.

Run Multiple Instances

python -m mssql_mcp.cli --transport http --bind 127.0.0.1:8080
python -m mssql_mcp.cli --transport http --bind 127.0.0.1:8081  # Different port