MCP Servers

A collection of Model Context Protocol servers, templates, tools and more.

M
Mssql Mcpo Server
by @mortada7-11
GitHub Repo(1 stars)

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.

Created 5/21/2026
Updated about 4 hours ago
README
View Source
Repository documentation and setup instructions

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

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
Quick Setup
Installation guide for this server

Install Package (if required)

uvx mssql-mcpo-server

Cursor configuration (mcp.json)

{ "mcpServers": { "mortada7-11-mssql-mcpo-server": { "command": "uvx", "args": [ "mssql-mcpo-server" ] } } }