MSSQL-Python

What is py-mcp-mssql?

py-mcp-mssql is a Python-based Model Context Protocol (MCP) server designed to facilitate secure and standardized interaction with Microsoft SQL Server databases. It allows Language Models (LMs) to inspect database table schemas and execute SQL queries through a well-defined API, abstracting the complexities of direct database access.

How to use py-mcp-mssql?

To use py-mcp-mssql, you first need to clone the repository, install the required Python packages (pyodbc, pydantic, python-dotenv, mcp-server), and ensure you have ODBC Driver 17 for SQL Server installed. Configuration involves creating a .env file with your MSSQL server details (server, database, user, password, driver). The server can then be integrated with applications like Claude Desktop by adding specific configurations to its mcpServers settings, allowing the LM to communicate with your MSSQL database.

Key Features of py-mcp-mssql

Asynchronous Operation: Utilizes Python's asyncio for efficient, non-blocking database interactions.
Environment-based Configuration: Manages sensitive database credentials and settings via .env files using python-dotenv.
Comprehensive Logging: Provides detailed logging for monitoring and debugging.
Connection Pooling: Manages database connections efficiently through pyodbc.
Robust Error Handling: Implements extensive error handling for database connection failures, invalid queries, and resource access issues.
FastAPI Integration: Exposes API endpoints for database interaction using FastAPI.
Data Validation: Employs Pydantic models for rigorous input and output data validation.
Schema Inspection: Enables LMs to list available tables and inspect their schemas.
SQL Execution: Supports both SELECT and modification SQL queries, returning results in CSV format or affected row counts.
Security Features: Includes environment variable-based configuration, connection string security, result set size limits, and input validation.

Use Cases of py-mcp-mssql

Language Model Integration: Allows LMs (e.g., Claude) to directly query and analyze data in MSSQL databases, facilitating data-driven insights and automated reporting.
Automated Data Analysis: Enables programmatic access to MSSQL data for automated data analysis pipelines.
Database Schema Discovery: Provides a standardized way for applications to discover and understand MSSQL database schemas.
Secure Database Interaction: Offers a secure layer for applications to interact with MSSQL databases, reducing the risk of direct, unvalidated SQL execution.

FAQ about py-mcp-mssql

Q: What is the Model Context Protocol (MCP)? A: The Model Context Protocol is a standardized interface that allows Language Models to interact with external tools and data sources, such as databases, in a structured and secure manner.

Q: What are the prerequisites for running py-mcp-mssql? A: You need Python 3.x, specific Python packages (pyodbc, pydantic, python-dotenv, mcp-server), and ODBC Driver 17 for SQL Server.

Q: How does py-mcp-mssql ensure security? A: It incorporates features like environment variable-based configuration for credentials, connection string security, result set size limits, and input validation through Pydantic models to enhance security.

Q: Can I execute both read and write SQL queries? A: Yes, py-mcp-mssql supports both SELECT queries for data retrieval and modification queries (e.g., INSERT, UPDATE, DELETE).

Q: How can I integrate py-mcp-mssql with my application? A: The server exposes API endpoints via FastAPI. For specific LM integrations like Claude Desktop, you can configure it to communicate with the py-mcp-mssql server using the provided JSON configuration example.

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

MCP MSSQL Server Demo

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

Fork the repository
Create your feature branch (git checkout -b feature/amazing-feature)
Commit your changes (git commit -m 'Add some amazing feature')
Push to the branch (git push origin feature/amazing-feature)
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

MSSQL-Python Overview

What is py-mcp-mssql?

How to use py-mcp-mssql?

Key Features of py-mcp-mssql

Use Cases of py-mcp-mssql

FAQ about py-mcp-mssql

MSSQL-Python's README

Python MSSQL MCP Server

Features

Core Functionality

Prerequisites

Installation

Screenshots

Project Structure

Directory Structure Explanation

Configuration

API Implementation Details

Resource Listing

Resource Reading

SQL Execution

Usage with Claude Desktop

Error Handling

Security Features

Contact Information

Amornpan Phornchaicharoen

License

Author

Contributing

Requirements

Acknowledgments

MSSQL-Python Reviews

Actions

MSSQL-Python's Information