ms-mcp

stdio MCP server for Microsoft SQL Server.

Features

• Connects to one SQL Server instance using credentials from .env
• Supports multiple named SQL Server connections in one MCP server
• Executes queries against different databases by passing database on each query call
• Executes confirmed write/admin statements through a separate write_query tool
• Reads full SQL object definitions in chunks with get_object_definition
• Keeps query read-only and requires explicit confirmation on every write_query call
• Returns short text plus structured JSON responses for every MCP tool

Requirements

• Node.js 20+
• Reachable Microsoft SQL Server

Setup

1. Copy .env.example to .env
2. Fill in either single-connection variables or multi-connection variables from .env.example
3. Install dependencies with npm install
4. Start in dev mode with npm run dev or build with npm run build && npm start

Local MSSQL via Docker

1. Run docker compose up -d
2. Wait until ms-mcp-mssql becomes healthy and mssql-init exits successfully
3. Copy .env.example to .env and set:
   • MSSQL_SERVER=localhost
   • MSSQL_PORT=1433
   • MSSQL_USER=sa
   • MSSQL_PASSWORD=YourStrong!Passw0rd
4. Start the server with npm run dev

This seeds AppDb and ReportingDb for multi-db smoke checks.

Configuration

• MSSQL_SERVER: SQL Server host or instance address
• MSSQL_PORT: SQL Server port, default 1433
• MSSQL_DOMAIN: optional Windows domain for NTLM login, for example EXAMPLE
• MSSQL_USER: login name
• MSSQL_PASSWORD: login password
• MSSQL_ALLOW_WRITE: false by default; set true only for connections that may be used by write_query
• MSSQL_ENCRYPT: TLS encryption flag, default true
• MSSQL_TRUST_SERVER_CERTIFICATE: trust server certificate, default false
• MSSQL_CONNECTION_TIMEOUT_MS: connection timeout, default 15000
• MSSQL_REQUEST_TIMEOUT_MS: request timeout, default 30000
• MSSQL_MAX_ROWS: maximum returned rows before truncation, default 1000
• MSSQL_MAX_RESULT_BYTES: maximum JSON payload size for returned rows, default 1048576

Multi-connection mode:

• MSSQL_CONNECTION_NAMES: comma-separated connection names, for example default,reporting
• MSSQL_DEFAULT_CONNECTION: optional default connection name used when tool input omits connection
• Per-connection variables use MSSQL_<NAME>_..., for example MSSQL_DEFAULT_SERVER or MSSQL_REPORTING_DOMAIN
• Connection names are normalized to env keys by replacing non-alphanumeric characters with _ and uppercasing

Tools

healthcheck

Checks connectivity to SQL Server and whether database listing is available. Input accepts optional connection and optional database.

If database is provided, healthcheck also verifies that the selected connection can open that specific database, not just master.

Example:

{
  "connection": "reporting",
  "database": "Orchestrator"
}

list_connections

Returns configured connection names so clients can discover them without guessing. No input is required.

list_databases

Returns databases from sys.databases with name, isSystemDatabase, isAccessible. Input accepts optional connection.

query

Read-only tool. It accepts only SELECT and WITH statements, even on write-enabled connections.

Input:

{
  "connection": "reporting",
  "database": "MyDb",
  "sql": "select * from Users where Id = @id",
  "params": [
    { "name": "id", "type": "Int", "value": 42 }
  ]
}

Successful structuredContent envelope:

{
  "ok": true,
  "code": "ok",
  "message": "Query executed successfully",
  "data": {
    "rows": [],
    "rowCount": 0,
    "rowsAffected": [],
    "columns": [],
    "recordsets": 1,
    "truncated": false
  },
  "meta": {
    "server": "mssql.example.local",
    "database": "MyDb",
    "executionTimeMs": 12,
    "writeEnabled": false
  }
}

Error envelope example:

{
  "ok": false,
  "code": "write_blocked",
  "message": "Only SELECT or WITH queries are allowed in safe mode",
  "details": {
    "detectedCommand": "INSERT"
  }
}

write_query

Write/admin tool. It only works on connections where MSSQL_ALLOW_WRITE=true, requires explicit per-call confirmation, and only accepts a single statement.

Input:

{
  "connection": "default",
  "database": "sample_db",
  "sql": "ALTER FUNCTION dbo.msg(@msg nvarchar(max)) RETURNS varchar(max) AS BEGIN RETURN @msg END",
  "confirm": true
}

If confirm is missing or false, the tool rejects the call without sending SQL to the server.

get_object_definition

Reads a stored procedure, view, function, or trigger definition from sys.sql_modules. Useful when query result formatting is too compact for large object bodies.

Input:

{
  "connection": "reporting",
  "database": "MyDb",
  "schema": "dbo",
  "object_name": "usp_DoWork",
  "offset": 1,
  "limit": 12000
}

Notes:

• offset is a 1-based character offset in the definition text
• limit is the maximum number of returned characters, up to 50000
• Use nextOffset from the response to fetch the next chunk for large procedures

Safe-mode behavior

query always stays read-only and only allows statements that start with SELECT or WITH.

write_query is available for connections with MSSQL_ALLOW_WRITE=true, but every call must include confirm=true and is limited to a single SQL statement.

Example:

{
  "connection": "default",
  "database": "sample_db",
  "sql": "ALTER FUNCTION dbo.msg(@msg nvarchar(max)) RETURNS varchar(max) AS BEGIN RETURN @msg END",
  "confirm": true
}

Both tools block batches and known unsafe patterns when they do not match the tool's purpose.

This is a practical safeguard, not a full SQL sandbox.

Testing

• Run unit tests with npm test
• Run integration tests against Docker MSSQL with npm run test:integration
• Run TypeScript build with npm run build

test:integration is opt-in and expects Docker MSSQL to be up with seeded databases from docker compose up -d.

Client config examples

See docs/mcp-client-config.md for Claude Desktop, Cursor, and OpenCode examples.