# mcp-sql

An extensible, **read-only** [Model Context Protocol](https://modelcontextprotocol.io)
server for SQL databases. It lets an MCP client (Claude, etc.) explore schemas and
run `SELECT` queries safely.

- **First engine:** Microsoft SQL Server (via `pyodbc` / ODBC Driver 18).
- **Designed to extend:** new engines plug in behind a `DatabaseProvider` interface;
  auth methods plug in behind an `AuthStrategy`.
- **Transports:** local **stdio** and remote **streamable HTTP** from the same server.
- **Read-only by design:** every query is validated to be a single `SELECT`/`WITH`/`EXPLAIN`.

## Install

Requires Python 3.11+. The MS SQL engine needs the **Microsoft ODBC Driver 18**:

```bash
# macOS
brew tap microsoft/mssql-release https://github.com/microsoft/homebrew-mssql-release
brew trust microsoft/mssql-release   # newer Homebrew requires trusting 3rd-party taps
HOMEBREW_ACCEPT_EULA=Y brew install unixodbc msodbcsql18

# Debian/Ubuntu: see https://learn.microsoft.com/sql/connect/odbc/linux-mac/
```

Then install the project (with the `mssql` extra for the SQL Server driver):

```bash
uv sync --extra mssql --extra dev
# or: pip install -e ".[mssql,dev]"
```

## Configure

Copy `.env.example` to `.env` and edit. Key settings (env prefix `MCPSQL_`):

| Setting | Purpose |
| --- | --- |
| `MCPSQL_DB_TYPE` | Engine. Currently `mssql`. |
| `MCPSQL_HOST` / `MCPSQL_PORT` / `MCPSQL_DATABASE` | Connection target. |
| `MCPSQL_AUTH_METHOD` | `sql_password` \| `windows` \| `azure_ad`. |
| `MCPSQL_MAX_ROWS` | Row cap for `execute_query` (default 1000). |
| `MCPSQL_ODBC_DRIVER` | ODBC driver name (default `ODBC Driver 18 for SQL Server`). |

### Authentication matrix (MS SQL)

| `MCPSQL_AUTH_METHOD` | What it does | Extra settings |
| --- | --- | --- |
| `sql_password` | SQL Server login (username/password). | `MCPSQL_USERNAME`, `MCPSQL_PASSWORD` |
| `windows` | Integrated / trusted connection (Windows or AD-joined host). | — |
| `azure_ad` | **OAuth2** access token via Azure AD / Entra ID. | `MCPSQL_AZURE_AUTH_MODE` |

`azure_ad` token acquisition modes (`MCPSQL_AZURE_AUTH_MODE`):

- `default` — `DefaultAzureCredential` (env vars, managed identity, Azure CLI, …).
- `service_principal` — reads `AZURE_TENANT_ID`, `AZURE_CLIENT_ID`, `AZURE_CLIENT_SECRET`.
- `managed_identity` — `ManagedIdentityCredential` (for Azure-hosted workloads).

> **Security note:** the read-only validator is a guard, not a boundary. For real
> protection, connect with a least-privilege principal (e.g. a login mapped to
> `db_datareader`). Then even a validator bypass cannot write.

## Quickstart with the bundled demo database

A `docker-compose.yml` spins up SQL Server 2022 and seeds a small `AppDemo`
database (`sales.customers` / `orders` / `order_items` + a view and FKs):

```bash
docker compose up -d          # start + seed (first run pulls the image)
docker compose logs seed      # look for "Seed complete"
```

`.env.example`'s defaults already point at this database, so:

```bash
cp .env.example .env
uv run mcp-sql                # or inspect it (see below)
```

When you're done: `docker compose down -v`.

> The seed also creates a least-privilege `mcp_reader` login (password
> `Reader!Pass1`). Point `MCPSQL_USERNAME`/`MCPSQL_PASSWORD` at it to run mcp-sql
> with read-only database permissions — the recommended setup.
>
> On Apple Silicon the SQL Server image runs under amd64 emulation; first start
> takes a minute or two.

## Run

```bash
# Local stdio (default) — how MCP clients usually launch it
uv run mcp-sql

# Remote streamable HTTP
uv run mcp-sql --transport http --host 0.0.0.0 --port 8000
```

> **Endpoint authentication is not done here.** This server is designed to sit
> behind an **MCP gateway that authenticates callers**. Run it only on a network
> reachable through that gateway (private network / mTLS); for defense in depth
> bind to loopback and rely on FastMCP's DNS-rebinding (allowed-hosts) protection.
> The gateway should also strip/overwrite client-supplied connection headers it sets.

## Remote: one server, many databases

For a remote deployment the server is **not** pinned to a single database. The
caller (or gateway) names the database in a request **header**; the server
resolves that name to a connection. The wire only ever carries the *name* — never
credentials. Backend chosen by `MCPSQL_CONNECTION_BACKEND`:

| Backend | Resolves a name via | Use for |
| --- | --- | --- |
| `static` (default) | the single connection in the settings above | stdio / local / single DB |
| `map` | `MCPSQL_CONNECTIONS` JSON (`{name: "<ODBC string>"}`) | local/dev multi-DB (no Azure) |
| `keyvault` | Azure Key Vault secret `<prefix><name>` → ODBC string | production |

Request header (default `X-MCP-Connection`) selects the connection per request:

```
X-MCP-Connection: analytics      →  secret "mcpsql-conn-analytics" in Key Vault
```

**Key Vault path (production):**
- Store each database's **ODBC connection string** as a secret named
  `mcpsql-conn-<name>`. Prefer connection strings that use `azure_ad` /
  managed identity so the vault holds no SQL password.
- The server authenticates to Key Vault with `DefaultAzureCredential` (managed
  identity in Azure) — set `MCPSQL_KEYVAULT_URL`.
- The connection name is validated against `[A-Za-z0-9-]{1,120}` and the fixed
  prefix, so a header can never address an arbitrary vault secret. Add
  `MCPSQL_CONNECTION_ALLOWLIST` to restrict further.
- Resolved connections are cached for `MCPSQL_CONNECTION_CACHE_TTL` seconds
  (rotation is picked up on expiry).

Resolved connections are pooled (one connection per operation, via pyodbc's
driver-level pool), so a single process serves many databases and many
concurrent callers safely.

### Use with an MCP client (stdio)

```json
{
  "mcpServers": {
    "sql": {
      "command": "uv",
      "args": ["run", "mcp-sql"],
      "cwd": "/path/to/mcp-sql"
    }
  }
}
```

### Inspect manually

```bash
npx @modelcontextprotocol/inspector uv run mcp-sql
```

### Runnable examples

See [`examples/`](examples/) for working client scripts:
- [`examples/stdio_client.py`](examples/stdio_client.py) — local stdio (spawns the server).
- [`examples/http_client.py`](examples/http_client.py) — remote streamable HTTP, selecting the
  database per request with the `X-MCP-Connection` header.

## Tools

| Tool | Description |
| --- | --- |
| `list_schemas()` | Schemas in the database. |
| `list_tables(schema?)` | Tables and views. |
| `describe_table(table, schema?)` | Columns, types, PK, FKs, indexes. |
| `list_relationships(schema?)` | Foreign-key relationships. |
| `execute_query(sql, max_rows?)` | Run a validated read-only query. |

## Extending to another database

1. Add a provider module implementing `DatabaseProvider`
   (`src/mcp_sql/providers/base.py`).
2. Register it in `src/mcp_sql/providers/registry.py` (one line).
3. Reuse the existing `AuthStrategy` types, or add new ones under `src/mcp_sql/auth/`.
4. Add the driver as a new optional extra in `pyproject.toml`.

The MCP tool layer and the read-only validator are engine-agnostic and need no changes.

## Develop

```bash
uv run pytest
```

The `test_safety.py` and `test_config_auth.py` suites need no database.