SQL Server模型上下文协议服务器

MSSQL MCP Server

A production-grade Model Context Protocol (MCP) server for Microsoft SQL Server. Provides secure, read-only schema introspection, documentation-grade DDL generation, and opt-in query execution — all with structured logging, connection resilience, and dual transport (stdio + Streamable HTTP).

Built for Hermes Agent and compatible with any MCP client (Claude Desktop, Continue, Cursor, etc.).

Quick Start

1. Clone and build

git clone https://github.com/cmaxtt/mssql-mcp-server.git
cd mssql-mcp-server
npm install
npm run build

2. Configure

Copy .env.example to .env and fill in your database credentials:

DB_HOST=your-server
DB_PORT=1433
DB_NAME=your-database
DB_USER=your-user
DB_PASSWORD=your-password
DB_ENCRYPT=true
DB_TRUST_SERVER_CERTIFICATE=false

Alternatively, use a connection string:

DB_CONNECTION_STRING=Server=your-server;Database=your-db;User Id=your-user;Password=your-password;Encrypt=true;

3. Register with your MCP client

Hermes Agent:

hermes mcp add mssql-schema --command node --args "$(pwd)/dist/index.js" \
  --env DB_HOST=localhost DB_PORT=1433 DB_NAME=master \
  --env DB_USER=sa DB_PASSWORD=your-password \
  --env DB_ENCRYPT=false DB_TRUST_SERVER_CERTIFICATE=true

Claude Desktop — add to claude_desktop_config.json:

{
  "mcpServers": {
    "mssql-schema": {
      "command": "node",
      "args": ["/absolute/path/to/dist/index.js"],
      "env": {
        "DB_HOST": "your-server",
        "DB_PORT": "1433",
        "DB_USER": "your-user",
        "DB_PASSWORD": "your-password",
        "DB_NAME": "your-database",
        "DB_ENCRYPT": "true",
        "DB_TRUST_SERVER_CERTIFICATE": "false"
      }
    }
  }
}

4. Use it

Ask your AI: "List all tables in the dbo schema" or "Show me the DDL for tblInvoices".

Streamable HTTP Mode

node dist/index.js --transport http --port 3000

CLI flags override .env settings. HTTP security controls (origin validation, bearer tokens) are described in the Security section.

Tools Reference (12 total)

Schema Inspection

Views & Procedures

Health & Query

DDL Generation Features

The get_ddl tool generates documentation-grade DDL (not byte-perfect round-trips). It covers:

• Identity columns: seed/increment
• Computed columns: expression + PERSISTED flag
• Default constraints: with constraint names
• Primary keys: clustered/nonclustered, composite keys, ascending/descending
• Unique constraints: with constraint names
• Check constraints: with trust status noted
• Foreign keys: composite FKs, referenced columns, ON DELETE/UPDATE actions
• Non-constraint indexes: clustered/nonclustered, key columns with sort order, INCLUDEd columns, WHERE filters, disabled state
• Schema-qualified identifiers: [schema].[table] with bracket escaping
• Unsupported features: flagged as SQL comments in the output

Security

Default posture

• Query execution disabled by default (ENABLE_EXECUTE_QUERY=false)
• TLS encryption enabled by default (DB_ENCRYPT=true)
• HTTP binds to 127.0.0.1 only
• No wildcard CORS — explicit origin allowlist only
• Non-loopback HTTP binds require bearer token authentication
• Credentials never logged — passwords and connection strings are sanitized to ***
• Error messages expose a public vocabulary — internal details stay in logs

Least-privilege SQL login

Use create_least_privilege_login.sql to create a dedicated login with only the permissions needed:

• CONNECT — required
• VIEW DEFINITION ON SCHEMA::dbo — for schema inspection tools
• SELECT on specific tables — only if ENABLE_EXECUTE_QUERY=true
• Explicit DENY on ALTER, CONTROL, CREATE, DELETE, EXECUTE, INSERT, UPDATE, etc.

-- Customize and run:
-- sqlcmd -S your-server -U sa -P your-password -i create_least_privilege_login.sql

Query validation (execute_query)

When enabled, all queries pass through a fail-closed, multi-layer validator:

1. Size check: query text ≤ 32,768 bytes
2. Statement count: exactly one statement
3. AST parse: node-sql-parser (TransactSQL dialect) — rejects non-SELECT, multiple statements, unparseable syntax
4. Text blocklist: rejects SELECT INTO, OPENROWSET, OPENQUERY, OPENDATASOURCE, xp_cmdshell, sp_executesql
5. Schema/table allowlist: optional whitelist enforcement
6. Execution limits: max rows, max result bytes, request timeout, lock timeout, concurrency limit

Configuration Reference

All settings are documented in .env.example. Key variables:

Connection string and individual parameters are mutually exclusive — providing both causes a startup error.

Development

npm install              # Install dependencies
npm run build            # Compile TypeScript
npm test                 # Unit + contract tests (129 tests, ~1.3s)
npm run test:unit        # Unit tests only
npm run test:contract    # Contract tests (MCP protocol)
npm run test:integration # Docker-based integration tests (16 tests)
npm run typecheck        # TypeScript type checking
npm run test:watch       # Watch mode
npm run pack:check       # Dry-run npm pack

Integration tests

Requires Docker. Start the test SQL Server container:

MSSQL_SA_PASSWORD=YourPassword123! docker compose -f docker-compose.test.yml up -d
npm run test:integration

Without Docker, integration tests skip gracefully (16 skipped, 0 failed).

Architecture

src/
  index.ts                  — CLI parsing, dual transport (stdio/HTTP)
  config.ts                 — Zod-validated env config (30+ vars)
  logger.ts                 — Pino structured logger (stderr only)
  errors.ts                 — 8-code public error vocabulary
  db.ts                     — ConnectionPool lifecycle, retry with backoff + jitter, connection dedup
  tools.ts                  — 12 MCP tools with logging wrapper + correlation IDs
  db/
    metadata-repository.ts  — All SQL queries, sys.* catalog views, deterministic ordering
    query-executor.ts       — Safe query execution with limits, value normalization, cleanup
  query/
    query-validator.ts      — Fail-closed T-SQL validator (AST + text blocklist)
  ddl/
    ddl-builder.ts          — Complete DDL generation (identity, computed, constraints, indexes)
  tools/
    result-builders.ts      — Typed result builders with structuredContent + text dual output
  transports/
    streamable-http.ts      — HTTP server with origin validation, bearer auth, body limits

Key design principles

• Single source of truth: All configuration in config.ts, all SQL in metadata-repository.ts
• Fail-closed defaults: Query execution disabled, TLS enabled, loopback HTTP
• Structured logging: Pino JSON to stderr — stdout stays clean for MCP protocol
• Error vocabulary: 8 public error codes, internal details only in logs
• Transient-only retry: Socket errors retried with capped exponential backoff + jitter; auth/config errors fail immediately
• Connection deduplication: Concurrent callers share one in-flight connect promise
• Idempotent shutdown: SIGINT + SIGTERM handle both stdio and HTTP cleanly

Error Codes

Public error vocabulary returned in isError tool responses:

Docker

Development stack

docker compose up -d

Starts SQL Server 2022 + the MCP server.

Test stack (integration tests)

docker compose -f docker-compose.test.yml up -d

Starts a dedicated SQL Server on port 14333 with test schema objects.

Migration from Python Version

The Python server (python_version/) has been retired. All capabilities are now provided by the TypeScript server:

The Python code is preserved in git history (git checkout HEAD~1 python_version/).

License

ISC