MCP Server MSSQL Python Read-Only Schema Inspection

Overview

MCP Server MSSQL Python Read-Only Schema Inspection is a lightweight Python service that exposes Microsoft SQL Server schema metadata over an MCP (Model Context Protocol) endpoint. It is designed for situations where models or developer tools need safe, read-only access to database structure — tables, columns, indexes, and relationships — without permitting data modification or arbitrary query execution.

This tool is useful when you want to integrate schema knowledge into LLM-assisted workflows, build documentation/catalogs, or audit database structure while enforcing strict access controls. The server focuses on security: it only exposes metadata, supports configurable access lists and role-based restrictions, and can be deployed behind existing MCP-aware model stacks or run standalone.

Features

• Read-only exposure of MSSQL schema metadata (tables, columns, types, indexes, FK relationships)
• MCP-compatible API surface for model context retrieval
• Configurable authentication and authorization (API keys, allowed schemas/tables)
• Pluggable transport (direct Python process, Dockerized deployment)
• Minimal dependencies: standard Python packages plus an MSSQL driver (pyodbc or pymssql)
• Logging, request throttling, and optional JSON/JSON-LD responses for easy consumption by tools and LLMs
• Safe defaults: denies modification requests and filters potentially sensitive object names

Installation / Configuration

Prerequisites: Python 3.9+, an MSSQL driver (pyodbc or pymssql), network access to the SQL Server.

Install from PyPI or from source:

# From PyPI
pip install py-mcp-mssql

# From source (clone and install)
git clone https://github.com/amornpan/py-mcp-mssql.git
cd py-mcp-mssql
pip install -e .

Example minimal configuration (YAML):

# config.yml
mssql:
  host: "db.example.local"
  port: 1433
  database: "SalesDB"
  user: "readonly_user"
  password: "REDACTED"
  driver: "ODBC Driver 17 for SQL Server"

server:
  host: "0.0.0.0"
  port: 8080
  api_key: "replace-with-secure-key"

access_control:
  allowed_schemas:
    - dbo
    - sales
  allowed_tables: []
  deny_patterns:
    - "%_secret"
  max_items_per_request: 1000

Start the server:

mcp-mssql --config config.yml
# Or as a module
python -m mcp_mssql.server --config config.yml

Docker (example Dockerfile usage):

FROM python:3.11-slim
RUN pip install py-mcp-mssql
COPY config.yml /app/config.yml
CMD ["mcp-mssql", "--config", "/app/config.yml"]

Available Resources

The server exposes a small set of MCP-aligned endpoints (or RPC methods) focused on schema metadata. Typical endpoints include:

• GET /schema — top-level schema list and metadata summary
• GET /tables — list tables for a schema (supports filtering and pagination)
• GET /tables/{table_name}/columns — column definitions and types
• GET /tables/{table_name}/indexes — index metadata
• GET /foreign_keys — foreign key relationships
• GET /server_info — MSSQL server version and basic statistics

Responses are JSON by default and include provenance fields indicating the server and read-only status. All endpoints enforce the configured access control rules.

Configuration Options (summary)

Use Cases

• LLM grounding: Provide a model with an accurate list of table/column names and types so it can generate safe, schema-aware prompts or queries without exposing row-level data.
  • Example: A prompt loader calls GET /tables and GET /tables/orders/columns to assemble context for a SQL-generation model.
• Discovery and documentation: Automatically generate data catalogs or ER diagrams from live metadata.
  • Example: CI job polls /foreign_keys and creates a Graphviz diagram for architecture docs.
• Security audits: Validate that only allowed schemas and table patterns are visible to external tools.
  • Example: Run an automated scan that asserts no _secret tables are returned and logs violations.
• Developer tooling: IDE plugins or API clients can autocomplete column names and types by fetching /tables/{table}/columns.

Security Notes

• Use a dedicated read-only database account with minimal privileges (VIEW DEFINITION).
• Always run behind TLS and require API keys or integration tokens.
• Configure allowed_schemas and deny_patterns to limit exposure of sensitive metadata.
• Monitor access logs and throttle requests to prevent metadata enumeration abuse.

For more advanced integration examples and a full reference of endpoints and response formats, see the repository README and the included example clients in the project.