mssql-mcp

A read-only MCP server for Microsoft SQL Server. It lets an MCP client run SELECT queries against a database and inspect how those queries perform.

It provides two main capabilities:

1. Run SELECT queries and return the rows as JSON.
2. Analyze a SELECT under SET STATISTICS XML/IO/TIME and return performance data as JSON: estimated vs. actual rows, logical and physical reads, statement timing, missing-index suggestions, and plan warnings.

For a local Docker/Colima setup, see local-readme.md. This document covers a Windows machine connecting to a remote SQL Server.

Tools

Tool	Description
query	Run a single SELECT and return the rows as JSON.
analyze_query	Run a SELECT with statistics collection enabled and return performance metrics.
list_tables	List tables and views in the current database.
describe_table	Column names, types, nullability, and defaults for a table.

By default only SELECT and WITH statements are permitted. See Security.

Requirements

• Node.js 18 or later. Install the LTS build from https://nodejs.org and confirm with node -v.
• Network access from this machine to the SQL Server. The default port is TCP 1433; make sure any firewall between the two allows it.
• A SQL Server Authentication login (username and password) with read access to the target database.

Setup

Open PowerShell in the project folder and run:

cd C:\Tools\mssql-mcp
copy .env.example .env
npm install
npm run build

Then edit .env with the details for your server (see the next section).

Connecting to a remote server

.env holds the connection settings. It is git-ignored and is the only place credentials are stored.

MSSQL_SERVER=sqlserver.corp.example.com
MSSQL_PORT=1433
MSSQL_USER=reporting_user
MSSQL_PASSWORD=your-password
MSSQL_DATABASE=Sales
MSSQL_ENCRYPT=true
MSSQL_TRUST_SERVER_CERT=false

• MSSQL_SERVER can be a hostname, fully qualified domain name, or IP address. For a named instance, use HOST\INSTANCE and set MSSQL_PORT to that instance's port.
• MSSQL_ENCRYPT=true encrypts the connection. Keep it on for a remote server.
• MSSQL_TRUST_SERVER_CERT=false requires the server to present a certificate your machine already trusts. Set it to true only when the server uses a self-signed certificate.
• To point at a different server or database later, edit .env and restart the client.

Verify the connection before wiring up a client:

node dist\index.js

It prints a readiness line to standard error and then waits for input. Press Ctrl+C to stop. If the connection fails, the error message states the reason.

Registering with a client

The client starts the server as a subprocess and communicates over standard input/output. Credentials stay in .env; the client configuration holds no secrets.

opencode (opencode.json):

{
  "$schema": "https://opencode.ai/config.json",
  "mcp": {
    "mssql": {
      "type": "local",
      "command": ["node", "C:\\Tools\\mssql-mcp\\dist\\index.js"],
      "enabled": true
    }
  }
}

Claude Code (.mcp.json):

{
  "mcpServers": {
    "mssql": {
      "command": "node",
      "args": ["C:\\Tools\\mssql-mcp\\dist\\index.js"]
    }
  }
}

Use the full path to dist\index.js, and double the backslashes in JSON. Start the client from the folder that contains its configuration file. Any env values set in the client configuration take precedence over .env.

analyze_query output

{
  "row_count": 100,
  "truncated": false,
  "rows": [ /* result rows, capped at maxRows */ ],
  "timing": { "cpu_ms": 53, "elapsed_ms": 53 },
  "io_by_table": [
    { "table": "Orders", "scan_count": 1, "logical_reads": 283,
      "physical_reads": 0, "read_ahead_reads": 0 }
  ],
  "statement": { "est_rows": 100, "subtree_cost": 0.30 },
  "operators": [
    { "node_id": 0, "parent_node_id": null, "depth": 0,
      "physical_op": "Sort", "cost_pct": 100 },
    { "node_id": 1, "parent_node_id": 0, "depth": 1,
      "physical_op": "Clustered Index Scan",
      "est_rows": 100, "actual_rows": 100, "rows_read": 50000,
      "actual_logical_reads": 283, "cost_pct": 88 }
  ],
  "missing_indexes": [
    { "impact_pct": 94.6, "table": "dbo.Orders",
      "create": "CREATE INDEX IX_Orders_CustomerId ON [dbo].[Orders] ([CustomerId]) INCLUDE ([Amount]);" }
  ],
  "warnings": []
}

Reads and timing come from the plan's runtime counters (ActualLogicalReads, QueryTimeStats). io_by_table is parsed from the STATISTICS IO messages, so it matches the Messages tab in SQL Server Management Studio.

Operators are returned in tree order. Each carries node_id, parent_node_id, and depth. The root (node_id 0) is the final step and runs last; the deepest leaf runs first and feeds its parent up toward the root.

Logging

Set MSSQL_LOG=true in .env to append a record of every tool call, including the arguments received and the result returned, to mssql-mcp\mcp.log. The file is git-ignored.

[2026-07-05T15:06:26Z]  CALL #2  query
INPUT (from client):
  { "sql": "SELECT TOP 2 name FROM sys.tables ORDER BY name" }
[2026-07-05T15:06:27Z]  DONE #2  query  ·  1002 ms
OUTPUT (to client):
  { "row_count": 2, "rows": [ { "name": "Orders" } ] }

Each call is numbered so a request and its response can be matched even when calls overlap. Set MSSQL_LOG_FILE for a custom path and MSSQL_LOG_MAX for the maximum characters per entry before truncation (default 20000).

To follow the log live in PowerShell:

Get-Content .\mcp.log -Wait

Rate limiting

Two limits guard against overloading the server, both configured in .env:

• MSSQL_RATE_MAX requests per MSSQL_RATE_WINDOW_MS milliseconds.
• MSSQL_MAX_CONCURRENT queries in flight at once.

Set any of them to 0 to disable that limit. When a limit is reached, the tool returns an error with a retry hint and does not contact the database.

Security

MSSQL_READONLY=true (the default) rejects anything that is not a single SELECT or WITH statement and blocks write and DDL keywords. This is a convenience check, not a replacement for database permissions.

Enforce read-only access at the server by connecting with a login that has read rights only:

CREATE LOGIN reporting_user WITH PASSWORD = 'your-password';
CREATE USER reporting_user FOR LOGIN reporting_user;
ALTER ROLE db_datareader ADD MEMBER reporting_user;

Point MSSQL_USER and MSSQL_PASSWORD at that login. Set MSSQL_READONLY=false only when you intend to allow writes.