# mssql-mcp

[English](#english) | [中文](#中文)

A Model Context Protocol (MCP) server that exposes any Microsoft SQL Server database to AI agents — supports multiple databases in a single instance, generic keyword lookup, free SELECT queries, and three transport modes (stdio / SSE / Streamable HTTP). Ships as a Docker image.

一個把 Microsoft SQL Server 資料庫包裝成 MCP（Model Context Protocol）伺服器的工具，讓 AI Agent 可以直接查詢資料庫。單一服務可同時連多個資料庫，提供通用關鍵字查詢、自由 SELECT 查詢，並支援三種連線方式（stdio / SSE / Streamable HTTP）。以 Docker image 形式發布。

---

## English

### Features

- **Multi-database**: configure any number of SQL Server connections in one deployment via a single `DB_CONNECTIONS` environment variable.
- **Four MCP tools**:
  - `list_databases` — list all configured database connection names
  - `list_tables` — list all TABLEs and VIEWs (with column info) in a database
  - `lookup` — generic keyword search (`LIKE`) across any table/view's text columns
  - `query_sql` — free-form `SELECT` queries, with a safety guard that rejects any non-SELECT statement
- **Three transports**, so it works with virtually any MCP client regardless of version:
  - `stdio` — for Claude Code CLI (`docker run`)
  - `SSE` (legacy HTTP) — `GET /sse` + `POST /message`
  - `Streamable HTTP` (current spec) — `POST /mcp`
- **SQL injection protections**: table/field identifiers are validated against a blacklist of dangerous characters; all values are passed as parameterized query inputs.
- **No hardcoded credentials** — everything comes from environment variables.

### Requirements

- Docker (recommended), or Node.js 20+ if running without Docker
- Network access to your SQL Server instance(s)
- A SQL Server login with (at minimum) `db_datareader` on the target database(s)

### Quick Start (Docker)

```bash
git clone <this-repo-url>
cd mssql-mcp
cp .env.example .env
```

Edit `.env` and set `DB_CONNECTIONS` — a JSON array, one entry per database:

```env
DB_CONNECTIONS=[{"name":"main","host":"192.168.1.10","port":1433,"database":"MyDatabase","user":"sql_user","password":"sql_password"}]
```

To connect to more than one database, add more entries:

```env
DB_CONNECTIONS=[
  {"name":"sales","host":"192.168.1.10","database":"SalesDb","user":"u1","password":"p1"},
  {"name":"inventory","host":"192.168.1.11","database":"InventoryDb","user":"u2","password":"p2"}
]
```
(Keep it on one line in the actual `.env` file — JSON, no line breaks.)

Then build and start:

```bash
docker compose up -d --build
```

Check it's running:

```bash
docker logs mssql-mcp
# Expect: mssql-mcp HTTP server listening on port 3000
```

### Testing the endpoints

**Streamable HTTP (`/mcp`)** — requires the `Accept` header to include both content types:

```bash
curl -X POST http://localhost:3000/mcp \
  -H "Content-Type: application/json" \
  -H "Accept: application/json, text/event-stream" \
  -d '{"jsonrpc":"2.0","id":1,"method":"tools/call","params":{"name":"list_databases","arguments":{}}}'
```

**Legacy SSE (`/sse`)** — for older MCP clients, connect a client to `http://<host>:3000/sse`.

### Connecting from Claude Code

Add to `~/.claude/settings.json` (or your project's `.claude/settings.json`):

```json
{
  "mcpServers": {
    "mssql": {
      "type": "sse",
      "url": "http://<docker-host>:3000/sse"
    }
  }
}
```

If your Claude Code version supports the newer Streamable HTTP transport:

```json
{
  "mcpServers": {
    "mssql": {
      "type": "http",
      "url": "http://<docker-host>:3000/mcp"
    }
  }
}
```

Verify with `/mcp` inside Claude Code — you should see `list_databases`, `list_tables`, `lookup`, and `query_sql`.

### Tool reference

| Tool | Arguments | Description |
|---|---|---|
| `list_databases` | none | Lists all configured connection names |
| `list_tables` | `database?` | Lists all TABLEs/VIEWs and their columns in a database. `database` can be omitted if only one connection is configured. |
| `lookup` | `table`, `keyword`, `field?`, `database?` | Keyword search (`LIKE '%keyword%'`) across a table's text columns, or a single `field` if specified. Returns up to 100 rows. |
| `query_sql` | `sql`, `database?` | Runs a free-form `SELECT`. Any other statement type (`INSERT`/`UPDATE`/`DELETE`/`DROP`/etc.) is rejected before it reaches the database. |

When more than one database is configured, `database` is **required** — call `list_databases` first to discover valid names.

### Configuration reference

| Environment variable | Required | Description |
|---|---|---|
| `DB_CONNECTIONS` | Yes | JSON array of `{name, host, port?, database, user, password}` objects. `port` defaults to `1433`. |
| `HTTP_PORT` | No | HTTP listen port (default `3000`) |
| `MCP_TRANSPORT` | No | Force `stdio` or `http` mode. If unset, auto-detects: `stdio` when stdin is piped (e.g. by Claude Code CLI), otherwise `http`. |

### Local development (without Docker)

```bash
npm install
npm test          # run unit tests (SQL safety guard)
npm run build      # compile TypeScript
DB_CONNECTIONS='[{"name":"main","host":"...","database":"...","user":"...","password":"..."}]' npm start
```

### Security notes

- The safety guard only permits statements starting with `SELECT`. It does **not** attempt to block dangerous substrings inside string literals (e.g. a `WHERE name = 'DROP TABLE x'` search is legitimate and allowed) — the real security boundary is the database account's own permissions. Grant the SQL login used here **read-only** access (`db_datareader`).
- Table/field names for the `lookup` tool are validated against a blacklist of characters that could break out of `[...]` bracket-quoting (`]`, `[`, `;`, quotes, backticks, newlines, SQL comment sequences). This permits Unicode identifiers (e.g. Chinese table names) while blocking injection attempts.
- No authentication is built into the HTTP transports — deploy this only on a trusted internal network, or add your own reverse-proxy auth layer in front of it.

### License

MIT (or your organization's preferred license — update this section as needed)

---

## 中文

### 功能特色

- **多資料庫支援**：單一部署可透過一個 `DB_CONNECTIONS` 環境變數設定任意數量的 SQL Server 連線。
- **四個 MCP 工具**：
  - `list_databases` — 列出所有已設定的資料庫連線名稱
  - `list_tables` — 列出資料庫中所有 TABLE 和 VIEW（含欄位資訊）
  - `lookup` — 通用關鍵字模糊搜尋（`LIKE`），可搜任意 TABLE/VIEW 的文字欄位
  - `query_sql` — 自由 `SELECT` 查詢，內建安全守衛，拒絕任何非 SELECT 語句
- **三種連線方式**，幾乎相容所有版本的 MCP client：
  - `stdio` — 給 Claude Code CLI 使用（`docker run`）
  - `SSE`（舊版 HTTP）— `GET /sse` + `POST /message`
  - `Streamable HTTP`（新版規格）— `POST /mcp`
- **SQL injection 防護**：資料表/欄位名稱以黑名單擋掉危險字元，所有數值一律走參數化查詢。
- **無硬編碼帳密** — 全部透過環境變數設定。

### 需求

- Docker（建議），或 Node.js 20+（若不用 Docker）
- 能連到目標 SQL Server 的網路
- 一個至少有 `db_datareader` 權限的 SQL Server 帳號

### 快速開始（Docker）

```bash
git clone <this-repo-url>
cd mssql-mcp
cp .env.example .env
```

編輯 `.env`，設定 `DB_CONNECTIONS`（JSON 陣列，一個資料庫一個項目）：

```env
DB_CONNECTIONS=[{"name":"main","host":"192.168.1.10","port":1433,"database":"MyDatabase","user":"sql_user","password":"sql_password"}]
```

要連多個資料庫就多加幾個項目：

```env
DB_CONNECTIONS=[
  {"name":"sales","host":"192.168.1.10","database":"SalesDb","user":"u1","password":"p1"},
  {"name":"inventory","host":"192.168.1.11","database":"InventoryDb","user":"u2","password":"p2"}
]
```
（實際寫進 `.env` 時要是**單行**的 JSON，不能有換行。）

接著 build 並啟動：

```bash
docker compose up -d --build
```

確認啟動成功：

```bash
docker logs mssql-mcp
# 應看到：mssql-mcp HTTP server listening on port 3000
```

### 測試 endpoint

**Streamable HTTP（`/mcp`）**— 注意 `Accept` header 必須同時宣告兩種格式：

```bash
curl -X POST http://localhost:3000/mcp ^
  -H "Content-Type: application/json" ^
  -H "Accept: application/json, text/event-stream" ^
  -d "{\"jsonrpc\":\"2.0\",\"id\":1,\"method\":\"tools/call\",\"params\":{\"name\":\"list_databases\",\"arguments\":{}}}"
```
（Windows CMD 用 `^` 換行；PowerShell 或 Linux/Mac 請用單行或改用 `\`。）

**舊版 SSE（`/sse`）**— 給較舊的 MCP client 連 `http://<host>:3000/sse`。

### 接入 Claude Code

在 `~/.claude/settings.json`（或專案內 `.claude/settings.json`）加入：

```json
{
  "mcpServers": {
    "mssql": {
      "type": "sse",
      "url": "http://<docker-host>:3000/sse"
    }
  }
}
```

若你的 Claude Code 版本支援新版 Streamable HTTP：

```json
{
  "mcpServers": {
    "mssql": {
      "type": "http",
      "url": "http://<docker-host>:3000/mcp"
    }
  }
}
```

在 Claude Code 內執行 `/mcp` 確認，應會看到 `list_databases`、`list_tables`、`lookup`、`query_sql` 四個工具。

### 工具說明

| 工具 | 參數 | 說明 |
|---|---|---|
| `list_databases` | 無 | 列出所有已設定的連線名稱 |
| `list_tables` | `database?` | 列出指定資料庫中所有 TABLE/VIEW 及其欄位。若只設定一個連線可省略 `database`。 |
| `lookup` | `table`, `keyword`, `field?`, `database?` | 在資料表文字欄位上模糊搜尋（`LIKE '%keyword%'`），可指定單一 `field`。最多回傳 100 筆。 |
| `query_sql` | `sql`, `database?` | 執行自由 `SELECT` 查詢。其他任何語句類型（`INSERT`/`UPDATE`/`DELETE`/`DROP` 等）在進入資料庫前就會被拒絕。 |

當設定了多個資料庫連線時，**必須**填入 `database` 參數——先呼叫 `list_databases` 取得可用名稱。

### 環境變數設定

| 環境變數 | 是否必填 | 說明 |
|---|---|---|
| `DB_CONNECTIONS` | 是 | JSON 陣列，每個項目為 `{name, host, port?, database, user, password}`。`port` 預設 `1433`。 |
| `HTTP_PORT` | 否 | HTTP 監聽埠（預設 `3000`） |
| `MCP_TRANSPORT` | 否 | 強制指定 `stdio` 或 `http` 模式。未設定時自動判斷：stdin 被 pipe（如 Claude Code CLI 啟動）則用 `stdio`，否則用 `http`。 |

### 本機開發（不用 Docker）

```bash
npm install
npm test          # 執行單元測試（SQL 安全守衛）
npm run build      # 編譯 TypeScript
DB_CONNECTIONS='[{"name":"main","host":"...","database":"...","user":"...","password":"..."}]' npm start
```

### 安全性說明

- 安全守衛只允許以 `SELECT` 開頭的語句，**不會**特別阻擋字串字面值中包含的危險關鍵字（例如 `WHERE name = 'DROP TABLE x'` 這種合法查詢會被允許）——真正的安全邊界是資料庫帳號本身的權限，請務必只給**唯讀**（`db_datareader`）權限。
- `lookup` 工具的資料表/欄位名稱以黑名單方式擋掉能跳出 `[...]` 包裹或注入 SQL 的字元（`]`、`[`、`;`、引號、反引號、換行、SQL 註解符號），因此可以使用中文等 Unicode 名稱，同時仍防止注入。
- HTTP transport 本身沒有內建驗證機制——請務必只部署在信任的內網環境，或自行在前面加一層 reverse proxy 驗證。

### 授權

MIT（或依你的組織需求調整）