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.

---

Contents

• 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