# 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](#what-it-does)
- [Architecture](#architecture)
- [Project Structure](#project-structure)
- [Quick Start (Docker)](#quick-start-docker)
- [Quick Start (Local Python)](#quick-start-local-python)
- [Configuration Reference](#configuration-reference)
- [Allowlist Policy](#allowlist-policy)
- [Endpoints](#endpoints)
- [MCP Resources](#mcp-resources)
- [Error Handling](#error-handling)
- [OpenTelemetry Tracing](#opentelemetry-tracing)
- [Running Tests](#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](https://github.com/open-webui/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

```bash
cp .env.example .env
```

Edit ``.env`` and set at minimum:

```env
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

```bash
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:

```bash
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](https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server).

> **Note:** Running locally starts the FastMCP server only (port 8082). To get the
> OpenAPI/REST layer, run [mcpo](https://github.com/open-webui/mcpo) separately pointing
> at ``http://localhost:8082/sse``.

### 1. Create and activate a virtual environment

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

### 2. Install packages

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

### 3. Set environment variables

```bash
# 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

```bash
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``):

```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:

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

```env
# .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)**

```json
{
  "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**

```json
{ "include_system": false }
```

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

**Response (200)**

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

---

### ``POST /list_tables``

Lists tables in a database, optionally filtered by schema.

**Request body**

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

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

**Response (200)**

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

---

### ``POST /describe_table``

Returns column metadata and foreign keys for a single table.

**Request body**

```json
{
  "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)**

```json
{
  "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**

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

**Response (200)**

```json
{
  "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**

```json
{
  "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)**

```json
{
  "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-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**

```json
{
  "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)**

```json
{
  "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**

```json
{
  "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)**

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

---

### ``POST /record_feedback``

Persists user feedback for a SQL copilot interaction.

**Request body**

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

**Response (200)**

```json
{
  "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](https://phoenix.arize.com/) (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.

```bash
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