amornpan/py-mcp-mssql

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
bash
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
│ ├── testmssql.py
│ └── testpackages.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
testmssql.py - MSSQL functionality tests
testpackages.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:
env
MSSQLSERVER=yourserver
MSSQLDATABASE=yourdatabase
MSSQLUSER=yourusername
MSSQLPASSWORD=yourpassword
MSSQLDRIVER={ODBC Driver 17 for SQL Server}
## API Implementation Details
### Resource Listing
python
@app.listresources()
async def listresources() -> list[Resource]
Lists all available tables in the database
Returns table names with URIs in the format mssql:// /data
Includes table descriptions and MIME types
### Resource Reading
python
@app.readresource()
async def readresource(uri: AnyUrl) -> str
Reads data from specified table
Accepts URIs in the format mssql:// /data
Returns first 100 rows in CSV format
Includes column headers
### SQL Execution
python
@app.calltool()
async def calltool(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/claudedesktopconfig.json
On Windows: %APPDATA%/Claude/claudedesktopconfig.json
json
"mcpServers": {
"mssql": {
"command": "python",
"args": [
"server.py"
],
"env": {
"MSSQLSERVER": "yourserver",
"MSSQLDATABASE": "yourdatabase",
"MSSQLUSER": "yourusername",
"MSSQLPASSWORD": "yourpassword",