# mcp-sql-multi

[![CI](https://github.com/rajivdatta/mcp-sql-multi/actions/workflows/ci.yml/badge.svg)](https://github.com/rajivdatta/mcp-sql-multi/actions/workflows/ci.yml)

One MCP server that fronts **several SQL Server / Azure SQL / Synapse databases**
at once. Each tool takes a `database` argument naming a target declared in
`connections.json`; call `list_databases` first to see what's available.

## Tools

| Tool | Purpose |
| --- | --- |
| `list_databases` | List configured targets (name, server, database, read-only). |
| `list_tables` | List base tables in a target (optional schema filter). |
| `describe_table` | Columns, types, lengths, nullability for a table. |
| `run_query` | Run a query, return up to 1000 rows as JSON (writes blocked on read-only targets). |

## Prerequisites

- **Python 3.10+**
- **Microsoft ODBC Driver 18 for SQL Server** — install from
  https://learn.microsoft.com/sql/connect/odbc/download-odbc-driver-for-sql-server
  (the connection strings use `DRIVER={ODBC Driver 18 for SQL Server}`).
- Network access / firewall allowance to each database server.

---

## Setup (step by step)

### 1. Install

```powershell
git clone https://github.com/rajivdatta/mcp-sql-multi.git
cd mcp-sql-multi
python -m venv .venv
.venv\Scripts\activate          # Windows  (source .venv/bin/activate on macOS/Linux)
pip install -r requirements.txt
```

### 2. Define your databases (`connections.json`)

Copy the example and edit it:

```powershell
copy connections.example.json connections.json
```

`connections.json` is git-ignored (it holds your server names and logins).
Each top-level key is a target name you'll pass to the tools. Example:

```jsonc
{
  "dev": {
    "server": "your-dev.database.windows.net",  // host
    "port": 1433,
    "database": "YourDevDb",
    "auth": "sql",                               // see auth modes below
    "user": "your_login",
    "password_env": "DEV_PASSWORD",              // env var holding the password
    "trust_cert": true,
    "readonly": false
  },
  "prod": {
    "server": "your-prod.database.windows.net",
    "port": 1433,
    "database": "YourProdDb",
    "auth": "aad-broker",                        // Windows WAM broker, silent after first sign-in, no secret
    "trust_cert": false,
    "readonly": true
  }
}
```

#### Per-target field reference

| Field | Required | Notes |
| --- | --- | --- |
| `server` | yes | Hostname (e.g. `mysrv.database.windows.net`). |
| `port` | optional | Defaults to `1433`. |
| `database` | yes | Database/pool name. |
| `auth` | yes | One of the auth modes below. |
| `user` | for `sql` | SQL login name. |
| `password_env` | for `sql` | **Name of an env var** holding the password (never the password itself). |
| `client_id` / `client_secret_env` | for `aad-service-principal` | App id + env var holding its secret. |
| `trust_cert` | optional | `true` adds `TrustServerCertificate=yes` (use for self-signed/dev). Azure SQL: `false`. |
| `readonly` | optional | `true` blocks write/DDL statements on that target. Defaults to read-only-safe. |

#### Auth modes (`auth`)

| Value | Use | Needs |
| --- | --- | --- |
| `sql` | SQL login | `user` + `password_env` |
| `aad-broker` | Entra via the Windows WAM broker; signs in once, then silently reuses the OS-cached token on every later start (recommended for prod/Synapse) | nothing — no secret stored; needs `azure-identity` + `azure-identity-broker` |
| `aad-integrated` | Azure AD via your signed-in Windows/Entra identity (silent SSO) | nothing — no secret stored |
| `aad-interactive` | Azure AD with a browser sign-in popup (**every new process**) | nothing |
| `aad-service-principal` | Azure AD app/service principal | `client_id` + `client_secret_env` |

Tip: mark prod or shared targets `"readonly": true`.

### 3. Supply passwords (`.env`)

Only needed for `sql` targets. Copy `.env.example` to `.env` and set each
variable you referenced via `password_env`:

```
DEV_PASSWORD=your-dev-sql-password
```

`.env` is git-ignored. `aad-broker` / `aad-integrated` / `aad-interactive`
targets need no secret here.

### 4. Test a connection standalone

```powershell
.venv\Scripts\activate
python -c "import db; print(db.list_targets())"               # config loads, targets listed
python -c "import server; print(server.list_tables('dev','dbo'))"   # real query against 'dev'
```

`aad-broker` targets sign in once via the Windows WAM broker, then connect
silently on later runs; `aad-interactive` targets pop a browser sign-in on the
first query of every new process.

### 5. Register with your MCP host

See [`examples/mcp.json`](examples/mcp.json) — point `command` at the venv's
Python and `args` at `server.py`. Passwords can live in `.env` (recommended) or
in the host's `env` block:

```json
{
  "mcpServers": {
    "sql-multi": {
      "command": "C:\\path\\to\\mcp-sql-multi\\.venv\\Scripts\\python.exe",
      "args": ["C:\\path\\to\\mcp-sql-multi\\server.py"],
      "env": { "DEV_PASSWORD": "set-here-or-in-.env" }
    }
  }
}
```

Restart your MCP client, then try: *"using sql-multi, list databases"* →
*"list tables in dev"*.

### Use with Claude Desktop

[Claude Desktop](https://claude.ai/download) reads its MCP servers from
`claude_desktop_config.json`. Open it from **Settings → Developer → Edit Config**
(this creates the file if it doesn't exist), or edit it directly:

- **Windows:** `%APPDATA%\Claude\claude_desktop_config.json`
- **macOS:** `~/Library/Application Support/Claude/claude_desktop_config.json`

Add this server under `mcpServers`, using **absolute paths** to the venv's
Python and `server.py` (passwords can stay in `.env` instead of `env`):

```json
{
  "mcpServers": {
    "sql-multi": {
      "command": "C:\\path\\to\\mcp-sql-multi\\.venv\\Scripts\\python.exe",
      "args": ["C:\\path\\to\\mcp-sql-multi\\server.py"],
      "env": { "DEV_PASSWORD": "set-here-or-in-.env" }
    }
  }
}
```

On macOS the paths are POSIX, e.g. `"command": "/Users/you/mcp-sql-multi/.venv/bin/python"`.
Save the file and **fully quit and reopen Claude Desktop** (use *Quit* from the
tray/menu-bar icon — closing the window isn't enough). The server's tools then
appear in the tools (🔌) menu of a new chat.

## Safety

- Read-only targets refuse write/DDL statements (regex guard).
- Results capped at 1000 rows; 60-second query timeout.
- Connections use `Encrypt=yes`; set `trust_cert` only where appropriate.
- `connections.json` and `.env` are git-ignored, so server names, logins, and
  passwords are never committed.

## License

[MIT](LICENSE) (c) 2026 Rajiv Datta