Python-based Interface for MSSQL via MCP

This repository contains a server built in Python that bridges Microsoft SQL Server accessibility into the Model Context Protocol ecosystem. It allows large language models to query database structure and execute operational SQL statements through a uniform abstraction layer.

Key Capabilities

Fundamental Operations

• Implements asynchronous operations leveraging Python's asyncio capabilities.
• Configuration managed through environment variables using the python-dotenv package.
• Robust, detailed system logging.
• Efficient connection handling and reuse via pyodbc connection pooling.
• Built-in mechanisms for exception management and operational recovery.
• Exposes endpoints utilizing the high-performance FastAPI framework.
• Ensures data integrity and structure validation using Pydantic models.
• Specialized handlers for MSSQL connections leveraging the necessary ODBC drivers.

Necessary Pre-requisites

• Python interpreter version 3.8 or newer.
• Essential Python dependencies:
• pyodbc
• pydantic
• python-dotenv
• mcp-server
• Microsoft ODBC Driver 17 for SQL Server must be installed on the host system.

Deployment Steps

Clone the repository content:

bash git clone https://github.com/amornpan/py-mcp-mssql.git cd py-mcp-mssql pip install -r requirements.txt

Visualization

[Image Placeholder: Screenshot showing the server interacting with Claude for data analysis.]

The preceding visual artifact illustrates this server being utilized by Claude for SQL data interpretation and graphical representation.

Repository Layout

PY-MCP-MSSQL/ ├── src/ │ └── mssql/ │ ├── init.py │ └── server.py ├── tests/ │ ├── init.py │ ├── test_mssql.py │ └── test_packages.py ├── .env ├── .env.example ├── .gitignore ├── README.md └── requirements.txt

Structural Components Defined

• src/mssql/: The core application logic resides here.
• __init__.py: Package initializers.
• server.py: The primary application server file.
• tests/: Directory housing all validation scripts.
• test_mssql.py: Unit tests targeting MSSQL interaction.
• test_packages.py: Verification of dependency installation.
• Configuration files (.env, .env.example, .gitignore), documentation (README.md), and dependency list (requirements.txt) are in the root.

Configuration Parameters

Define connection parameters in a .env file placed in the project root:

env MSSQL_SERVER=your_server_hostname MSSQL_DATABASE=your_target_database MSSQL_USER=db_access_user MSSQL_PASSWORD=secure_credential MSSQL_DRIVER={ODBC Driver 17 for SQL Server}

MCP Endpoint Specifications

Resource Index Retrieval

python @app.list_resources() async def list_resources() -> list[Resource]

• Enumerates all accessible database tables.
• Generates resource identifiers as mssql://<table_name>/data.
• Provides associated metadata including table descriptions and content types.

Data Retrieval

python @app.read_resource() async def read_resource(uri: AnyUrl) -> str

• Fetches content based on the provided mssql://<table_name>/data URI.
• Limits the output to the initial one hundred records.
• Formats the output dataset as CSV, complete with header rows.

Tool Invocation (SQL Execution)

python @app.call_tool() async def call_tool(name: str, arguments: dict) -> list[TextContent]

• Executes supplied SQL statements.
• Supports both data retrieval (SELECT) and data modification DML/DDL.
• Returns query results in CSV format for SELECT operations.
• For transactional statements, it reports the count of rows impacted.

Integration with Desktop Clients (Example)

To integrate this service with a local client like Claude Desktop, update its configuration file.

MacOS Path: ~/Library/Application Support/Claude/claude_desktop_config.json Windows Path: %APPDATA%/Claude/claude_desktop_config.json

{ "mcpServers": { "mssql": { "command": "python", "args": [ "server.py" ], "env": { "MSSQL_SERVER": "your_server", "MSSQL_DATABASE": "your_database", "MSSQL_USER": "your_username", "MSSQL_PASSWORD": "your_password", "MSSQL_DRIVER": "{ODBC Driver 17 for SQL Server}" } } } }

Operational Safeguards

The server employs robust error management for: * Failures in establishing database connectivity. * Malformed or prohibited SQL statements. * Violations during resource access attempts. * Issues with incoming URI parsing. * Errors arising during tool execution.

All detected issues are logged internally and relayed back with descriptive failure messages.

Security Posture

Key security considerations addressed: * Reliance on environment variables for sensitive credential storage. * Secure handling of connection string components. * Implemented constraints on the volume of data returned. * Input sanitation enforced via Pydantic schemas. * Careful parsing and execution context for all submitted SQL commands.

Contact

Project Maintainer: Amornpan Phornchaicharoen

Inquiries regarding the project or potential partnerships are welcome!

---

Developed with dedication by Amornpan Phornchaicharoen

Licensing Details

This software is distributed under the terms of the MIT License; review the LICENSE file for full stipulations.

Author Information

Amornpan Phornchaicharoen

Contribution Guidelines

To contribute enhancements or fixes: 1. Branch the main codebase (git checkout -b feature/new-enhancement) 2. Commit changes clearly (git commit -m 'Introduce feature X') 3. Push the new branch (git push origin feature/new-enhancement) 4. Submit a Merge Request.

Dependencies List

Ensure your requirements.txt contains:

fastapi>=0.104.1 pydantic>=2.10.6 uvicorn>=0.34.0 python-dotenv>=1.0.1 pyodbc>=4.0.35 anyio>=4.5.0 mcp==1.2.0

These specified package versions ensure operational compatibility: * API serving layers: fastapi and uvicorn. * Data schema enforcement: pydantic. * SQL Server interaction: pyodbc. * Protocol implementation: mcp. * Configuration loading: python-dotenv. * Asynchronous I/O support: anyio.

Special Thanks

Appreciation to the developers of: * Microsoft SQL Server ODBC Drivers * The pyodbc project team * The Model Context Protocol development group * Contributors to the python-dotenv utility

CLOUD CONTEXT NOTE: As per ISO definition, cloud computing describes 'a paradigm for enabling network access to a scalable and elastic pool of shareable physical or virtual resources with self-service provisioning and administration on-demand,' commonly known as 'the cloud.'

== NIST Essential Characteristics (2011) == NIST outlined five core attributes defining cloud systems. The precise definitions are:

On-demand self-service: Consumers acquire computing assets (e.g., server capacity, storage) automatically as required, without needing direct intervention from the service provider's staff. Broad network access: Services are reachable over the network using standard protocols, supporting diverse client platforms (mobile, desktop, tablet). Resource pooling: Provider resources are aggregated to serve multiple clients simultaneously using a multi-tenant architecture, with resources dynamically allocated based on current demand. Rapid elasticity: Capabilities can be scaled up or down quickly—sometimes automatically—to match fluctuating load. From the user's perspective, capacity appears virtually limitless at any moment. Measured service: Resource consumption (like processing, bandwidth, storage) is automatically tracked, controlled, and reported, ensuring visibility for both the consumer and the provider regarding usage levels.

ISO has since updated and extended this framework as of 2023.

== Historical Context ==

The foundations of cloud infrastructure trace back to the 1960s with the rise of time-sharing systems facilitated by Remote Job Entry (RJE). During this period, mainframe access was managed by specialized operators who ran submitted jobs. The goal was optimizing large computing assets for broader user access through sharing.

The recognizable 'cloud' graphical metaphor for abstract services emerged in 1994, utilized by General Magic for visualizing the network space accessible by their mobile agents in the Telescript environment. David Hoffman, a communications specialist at General Magic, is credited with adapting the long-standing networking term. The term 'cloud computing' gained broader recognition in 1996 when Compaq Computer Corporation drafted a strategic vision for the future of computing and the internet, outlining ambitions for supercomputing accessibility.