Py MCP MSSQL

What is Py MCP MSSQL about?

An MCP server written in Python that connects to Microsoft SQL Server using pyodbc, exposing FastAPI endpoints for listing resources, reading table data, and executing arbitrary SQL statements. The service is designed for LLMs to interact with relational data in a standardized way.

How to use Py MCP MSSQL?

1. Clone and install

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

2. Configure environment variables in a .env file (or set them in the runtime environment):

   MSSQL_SERVER=your_server
   MSSQL_DATABASE=your_database
   MSSQL_USER=your_username
   MSSQL_PASSWORD=your_password
   MSSQL_DRIVER={ODBC Driver 17 for SQL Server}
   

3. Run the server

   python src/mssql/server.py
   

4. Integrate with an LLM (e.g., Claude Desktop) by adding the MCP server definition to the client configuration as shown in the README.

Key Features of Py MCP MSSQL

• Fully asynchronous operation with asyncio.
• Environment‑based configuration via python-dotenv.
• Connection pooling and automatic recovery using pyodbc.
• Comprehensive logging and error handling.
• FastAPI endpoints wrapped with MCP decorators (list_resources, read_resource, call_tool).
• Pydantic models for request validation and response typing.
• Supports both SELECT and data‑modifying SQL queries, returning results in CSV format or affected‑row count.
• Security measures: env‑variable secrets, result size limits, input validation.

Use Cases of Py MCP MSSQL

• LLM‑driven data analysis – let a language model explore database schemas and run analytical queries without direct DB credentials.
• Chatbot integration – embed live SQL querying capabilities into conversational agents.
• Rapid prototyping – developers can expose a MSSQL instance to any MCP‑compatible client for testing data pipelines.
• Internal tooling – automate reporting or dashboards where the query logic is generated programmatically.

FAQ from the Py MCP MSSQL

Q: Which Python version is required? A: Python 3.8 or newer.

Q: Do I need to install ODBC drivers manually? A: Yes, install ODBC Driver 17 for SQL Server (or newer) on the host system.

Q: Can I limit the number of rows returned? A: The server automatically caps read operations to the first 100 rows; this can be adjusted in server.py.

Q: How are errors communicated to the client? A: Errors are logged server‑side and returned as structured MCP error messages with descriptive text.

Q: Is the server compatible with other databases? A: The core MCP implementation is generic, but this repository provides the MSSQL‑specific connector. A similar pattern can be used for other DBMS.

Python MSSQL MCP Server

A Model Context Protocol server implementation in Python that provides access to Microsoft SQL Server databases. This server enables Language Models to inspect table schemas and execute SQL queries through a standardized interface.

Features

Core Functionality

• Asynchronous operation using Python's asyncio
• Environment-based configuration using python-dotenv
• Comprehensive logging system
• Connection pooling and management via pyodbc
• Error handling and recovery
• FastAPI integration for API endpoints
• Pydantic models for data validation
• MSSQL connection handling with ODBC Driver

Prerequisites

• Python 3.x
• Required Python packages:
  • pyodbc
  • pydantic
  • python-dotenv
  • mcp-server
• ODBC Driver 17 for SQL Server

Installation

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

Screenshots

The screenshot above demonstrates the server being used with Claude to analyze and visualize SQL data.

Project Structure

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

Directory Structure Explanation

• src/mssql/ - Main source code directory
  • __init__.py - Package initialization
  • server.py - Main server implementation
• tests/ - Test files directory
  • __init__.py - Test package initialization
  • test_mssql.py - MSSQL functionality tests
  • test_packages.py - Package dependency tests
• .env - Environment configuration file (not in git)
• .env.example - Example environment configuration
• .gitignore - Git ignore rules
• README.md - Project documentation
• requirements.txt - Project dependencies

Configuration

Create a .env file in the project root:

MSSQL_SERVER=your_server
MSSQL_DATABASE=your_database
MSSQL_USER=your_username
MSSQL_PASSWORD=your_password
MSSQL_DRIVER={ODBC Driver 17 for SQL Server}

API Implementation Details

Resource Listing

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

• Lists all available tables in the database
• Returns table names with URIs in the format mssql://<table_name>/data
• Includes table descriptions and MIME types

Resource Reading

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

• Reads data from specified table
• Accepts URIs in the format mssql://<table_name>/data
• Returns first 100 rows in CSV format
• Includes column headers

SQL Execution

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

• Executes SQL queries
• Supports both SELECT and modification queries
• Returns results in CSV format for SELECT queries
• Returns affected row count for modification queries

Usage with Claude Desktop

Add to your Claude Desktop configuration:

On MacOS: ~/Library/Application Support/Claude/claude_desktop_config.json On Windows: %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}"
      }
    }
  }
}

Error Handling

The server implements comprehensive error handling for:

• Database connection failures
• Invalid SQL queries
• Resource access errors
• URI validation
• Tool execution errors

All errors are logged and returned with appropriate error messages.

Security Features

• Environment variable based configuration
• Connection string security
• Result set size limits
• Input validation through Pydantic
• Proper SQL query handling

Contact Information

Amornpan Phornchaicharoen

Feel free to reach out to me if you have any questions about this project or would like to collaborate!

---

Made with ❤️ by Amornpan Phornchaicharoen

License

This project is licensed under the MIT License - see the LICENSE file for details.

Author

Amornpan Phornchaicharoen

Contributing

1. Fork the repository
2. Create your feature branch (git checkout -b feature/amazing-feature)
3. Commit your changes (git commit -m 'Add some amazing feature')
4. Push to the branch (git push origin feature/amazing-feature)
5. Open a Pull Request

Requirements

Create a requirements.txt file with:

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 versions have been tested and verified to work together. The key components are:

• fastapi and uvicorn for the API server
• pydantic for data validation
• pyodbc for SQL Server connectivity
• mcp for Model Context Protocol implementation
• python-dotenv for environment configuration
• anyio for asynchronous I/O support

Acknowledgments

• Microsoft SQL Server team for ODBC drivers
• Python pyodbc maintainers
• Model Context Protocol community
• Contributors to the python-dotenv project