MCP → Local MSSQL Server

A local MCP server that exposes SQL Server stored procedures as tools for LLM clients (Claude Desktop, VS Code, Cursor, etc.).

---

Files in this project

File	Purpose
mssql_server.py	The MCP server — all tools live here
usp_SalesReport.sql	Sample table + stored procedure to run in SQL Server
requirements.txt	Python dependencies
.env.example	Template for your connection credentials
claude_desktop_config.json	Drop-in config snippet for Claude Desktop

---

Prerequisites

1 — Python 3.10+

python --version

2 — Microsoft ODBC Driver 18 for SQL Server

Windows: Download from https://aka.ms/odbc18

macOS:

brew install msodbcsql18

Linux (Ubuntu/Debian):

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/22.04/prod.list \
  | sudo tee /etc/apt/sources.list.d/mssql-release.list
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18

---

Setup

Step 1 — Install Python packages

# with uv (recommended)
uv add "mcp[cli]" pyodbc

# or with pip
pip install -r requirements.txt

Step 2 — Create your .env file

cp .env.example .env
# Edit .env and fill in your SQL Server credentials

Step 3 — Create the sample stored procedure (optional)

Open SSMS or Azure Data Studio, switch to your target database, and run usp_SalesReport.sql. This creates a demo Sales table and the usp_SalesReport procedure.

Step 4 — Test the server with MCP Inspector

# Load .env first
export $(cat .env | xargs)          # macOS/Linux
# On Windows PowerShell: use $env:VAR = "value" for each line

mcp dev mssql_server.py

This opens a browser-based inspector. Call test_connection first to verify the DB link, then try run_sales_report.

---

Connecting to Claude Desktop

1. Open (or create) claude_desktop_config.json:

   • macOS: ~/Library/Application Support/Claude/claude_desktop_config.json
   • Windows: %APPDATA%\Claude\claude_desktop_config.json

2. Merge the contents of the provided claude_desktop_config.json into it, replacing /ABSOLUTE/PATH/TO/ with the real path to mssql_server.py.

3. Restart Claude Desktop. The tools appear in the tools panel.

---

Tools exposed by the server

test_connection

Verifies the database connection. Returns server version, database name, and server time. Run this first to confirm everything is wired up.

list_procedures

Lists all stored procedures in the connected database.

run_sales_report(start_date, end_date)

Calls dbo.usp_SalesReport. Returns two result sets:

• Row-level sale detail for the date range
• Revenue summary grouped by region

Dates must be in YYYY-MM-DD format.

---

Adding your own procedures

1. Add a new @mcp.tool() function in mssql_server.py.
2. Hardcode the procedure name in the cursor.execute() call — never accept a proc name as a parameter (can't be bound with ?).
3. Bind all user-supplied values with ? placeholders, never f-strings.

@mcp.tool()
def get_customer_orders(customer_id: int) -> dict:
    """Fetch all orders for a customer from dbo.usp_GetCustomerOrders."""
    conn = get_connection()
    try:
        cursor = conn.cursor()
        cursor.execute("EXEC dbo.usp_GetCustomerOrders @CustomerID = ?", customer_id)
        data = _rows_from_cursor(cursor)
        return {"row_count": len(data), "data": data}
    except pyodbc.Error as e:
        return {"error": str(e)}
    finally:
        conn.close()

---

Security notes

• stdio transport runs with the same OS permissions as the host app. Use a least-privilege SQL login, not sa.
• Store credentials in environment variables or .env, never in code.
• Never commit .env — add it to .gitignore.
• TrustServerCertificate=yes is fine for local dev; remove it in production and use a proper TLS certificate.