MSSQL Model Context Protocol (MCP) Server

A Node.js implementation of the Model Context Protocol server for Microsoft SQL Server. Exposes a configured database (or set of databases) to an MCP client via 11 introspection and query tools, table resources, and guided prompts - over either stdio or Streamable HTTP.

Quick start

npm install
cp .env.example .env   # then edit credentials
npm start              # stdio transport (Claude Desktop, VS Code, etc.)
npm run start:http     # Streamable HTTP transport on :3000 (POST /mcp)

By default the server is read-only. Set MSSQL_ENABLE_WRITES=true to opt into execute_write_query.

Breaking changes from 2.x

2.x	3.x
execute_sql (regex-based safety check)	execute_read_query + execute_write_query (gated by MSSQL_ENABLE_WRITES)
get_table_schema	describe_table
list_databases	list_databases (richer output + pool status)
Bespoke REST API in src/express.js	MCP Streamable HTTP transport at POST /mcp
New ConnectionPool per call	Per-dbKey pool, cached and reused
get_table_schema only	+ list_tables, list_views, list_indexes, list_foreign_keys, list_stored_procedures, describe_database, describe_procedure
-	Prompts: explore_database, summarize_table

Multi-Database Support

Two modes; the server auto-detects from the environment:

1. Single-database - MSSQL_* variables, exposed as dbKey="maindb"
2. Multi-database - MSSQL_<NAME>_* variables, one config per <NAME> (lowercased)

Single-database mode

MSSQL_SERVER=your_sql_server_address
MSSQL_PORT=1433
MSSQL_USER=your_username
MSSQL_PASSWORD=your_password
MSSQL_DATABASE=your_database_name
MSSQL_ENCRYPT=true
MSSQL_TRUST_SERVER_CERTIFICATE=false

Multi-database mode

MSSQL_MAINDB_SERVER=...
MSSQL_MAINDB_USER=...
MSSQL_MAINDB_PASSWORD=...
MSSQL_MAINDB_DATABASE=main_db_name
MSSQL_MAINDB_ENCRYPT=true
MSSQL_MAINDB_TRUST_SERVER_CERTIFICATE=false

MSSQL_REPORTINGDB_SERVER=...
MSSQL_REPORTINGDB_USER=...
MSSQL_REPORTINGDB_PASSWORD=...
MSSQL_REPORTINGDB_DATABASE=reporting_db_name
MSSQL_REPORTINGDB_ENCRYPT=true
MSSQL_REPORTINGDB_TRUST_SERVER_CERTIFICATE=false

Custom names work the same way - MSSQL_ANALYTICS_* exposes dbKey="analytics", etc. Per-database credentials fall back to the global MSSQL_USER / MSSQL_PASSWORD / MSSQL_SERVER if omitted.

Configure either single-database or multi-database variables, not both. If any MSSQL_<NAME>_DATABASE is present, multi-db wins.

Write opt-in

MSSQL_ENABLE_WRITES=true   # enables execute_write_query; defaults to false

When disabled, execute_write_query returns an error before any connection attempt. execute_read_query always runs inside a transaction that is rolled back regardless of outcome, so accidental writes inside a "read" query are non-durable.

For real safety, also give the configured DB user only the grants you intend it to have - least privilege is the source of truth, not the tool split.

Environment variables

Consolidated reference. All 2.x variables still work identically - the only additions in 3.x are MSSQL_ENABLE_WRITES and the MSSQL_TEST_* family (integration-script only, never read by the server).

Database connection (used by the MCP server)

Variable	Mode	Required	Default	Notes
MSSQL_SERVER	single	yes	localhost	Hostname or IP.
MSSQL_PORT	single	no	mssql default	Coerced to integer.
MSSQL_USER	single	yes	-	Login name.
MSSQL_PASSWORD	single	yes	-	-
MSSQL_DATABASE	single	yes	-	Exposed as dbKey="maindb".
MSSQL_ENCRYPT	single	no	false	Set true to encrypt the connection.
MSSQL_TRUST_SERVER_CERTIFICATE	single	no	true	Set false to enforce certificate validation.
MSSQL_<NAME>_SERVER	multi	no	global	Falls back to MSSQL_SERVER if omitted.
MSSQL_<NAME>_PORT	multi	no	mssql default	-
MSSQL_<NAME>_USER	multi	no	global	Falls back to MSSQL_USER.
MSSQL_<NAME>_PASSWORD	multi	no	global	Falls back to MSSQL_PASSWORD.
MSSQL_<NAME>_DATABASE	multi	yes (per DB)	-	Presence of any _DATABASE switches the server into multi-db mode. Exposed as dbKey="<name>" (lowercased).
MSSQL_<NAME>_ENCRYPT	multi	no	false	-
MSSQL_<NAME>_TRUST_SERVER_CERTIFICATE	multi	no	true	-

Server behavior

Variable	Required	Default	Effect
MSSQL_ENABLE_WRITES	no	false	When true, execute_write_query is allowed to run. With it unset/false, the tool errors out before any connection attempt.
PORT	no	3000	HTTP transport only (npm run start:http). Ignored in stdio mode.

Integration script (scripts/integration.js, never read by the server)

Variable	Required	Default	Notes
MSSQL_TEST_SERVER	no	localhost	Target SQL Server (Docker, LocalDB, anywhere).
MSSQL_TEST_PORT	no	1433	-
MSSQL_TEST_USER	no	sa	-
MSSQL_TEST_PASSWORD	yes	-	The script exits 2 without it.

Single vs multi: configure either the bare MSSQL_* variables or the prefixed MSSQL_<NAME>_* variables - not both. If any MSSQL_<NAME>_DATABASE is present, multi-db mode wins. Your existing 2.x .env continues to work unchanged.

Transports

Stdio (default for MCP clients)

npm start

Runs src/index.js. Use this from Claude Desktop, VS Code MCP, or any client that spawns the server as a subprocess.

Streamable HTTP

npm run start:http       # listens on $PORT (default 3000)

Endpoint: POST /mcp (JSON-RPC 2.0). The server runs in stateless mode - every POST gets its own server instance - which is easier to scale and matches the SDK's recommended default. GET /mcp and DELETE /mcp return 405 (no SSE streams in stateless mode).

GET /healthz returns { ok: true } for liveness checks.

Smoke test

curl -sS -X POST http://localhost:3000/mcp \
  -H "Content-Type: application/json" \
  -H "Accept: application/json, text/event-stream" \
  -d '{"jsonrpc":"2.0","id":1,"method":"tools/list"}'

Tool catalog

All tools accept an optional dbKey. In single-database mode the default is maindb; in multi-database mode it's the first key loaded.

Every tool returns both human-readable content (JSON text) and parsed structuredContent (the same payload as a typed object).

Query tools

Tool	Annotations	Notes
execute_read_query	readOnly, idempotent	Streamed, rollback-only. Server cancels after offset + limit rows. Inputs: query, optional dbKey, limit (≤1000, default 100), offset.
execute_write_query	destructive	Requires MSSQL_ENABLE_WRITES=true. Inputs: query, optional dbKey.

Catalog (paginated)

Tool	Inputs
list_databases	-
describe_database	optional dbKey
list_tables	optional dbKey, limit, offset
list_views	optional dbKey, limit, offset
list_stored_procedures	optional dbKey, limit, offset

Per-object inspection

Tool	Inputs
describe_table	table (bare or schema.table), optional dbKey
describe_procedure	procedure, optional dbKey
list_indexes	table, optional dbKey
list_foreign_keys	optional table (whole-DB if omitted), dbKey

Example: execute_read_query

{
  "jsonrpc": "2.0",
  "id": 1,
  "method": "tools/call",
  "params": {
    "name": "execute_read_query",
    "arguments": {
      "query": "SELECT TOP 5 * FROM dbo.Users",
      "dbKey": "maindb",
      "limit": 5
    }
  }
}

Result structuredContent:

{
  "db": "your_database",
  "dbKey": "maindb",
  "rowCount": 5,
  "totalRowsReturnedByQuery": 5,
  "truncated": false,
  "recordset": [{ "id": 1, "name": "Item1", "created_at": "2025-01-01" }]
}

Resources

The server exposes one resource template:

mssql://<dbKey>@<schema>.<table>/data

Reading the resource returns the first 100 rows as CSV with a leading # Database: <name> comment. resources/list enumerates every base table across every configured dbKey (capped at 500 tables per DB to bound the response).

Prompts

Prompt	Args	Purpose
explore_database	optional dbKey	Step-by-step instructions for surveying an unknown database.
summarize_table	table, optional dbKey	Produces a column/index/FK/sample-rows brief on one table.

Integration with Claude Desktop or VS Code

The package ships an mssql-mcp-node bin, so you can invoke it via npx.

Single-database

{
  "servers": {
    "mssql-mcp-node": {
      "command": "npx",
      "args": ["-y", "mssql-mcp-node"],
      "env": {
        "MSSQL_SERVER": "your_server_name",
        "MSSQL_PORT": "1433",
        "MSSQL_USER": "your_username",
        "MSSQL_PASSWORD": "your_password",
        "MSSQL_DATABASE": "your_database",
        "MSSQL_ENCRYPT": "true",
        "MSSQL_TRUST_SERVER_CERTIFICATE": "false"
      }
    }
  }
}

Multi-database

{
  "servers": {
    "mssql-mcp-node": {
      "command": "npx",
      "args": ["-y", "mssql-mcp-node"],
      "env": {
        "MSSQL_MAINDB_SERVER": "your_server_name",
        "MSSQL_MAINDB_USER": "your_username",
        "MSSQL_MAINDB_PASSWORD": "your_password",
        "MSSQL_MAINDB_DATABASE": "main_database",
        "MSSQL_MAINDB_ENCRYPT": "true",
        "MSSQL_MAINDB_TRUST_SERVER_CERTIFICATE": "false",

        "MSSQL_REPORTINGDB_SERVER": "your_server_name",
        "MSSQL_REPORTINGDB_USER": "your_username",
        "MSSQL_REPORTINGDB_PASSWORD": "your_password",
        "MSSQL_REPORTINGDB_DATABASE": "reporting_database",
        "MSSQL_REPORTINGDB_ENCRYPT": "true",
        "MSSQL_REPORTINGDB_TRUST_SERVER_CERTIFICATE": "false"
      }
    }
  }
}

To enable writes, also add "MSSQL_ENABLE_WRITES": "true". Use a dedicated SQL login with the minimum grants the workload needs.

Architecture

src/
├── index.js              # stdio entry
├── http.js               # Streamable HTTP entry
├── server.js             # McpServer factory
├── config.js             # env -> validated connection configs
├── validation.js         # shared Zod shapes
├── resources.js          # ResourceTemplate registration
├── prompts.js            # MCP prompts
├── db/
│   ├── pools.js          # per-dbKey ConnectionPool cache
│   ├── safety.js         # runRead (rollback-only) + runWrite (gated)
│   └── introspection.js  # parameterized INFORMATION_SCHEMA / sys.* queries
└── tools/
    ├── index.js          # tool barrel
    ├── execute-read-query.js
    ├── execute-write-query.js
    ├── list-databases.js
    ├── describe-database.js
    ├── list-tables.js
    ├── list-views.js
    ├── list-indexes.js
    ├── list-foreign-keys.js
    ├── list-stored-procedures.js
    ├── describe-table.js
    └── describe-procedure.js

Security model

• Read isolation - execute_read_query and every list_*/describe_* tool runs inside a transaction that is always rolled back. This is a guardrail against accidental writes (a SELECT ... INTO new_table, an INSERT smuggled past a comment), not a sandbox against an adversarial query: an explicit COMMIT TRANSACTION inside the user's SQL ends the outer transaction, and following statements run in autocommit mode. Use a least-privilege SQL login if you need real isolation against intentional misuse.
• Write opt-in - execute_write_query is gated by MSSQL_ENABLE_WRITES=true. When disabled it errors out before a connection is acquired, so no resources are spent and no probing is possible.
• Parameterized introspection - every list_*/describe_* SQL uses @param placeholders rather than string concatenation; table identifiers are restricted by Zod to /^[a-zA-Z0-9_#$@]+(?:\.[a-zA-Z0-9_#$@]+)?$/ (bare Users or two-part dbo.Users - no spaces, brackets, or three-part names) and bracket-quoted ([schema].[table]) for the CSV resource path. Identifiers with spaces or non-ASCII characters aren't supported by the introspection tools; use execute_read_query with raw SQL for those.
• Cancellation - tool handlers honor the MCP request AbortSignal; an aborted request fires request.cancel() on the underlying mssql request.
• Least privilege - the safest setup is a SQL login with only SELECT (and EXECUTE if needed) on the relevant schemas. The MCP layer reinforces that, it doesn't replace it.

Testing

npm test

Runs unit tests with the built-in node --test runner. No external DB needed - the suite covers config parsing, validation, identifier escaping, the rollback-only contract, pool caching/retry, parameterized SQL placeholders, and tool registration metadata.

For a manual smoke test against the HTTP transport:

PORT=3000 npm run start:http &
curl -s http://localhost:3000/healthz
curl -s -X POST http://localhost:3000/mcp \
  -H "Content-Type: application/json" \
  -H "Accept: application/json, text/event-stream" \
  -d '{"jsonrpc":"2.0","id":1,"method":"tools/list"}'

For interactive end-to-end testing against a real database, use the MCP Inspector:

npx @modelcontextprotocol/inspector node src/index.js

Real-DB integration test (disposable)

npm run integration spins up a throwaway database, exercises every tool against real SQL Server, and drops it. The script also covers the streaming-cutoff and rollback-isolation paths that the unit tests can only mock.

Easiest setup is a one-shot Docker container:

docker run -d --name mcp-mssql-test \
  -e "ACCEPT_EULA=Y" \
  -e "MSSQL_SA_PASSWORD=YourStr0ng!Passw0rd" \
  -p 1433:1433 \
  mcr.microsoft.com/mssql/server:2022-latest

# wait ~10s for SQL Server to initialize, then:
MSSQL_TEST_PASSWORD='YourStr0ng!Passw0rd' npm run integration

# full cleanup:
docker rm -f mcp-mssql-test

The script creates mcp_test_<timestamp> inside the server, seeds it (Users, Orders with FK + index, a view, a stored procedure, 503 rows), runs ~20 tool-level assertions, and drops the database in a finally block - even on failure.

Override targets via MSSQL_TEST_SERVER, MSSQL_TEST_PORT, MSSQL_TEST_USER if you'd rather point it at an existing SQL Server, LocalDB, or Azure SQL.

License

MIT - see LICENSE.

Author

Mihai-Nicolae Dulgheru mihai.dulgheru18@gmail.com