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)

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

cp .env.example .env

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

docker compose up --build

http://localhost:8081/docs

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

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

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

# 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

python -m app.main

databases:
  - SalesDW
  - Reporting

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

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

# 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

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

{ "include_system": false }

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

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

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

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

{
  "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"
    }
  ]
}

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

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

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

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

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

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

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

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

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

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

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