Content
# 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:
```bash
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:**
```cmd
git clone https://github.com/GarfieldHuang/mssql-mcp-server-python.git
cd mssql-mcp-server-python
```
**Step 2 — Install Python dependencies:**
```cmd
pip install -r requirements.txt
```
**Step 3 — Set up environment variables:**
```cmd
copy .env.example .env
```
Open `.env` and fill in your DB credentials and `API_KEY`.
**Step 4 — Generate API_KEY:**
```cmd
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` 登記一次即可：
```json
{
"mcpServers": {
"mssql": {
"command": "python",
"args": ["C:\\path\\to\\server.py"]
}
}
}
```
登記後重啟 Claude Code，即可直接用自然語言查詢資料庫，無需手動執行任何指令。
### HTTP mode — 遠端 AI agent 使用
如果 AI agent 在其他機器上，需要改用 HTTP 模式。在能連到 MSSQL 的機器上執行：
```cmd
python server.py --http
```
Server 會持續在背景執行（保持 cmd 視窗開著），監聽 `HTTP_PORT`（預設 3000）。
遠端 agent 透過 HTTPS + JWT token 連線：
```http
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:**
```cmd
python server.py < test-list-tools.json
```
Expected output: JSON with `query`, `list_tables`, `describe_table`.
**Step 2 — List tables in your database:**
```cmd
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:
```cmd
python server.py < test-query.json
```
Expected output: JSON array of rows.
> **Windows tip:** If `<` redirection doesn't work in cmd, use PowerShell:
> ```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)
```sql
CREATE LOGIN mcp_agent WITH PASSWORD = 'strong-password';
CREATE USER mcp_agent FOR LOGIN mcp_agent;
EXEC sp_addrolemember 'db_datareader', 'mcp_agent';
```
