mssql-mcp-server-python

A read-only MCP (Model Context Protocol) server for MS SQL Server, written in Python. Lets AI agents query your database safely — no writes, no stored procedures, no shell access.

Features

• Read-only — only SELECT queries allowed
• Two modes — stdio (local) or HTTP + JWT (remote)
• Official driver — uses pyodbc with system ODBC driver
• No third-party auth libraries — JWT verified with Python built-in hmac + hashlib
• Query safety — blocks dangerous keywords, requires TOP or WHERE

Tools exposed to AI agents

Tool	Description
list_tables	List all tables in the database
describe_table	Show columns and types for a table
query	Run a read-only SELECT query

Requirements

• Python >= 3.11
• ODBC Driver for SQL Server installed on the machine

Check available ODBC drivers:

python -c "import pyodbc; print(pyodbc.drivers())"

If none installed, download from: https://learn.microsoft.com/sql/connect/odbc/download-odbc-driver-for-sql-server

Installation

Step 1 — Clone the repo:

git clone https://github.com/GarfieldHuang/mssql-mcp-server-python.git
cd mssql-mcp-server-python

Step 2 — Install Python dependencies:

pip install -r requirements.txt

Step 3 — Set up environment variables:

copy .env.example .env

Open .env and fill in your DB credentials and API_KEY.

Step 4 — Generate API_KEY:

python -c "import secrets; print(secrets.token_hex(32))"

Paste the output into .env as API_KEY=....

Usage

Stdio mode — 本機 AI agent 使用

Stdio 模式不需要手動啟動 server。AI agent（如 Claude Code）會在需要時自動啟動 server.py，透過 stdin/stdout 溝通，用完自動關閉。

在 claude_desktop_config.json 登記一次即可：

{
  "mcpServers": {
    "mssql": {
      "command": "python",
      "args": ["C:\\path\\to\\server.py"]
    }
  }
}

登記後重啟 Claude Code，即可直接用自然語言查詢資料庫，無需手動執行任何指令。

HTTP mode — 遠端 AI agent 使用

如果 AI agent 在其他機器上，需要改用 HTTP 模式。在能連到 MSSQL 的機器上執行：

python server.py --http

Server 會持續在背景執行（保持 cmd 視窗開著），監聽 HTTP_PORT（預設 3000）。
遠端 agent 透過 HTTPS + JWT token 連線：

POST http://your-server:3000/mcp
Authorization: Bearer <jwt-token>

Testing

Three test JSON files are included. Before running, edit each file and replace your-api-key with the value from your .env.

Step 1 — Check server starts and lists tools:

python server.py < test-list-tools.json

Expected output: JSON with query, list_tables, describe_table.

Step 2 — List tables in your database:

python server.py < test-list-tables.json

Expected output: JSON array of table names.

Step 3 — Run a query:

Edit test-query.json and replace your_table with a real table name, then:

python server.py < test-query.json

Expected output: JSON array of rows.

Windows tip: If < redirection doesn't work in cmd, use PowerShell:

Get-Content test-query.json | python server.py

Environment variables

Variable	Required	Description
DB_SERVER	Yes	MSSQL hostname or IP
DB_PORT	No	Default: 1433
DB_DATABASE	Yes	Database name
DB_USER	Yes	DB username (use a read-only account)
DB_PASSWORD	Yes	DB password
DB_DRIVER	No	ODBC driver name, default: ODBC Driver 18 for SQL Server
API_KEY	Yes	Secret key agents must pass in tool calls
HTTP_PORT	No	HTTP mode port, default: 3000
JWT_SECRET	HTTP only	Secret for JWT verification

Create a read-only DB user (MSSQL)

CREATE LOGIN mcp_agent WITH PASSWORD = 'strong-password';
CREATE USER mcp_agent FOR LOGIN mcp_agent;
EXEC sp_addrolemember 'db_datareader', 'mcp_agent';