# mssql-mcp

Local MCP server (stdio) for running ad hoc T-SQL queries against SQL Server databases, read-only by default.

Context: the official Microsoft sample `Azure-Samples/SQL-AI-samples/MssqlMcp` was removed in June 2026 ("unsafe MCP sample"), and its official replacement (Data API builder SQL MCP Server) does not allow free-form SQL. This small server (official MCP SDK + the `mssql` driver) fills the gap.

## Security model

**Use a SQL login restricted to `db_datareader`.** That is the only read-only guarantee that actually holds. This server assumes it is driven by an LLM that reads untrusted database content, so a prompt injection can make it emit any query it is allowed to emit.

On top of that, three defences apply in read-only mode (the default):

1. Queries are parsed with a tokeniser that understands string literals, quoted and bracketed identifiers, and nested block comments. Only a single `SELECT`/`WITH` statement is accepted, and write, DDL, execution and pass-through keywords are rejected anywhere in the statement — including after a CTE, where `WITH … DELETE` is otherwise valid T-SQL.
2. Every query runs inside a transaction that is always rolled back, so a statement that slips past the parser still cannot persist a change.
3. Rows are streamed and the query is cancelled once `maxRows` is reached, so an unbounded `SELECT` cannot exhaust memory.

Set `MSSQL_MCP_READONLY=false` to lift 1 and 2.

## Configuration

Credentials are read from a `.env` file resolved next to `index.js` (see `.env.example`), or from the environment. Copy the example and fill it in:

```bash
cp .env.example .env
```

| Variable | Default | Purpose |
| --- | --- | --- |
| `DB_CONFIG_HOST` | — | Server hostname (required) |
| `DB_CONFIG_PORT` | `1433` | Server port |
| `DB_CONFIG_DATABASE` | — | Default database |
| `DB_CONFIG_USER` / `DB_CONFIG_PASSWORD` | — | Credentials (required) |
| `DB_CONFIG_ENCRYPT` | `true` | Encrypt the connection |
| `DB_CONFIG_TRUST_SERVER_CERTIFICATE` | `false` | Skip certificate validation — see below |

Server options:

| Variable | Default | Purpose |
| --- | --- | --- |
| `MSSQL_MCP_ENV_FILE` | `.env` next to `index.js` | Alternate .env location |
| `MSSQL_MCP_READONLY` | `true` | `false` to allow INSERT/UPDATE/DELETE/EXEC |
| `MSSQL_MCP_REQUEST_TIMEOUT_MS` | `30000` | Query timeout |
| `MSSQL_MCP_ALLOWED_DATABASES` | unset | Comma-separated allowlist; also filters `list_databases` |
| `MSSQL_MCP_MAX_POOLS` | `8` | Connection pools kept open; the least recently used one is closed to make room |

`DB_CONFIG_TRUST_SERVER_CERTIFICATE=true` encrypts the connection without authenticating the server, which leaves it open to interception. Prefer installing the server's CA certificate. The server prints a warning at startup when this is enabled.

Without `MSSQL_MCP_ALLOWED_DATABASES`, every database the login can reach is queryable via the `database` parameter. Roaming across more databases than `MSSQL_MCP_MAX_POOLS` is fine — pools are recycled, not refused.

## Exposed tools

- `read_data` — ad hoc T-SQL query, `maxRows` defaults to 100, capped at 1000, optional `database` param
- `list_objects` — tables/views/procs/functions, LIKE filter (e.g. `%ORDER%`)
- `describe_object` — columns + parameters (e.g. `dbo.GetOrderTotals`)
- `get_definition` — T-SQL source of a view/proc/function via `OBJECT_DEFINITION`
- `list_databases` — databases available on the server

## Registering with Claude Code

```bash
claude mcp add --scope user mssql -- node /path/to/mssql-mcp/index.js
```

For a read-write server (on demand, one-off session):

```bash
claude mcp add --scope local mssql-rw --env MSSQL_MCP_READONLY=false -- node /path/to/mssql-mcp/index.js
```

## Tests

```bash
pnpm test          # offline: read-only guard regression suite
pnpm smoke         # end-to-end, needs a reachable database
```

## Bonus: dump all module definitions

```bash
node dump-definitions.mjs [out-dir]   # default: ./sql-definitions
```

Dumps the T-SQL source of every proc/view/function/trigger of the configured database, one file per object. Requires `GRANT VIEW DEFINITION` for the login. Object names are sanitised before being used as filenames, since SQL Server allows path separators inside bracketed identifiers; objects whose sanitised names collide are skipped and reported rather than silently overwritten.