MSSQL MCP Server — Standalone

A self-contained Model Context Protocol (MCP) server that exposes SQL Server metadata and read-only query execution as a structured HTTP API. It is designed to be consumed by AI orchestration layers (e.g. LangGraph, custom agents) that need reliable, safety-gated access to a SQL Server database.

What It Does
Architecture
Project Structure
Quick Start (Docker)
Quick Start (Local Python)
Configuration Reference
Allowlist Policy
Endpoints
MCP Resources
Error Handling
OpenTelemetry Tracing
Running Tests

What It Does

The MCP server acts as a safe, policy-enforced gateway between an AI agent and a SQL Server instance. It provides:

| Capability | Details | |---|---| | Schema introspection | List databases, schemas, tables, columns, and foreign keys | | Read-only SQL execution | Run SELECT-only queries with bounded pagination | | SQL safety validation | Deterministic risk scoring before any query is executed | | Allowlist policy | Restrict accessible databases/schemas/tables via YAML config | | Audit & feedback persistence | Record structured audit events and user feedback into SQL Server | | Metadata caching | In-memory TTL cache reduces redundant introspection roundtrips | | OpenTelemetry tracing | Optional OTLP export compatible with Arize Phoenix and Jaeger |

Architecture

AI Agent / Orchestrator
        |
        |  HTTP (JSON)  POST /list_databases, etc.
        v
+----------------------------------+
|         mcpo REST proxy          |
|         (port 8081)              |
|                                  |
|  OpenAPI/REST  -->  MCP client   |
+----------------------------------+
        |
        |  MCP over SSE
        |  GET  /sse
        |  POST /messages/
        v
+----------------------------------+
|      FastMCP Server              |
|      (port 8082, internal)       |
|                                  |
|  10 tools  -->  MetadataService  |
|               +-- schema-        |
|               |   introspection  |
|               +-- sql-validator  |
|               +-- policy layer   |
|                                  |
|  6 MCP resources (schema, docs)  |
|  Settings loaded from env vars   |
+----------------------------------+
        |
        |  pyodbc / ODBC Driver 18
        v
   SQL Server (any edition)

mcpo is a lightweight proxy that wraps any MCP server and exposes its tools as a standard OpenAPI/REST API. This means you can call the server with plain HTTP POST requests and browse an interactive Swagger UI at http://localhost:8081/docs no MCP client library required.

Three internal packages are bundled alongside the server:

| Package | Role | |---|---| | packages/shared-types | Pydantic request/response models shared across the surface | | packages/schema-introspection | Low-level pyodbc SQL Server introspection queries | | packages/sql-validator | Deterministic SQL risk scorer using sqlglot parse trees |

Project Structure

mssql-mcp-server-standalone/
+-- app/
|   +-- main.py              # FastMCP server, all tools and resources, entry point
|   +-- config.py            # McpSettings (pydantic-settings, env prefix MCP_)
|   +-- metadata_service.py  # Business logic: wraps introspection + policy + cache
|   +-- metadata_cache.py    # In-memory TTL cache
|   +-- policy.py            # Allowlist policy loader and enforcement
|   +-- tracing.py           # OpenTelemetry setup
+-- packages/
|   +-- shared-types/        # Shared Pydantic models
|   +-- schema-introspection/# pyodbc-based SQL Server introspection
|   +-- sql-validator/       # sqlglot-based SQL risk scorer
+-- tests/
|   +-- test_validator_tools.py
+-- Dockerfile
+-- docker-compose.yml
+-- pyproject.toml
+-- .env.example
+-- allowlist-policy.example.yaml

Quick Start (Docker)

1. Configure environment

cp .env.example .env

Edit .env and set at minimum:

MCP_SQLSERVER_HOST=your-sql-server-host
MCP_SQLSERVER_DATABASE=YourDatabase
MCP_SQLSERVER_USER=readonly_user
MCP_SQLSERVER_PASSWORD=your-password

2. Build and run

docker compose up --build

Two containers start:

fastmcp-app — the FastMCP server on internal port 8082
mcpo-proxy — the REST proxy on port 8081 (public)

mcpo-proxy waits for fastmcp-app to pass its healthcheck before starting.

3. Verify

Browse the interactive Swagger UI:

http://localhost:8081/docs

Or call the health check tool directly:

curl -s -X POST http://localhost:8081/health_check \
  -H "Content-Type: application/json" \
  -d '{}'

Quick Start (Local Python)

Requires Python 3.11+ and Microsoft ODBC Driver 18 for SQL Server.

Note: Running locally starts the FastMCP server only (port 8082). To get the OpenAPI/REST layer, run mcpo separately pointing at http://localhost:8082/sse.

1. Create and activate a virtual environment

python -m venv .venv
# Windows
.venv\Scripts\activate
# Linux / macOS
source .venv/bin/activate

2. Install packages

pip install -e packages/shared-types -e packages/schema-introspection -e packages/sql-validator -e .

3. Set environment variables

# Windows PowerShell
$env:MCP_SQLSERVER_HOST = "localhost"
$env:MCP_SQLSERVER_DATABASE = "YourDatabase"
$env:MCP_SQLSERVER_USER = "readonly_user"
$env:MCP_SQLSERVER_PASSWORD = "your-password"

# Linux / macOS
export MCP_SQLSERVER_HOST=localhost
export MCP_SQLSERVER_DATABASE=YourDatabase
export MCP_SQLSERVER_USER=readonly_user
export MCP_SQLSERVER_PASSWORD=your-password

Or copy .env.example to .env and load it manually.

4. Run

python -m app.main

The server listens on 0.0.0.0:8082 using SSE transport (GET /sse, POST /messages/).

Configuration Reference

All settings use the MCP_ environment variable prefix.

SQL Server Connection

| Variable | Default | Description | |---|---|---| | MCP_SQLSERVER_HOST | sqlserver-host | Hostname or IP of the SQL Server instance | | MCP_SQLSERVER_PORT | 1433 | SQL Server port | | MCP_SQLSERVER_DATABASE | master | Default database to connect to | | MCP_SQLSERVER_USER | readonly_user | SQL login username | | MCP_SQLSERVER_PASSWORD | change-me | SQL login password | | MCP_SQLSERVER_CONNECT_TIMEOUT_SECONDS | 10 | Connection timeout (1-120) | | MCP_SQLSERVER_QUERY_TIMEOUT_SECONDS | 30 | Query timeout (1-300) |

Allowlist Policy

| Variable | Default | Description | |---|---|---| | MCP_ALLOWLIST_ENABLED | false | Enable database/schema/table allowlist enforcement | | MCP_ALLOWLIST_POLICY_PATH | (none) | Absolute path to allowlist YAML file (required when enabled) |

Metadata Cache

| Variable | Default | Description | |---|---|---| | MCP_METADATA_CACHE_TTL_SECONDS | 300 | TTL for cached introspection results (1-86400) | | MCP_METADATA_MAX_TABLES | 200 | Max tables returned per listing call (1-10000) | | MCP_METADATA_MAX_COLUMNS | 1000 | Max columns returned per table (1-50000) | | MCP_METADATA_MAX_FOREIGN_KEYS | 2000 | Max foreign keys returned (1-100000) |

Query Execution Limits

| Variable | Default | Description | |---|---|---| | MCP_EXECUTION_DEFAULT_ROW_LIMIT | 100 | Default row limit when caller does not specify | | MCP_EXECUTION_MAX_ROW_LIMIT | 1000 | Hard cap on rows returned per query (1-10000) |

SQL Validator Risk Thresholds

Risk scores are integer values computed from the sqlglot parse tree. Thresholds map scores to risk levels:

| Variable | Default | Description | |---|---|---| | MCP_VALIDATOR_MEDIUM_MIN_SCORE | 3 | Minimum score to classify as MEDIUM risk | | MCP_VALIDATOR_HIGH_MIN_SCORE | 5 | Minimum score to classify as HIGH risk | | MCP_VALIDATOR_CRITICAL_MIN_SCORE | 8 | Minimum score to classify as CRITICAL risk | | MCP_VALIDATOR_REFUSE_AT_OR_ABOVE | critical | Refuse SQL execution at or above this risk level (low|medium|high|critical) |

OpenTelemetry Tracing

| Variable | Default | Description | |---|---|---| | MCP_TRACING_ENABLED | false | Enable OTLP trace export | | MCP_TRACING_OTLP_ENDPOINT | http://phoenix:4317 | OTLP gRPC exporter endpoint | | MCP_TRACING_SERVICE_NAME | mssql-mcp-server | Service name tag in traces | | MCP_TRACING_CAPTURE_HTTP_BODIES | false | Include request/response JSON in trace spans | | MCP_TRACING_BODY_MAX_CHARACTERS | 4096 | Max characters captured per body attribute (128-50000) | | MCP_TRACING_EXPORT_TIMEOUT_MILLISECONDS | 10000 | OTLP export timeout (100-120000) | | MCP_TRACING_SAMPLE_RATIO | 1.0 | Trace sampling ratio (0.0-1.0) |

Allowlist Policy

When MCP_ALLOWLIST_ENABLED=true, the server loads a YAML file that restricts which databases, schemas, and tables the server will expose. Any request targeting an object outside the policy returns an error.

Set MCP_ALLOWLIST_POLICY_PATH to the absolute path of your policy file.

Example policy file (allowlist-policy.example.yaml):

databases:
  - SalesDW
  - Reporting

schemas:
  SalesDW:
    - dbo
    - analytics
  Reporting:
    - dbo

tables:
  SalesDW.dbo:
    - FactSales
    - DimCustomer
  Reporting.dbo:
    - DailyKPI

Hierarchy rules:

If databases is empty, all databases are allowed.
If schemas has no entry for a database, all schemas in that database are allowed.
If tables has no entry for a database.schema, all tables in that schema are allowed.

When mounting the file in Docker:

# docker-compose.yml
volumes:
  - ./allowlist-policy.yaml:/etc/mcp/allowlist-policy.yaml:ro

# .env
MCP_ALLOWLIST_ENABLED=true
MCP_ALLOWLIST_POLICY_PATH=/etc/mcp/allowlist-policy.yaml

Endpoints

All tool endpoints are exposed by the mcpo proxy on port 8081 as POST /<tool_name> with a JSON body. Browse http://localhost:8081/docs for the interactive Swagger UI.

The FastMCP server itself listens on internal port 8082 (SSE transport) and is not directly reachable from outside the Docker network.

`POST /health_check`

Returns server configuration summary and cache statistics. No request body required.

Response (200)

{
  "status": "ok",
  "service": "mssql-mcp-server",
  "sqlserver_host": "localhost",
  "sqlserver_port": 1433,
  "sqlserver_database": "master",
  "tracing_enabled": false,
  "metadata_cache_ttl_seconds": 300,
  "metadata_cache_entries": 0,
  "validator_refuse_at_or_above": "critical"
}

`POST /list_databases`

Lists all accessible databases on the SQL Server instance.

Request body

{ "include_system": false }

| Field | Type | Default | Description | |---|---|---|---| | include_system | bool | false | Include system databases (master, model, msdb, tempdb) |

Response (200)

{
  "databases": [
    { "name": "SalesDW" },
    { "name": "Reporting" }
  ]
}

`POST /list_tables`

Lists tables in a database, optionally filtered by schema.

Request body

{
  "database": "SalesDW",
  "schema_name": "dbo"
}

| Field | Type | Required | Description | |---|---|---|---| | database | string | Yes | Target database | | schema_name | string | No | Filter to this schema |

Response (200)

{
  "tables": [
    { "schema": "dbo", "name": "FactSales" }
  ]
}

`POST /describe_table`

Returns column metadata and foreign keys for a single table.

Request body

{
  "database": "SalesDW",
  "schema_name": "dbo",
  "table": "FactSales"
}

| Field | Type | Required | Description | |---|---|---|---| | database | string | Yes | Target database | | schema_name | string | Yes | Target schema | | table | string | Yes | Target table |

Response (200)

{
  "table": {
    "database": "SalesDW",
    "schema": "dbo",
    "name": "FactSales"
  },
  "columns": [
    {
      "name": "SalesID",
      "data_type": "int",
      "is_nullable": false,
      "is_primary_key": true,
      "max_length": null,
      "precision": 10,
      "scale": 0
    }
  ],
  "foreign_keys": [
    {
      "constraint_name": "FK_FactSales_DimCustomer",
      "column": "CustomerID",
      "referenced_database": "SalesDW",
      "referenced_schema": "dbo",
      "referenced_table": "DimCustomer",
      "referenced_column": "CustomerID"
    }
  ]
}

`POST /get_foreign_keys`

Returns foreign key constraints for a single table.

Request body

{
  "database": "SalesDW",
  "schema_name": "dbo",
  "table": "FactSales"
}

Response (200)

{
  "foreign_keys": [
    {
      "constraint_name": "FK_FactSales_DimCustomer",
      "column": "CustomerID",
      "referenced_database": "SalesDW",
      "referenced_schema": "dbo",
      "referenced_table": "DimCustomer",
      "referenced_column": "CustomerID"
    }
  ]
}

`POST /validate_sql`

Validates a SQL statement and returns a deterministic safety and risk analysis. Does not execute the query.

Request body

{
  "sql": "SELECT * FROM dbo.FactSales WHERE SalesDate > '2024-01-01'",
  "policy": null
}

| Field | Type | Required | Description | |---|---|---|---| | sql | string | Yes | SQL statement to validate | | policy | ValidatorPolicy | No | Override validator risk thresholds for this call |

Response (200)

{
  "is_valid": true,
  "normalized_sql": "SELECT * FROM dbo.FactSales WHERE SalesDate > '2024-01-01'",
  "risk_level": "low",
  "risk_score": 1,
  "risk_factors": [],
  "blocked_rules": [],
  "refusal_reason": null
}

| Field | Description | |---|---| | is_valid | true if the query is safe to execute under the current policy | | normalized_sql | Normalized form of the SQL as parsed by sqlglot | | risk_level | low | medium | high | critical | | risk_score | Integer score (0 = safest) | | risk_factors | List of risk signals found in the parse tree | | blocked_rules | Rules that caused is_valid=false | | refusal_reason | Human-readable reason if the query was refused |

`POST /estimate_query_risk`

Identical to validate_sql — returns the same risk scoring. This alias is provided for semantic clarity in agent workflows where you want risk estimation as a separate reasoning step from the validation gate.

Request / Response: Same as POST /validate_sql.

`POST /run_sql_query`

Executes a read-only SQL query. The query is validated first; execution is denied if it fails the safety policy.

Request body

{
  "sql": "SELECT TOP 10 * FROM dbo.FactSales",
  "database": "SalesDW",
  "row_limit": 100,
  "offset": 0,
  "policy": null
}

| Field | Type | Required | Description | |---|---|---|---| | sql | string | Yes | SQL SELECT statement to execute | | database | string | No | Database context (overrides server default) | | row_limit | int | No | Max rows to return (default 100, bounded by MCP_EXECUTION_MAX_ROW_LIMIT) | | offset | int | No | Row offset for pagination (default 0) | | policy | ValidatorPolicy | No | Override validator thresholds for this call |

Response (200)

{
  "columns": ["SalesID", "CustomerID", "SalesDate", "Amount"],
  "rows": [
    { "SalesID": 1, "CustomerID": 42, "SalesDate": "2024-03-15", "Amount": 199.99 }
  ],
  "returned_rows": 1,
  "row_limit": 100,
  "offset": 0,
  "has_more": false,
  "next_offset": null,
  "execution_time_ms": 12
}

Important: Only SELECT statements pass validation. Any INSERT, UPDATE, DELETE, DROP, or other mutating statement will be refused.

`POST /record_audit_event`

Persists a structured audit event to the SQL Server audit store.

Request body

{
  "endpoint": "run_sql_query",
  "event_type": "QUERY_EXECUTED",
  "sequence_no": 1,
  "conversation_id": "conv-abc123",
  "correlation_id": "req-xyz456",
  "stage_name": "execution",
  "decision_type": "ALLOW",
  "refusal_reason": null,
  "duration_ms": 12,
  "event_utc": "2024-03-15T10:30:00Z",
  "payload": {}
}

| Field | Type | Required | Description | |---|---|---|---| | endpoint | string | Yes | Tool name that triggered the event | | event_type | string | Yes | Event classification string | | sequence_no | int | No | Ordering sequence within a conversation | | conversation_id | string | No | Correlates events in a single agent conversation | | correlation_id | string | No | Correlates events across services | | stage_name | string | No | Pipeline stage (e.g. validation, execution) | | decision_type | string | No | ALLOW or DENY | | refusal_reason | string | No | Populated when decision_type=DENY | | duration_ms | int | No | Elapsed time of the operation | | event_utc | string | No | ISO 8601 timestamp (defaults to server time) | | payload | object | No | Arbitrary extra data |

Response (200)

{
  "accepted": true,
  "event_id": 42
}

`POST /record_feedback`

Persists user feedback for a SQL copilot interaction.

Request body

{
  "conversation_id": "conv-abc123",
  "sql": "SELECT * FROM dbo.FactSales",
  "label": "correct",
  "rating": 5,
  "comments": "Exactly what I needed."
}

Response (200)

{
  "accepted": true,
  "feedback_id": 7
}

MCP Resources

In addition to tools, the server exposes 6 MCP resources accessible to MCP clients that connect directly to the SSE endpoint (http://localhost:8082/sse). Resources are read-only, cacheable views and are not exposed as REST endpoints through mcpo.

| Resource URI | Description | |---|---| | schema://index | Hierarchical catalog (databases -> schemas -> tables), system DBs excluded | | schema://index/with-system | Same catalog including system databases | | schema://table/{database}/{schema_name}/{table_name} | Full column + FK definition for one table | | schema://relationships/{database}/{schema_name}/{table_name} | Foreign key relationships for one table | | mcp://guidelines | Operational guidelines for safe use of this server | | mcp://policy | Snapshot of the currently active allowlist policy |

Error Handling

When a tool encounters an error (SQL Server connection failure, policy violation, object not found, etc.), it raises a runtime error with a structured code prefix. mcpo propagates the error message as a string in the response.

Common error codes embedded in error messages:

| Code | Description | |---|---| | CONNECTION_FAILED | Cannot connect to SQL Server | | TIMEOUT | Query or connection timed out | | POLICY_VIOLATION | Request blocked by allowlist policy | | ACCESS_DENIED | Insufficient database permissions | | TABLE_NOT_FOUND | Requested table does not exist | | SCHEMA_NOT_FOUND | Requested schema does not exist | | INVALID_REQUEST | Malformed request or invalid SQL | | UNKNOWN_ERROR | Unexpected internal error |

SQL safety refusals (from validate_sql / run_sql_query) are returned inline in the response body with is_valid: false and a refusal_reason string — they are not errors at the HTTP level.

OpenTelemetry Tracing

When MCP_TRACING_ENABLED=true, the server exports traces via OTLP/gRPC to MCP_TRACING_OTLP_ENDPOINT. Every tool call becomes a span under the service name MCP_TRACING_SERVICE_NAME.

Compatible receivers include:

Arize Phoenix (default endpoint: http://phoenix:4317)
Jaeger (http://jaeger:4317)
OpenTelemetry Collector

Running Tests

The test suite uses pytest with direct unit tests — no live SQL Server or MCP client required.

pip install pytest
pytest tests/ packages/sql-validator/tests/ -v

Tests cover:

SQL validation logic and risk scoring (direct validate_sql_query() calls)
run_sql_query execution guard (mocked metadata service)
Audit event and feedback recording stubs
Allowlist policy enforcement

MCP Servers

MSSQL MCP Server — Standalone

Contents

What It Does

Architecture

Project Structure

Quick Start (Docker)

1. Configure environment

2. Build and run

3. Verify

Quick Start (Local Python)

1. Create and activate a virtual environment

2. Install packages

3. Set environment variables

4. Run

Configuration Reference

SQL Server Connection

Allowlist Policy

Metadata Cache

Query Execution Limits

SQL Validator Risk Thresholds

OpenTelemetry Tracing

Allowlist Policy

Endpoints

`POST /health_check`

`POST /list_databases`

`POST /list_tables`

`POST /describe_table`

`POST /get_foreign_keys`

`POST /validate_sql`

`POST /estimate_query_risk`

`POST /run_sql_query`

`POST /record_audit_event`

`POST /record_feedback`

MCP Resources

Error Handling

OpenTelemetry Tracing

Running Tests

安装包（如果需要）

Cursor 配置 (mcp.json)

MSSQL MCP Server — Standalone

Contents

What It Does

Architecture

Project Structure

Quick Start (Docker)

1. Configure environment

2. Build and run

3. Verify

Quick Start (Local Python)

1. Create and activate a virtual environment

2. Install packages

3. Set environment variables

4. Run

Configuration Reference

SQL Server Connection

Allowlist Policy

Metadata Cache

Query Execution Limits

SQL Validator Risk Thresholds

OpenTelemetry Tracing

Allowlist Policy

Endpoints

POST /health_check

POST /list_databases

POST /list_tables

POST /describe_table

POST /get_foreign_keys

POST /validate_sql

POST /estimate_query_risk

POST /run_sql_query

POST /record_audit_event

POST /record_feedback

MCP Resources

Error Handling

OpenTelemetry Tracing

Running Tests

安装包 （如果需要）

Cursor 配置 (mcp.json)

`POST /health_check`

`POST /list_databases`

`POST /list_tables`

`POST /describe_table`

`POST /get_foreign_keys`

`POST /validate_sql`

`POST /estimate_query_risk`

`POST /run_sql_query`

`POST /record_audit_event`

`POST /record_feedback`

安装包（如果需要）