MSSQL MCP Python Server

GitHub stars GitHub forks GitHub issues Python Version GitHub release Downloads

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:

# 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):

{
  "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:

# 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.

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:

# 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):

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