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):

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.
Every query runs inside a transaction that is always rolled back, so a statement that slips past the parser still cannot persist a change.
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:

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

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

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

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

Tests

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

Bonus: dump all module definitions

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.