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:

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.