MCP SQL Server

A flexible and stable Model Context Protocol (MCP) server for Microsoft SQL Server. Supports queries, statements, metadata retrieval, and stored procedures with full Docker support.

Features

• ✅ Query Execution: Execute SELECT queries, CTEs (WITH ... SELECT), and temporary table staging (#temp, @table) without false positives

• ✅ Statement Execution: Execute INSERT, UPDATE, DELETE, and DDL statements

• ✅ Three-Tier Safety Modes: Granular control via Read-Only, Additive Mode (append-only), or Full Mutations

• ✅ MCP Tool Annotations: Dynamic readOnlyHint, destructiveHint, and openWorldHint annotations to eliminate repetitive permission prompts in Gemini and modern LLM clients

• ✅ Stored Procedures: Execute procedures with input/output parameters and auto-detected read-only safety

• ✅ Metadata Retrieval: List databases, tables, columns, and procedures

• ✅ Self-Correcting Errors: Invalid column/table names in query come back with Did you mean: ... suggestions from the real schema, and missing parameters in execute-statement and dynamic skills list what was accepted vs. received, so an AI client fixes the call in one retry

• ✅ Connection Pooling: Efficient connection management with configurable pool

• ✅ Retry Logic: Automatic reconnection with exponential backoff

• ✅ Error Handling: Comprehensive error handling and logging

• ✅ Docker Ready: Includes Dockerfile and docker-compose configuration

• ✅ Autosave to Knowledge: Optionally offloads large, token-heavy tool results silently into the knowledge base

• ✅ Dual Transport: Local stdio transport or remote modern Streamable HTTP transport (POST /mcp)

• ✅ Type Safe: Full TypeScript support with strict type checking

• ✅ Production Ready: Suitable for public repositories and enterprise use

Related MCP server: MSSQL MCP Server

Installation

Prerequisites

• Node.js 20+ or Bun 1.0+

• SQL Server 2019+ (local or remote)

• Docker & Docker Compose (for containerized setup)

Install via npm

The server is published as @pake/mssql-mcp. Run it directly with npx, no clone required — most MCP clients that spawn a stdio server can point straight at this:

npx -y @pake/mssql-mcp

{
  "mcpServers": {
    "mssql-mcp": {
      "command": "npx",
      "args": ["-y", "@pake/mssql-mcp"],
      "env": {
        "SQLSERVER_SERVER": "localhost",
        "SQLSERVER_DATABASE": "master",
        "SQLSERVER_USERNAME": "sa",
        "SQLSERVER_PASSWORD": "YourStrong@Password"
      }
    }
  }
}

See Configuration below for the full list of environment variables. For the HTTP transport, Docker, or hacking on the server itself, use the local setup below instead.

Local Setup

1. Clone the repository

git clone https://github.com/ekoeryanto/mssql-mcp.git
cd mssql-mcp

2. Install dependencies using Bun

bun install

3. Configure environment variables

cp .env.example .env
# Edit .env with your SQL Server details

4. Build the project

bun run build

5. Start the server

bun start

Docker Setup

The easiest way to get started with Docker Compose:

# Build and start both SQL Server and MCP server
docker-compose up -d

# View logs
docker-compose logs -f mcp-server

# Stop services
docker-compose down

The server listens on http://localhost:3000/mcp (Streamable HTTP). Connect a client with, e.g.:

claude mcp add --transport http mssql-mcp http://localhost:3000/mcp -H "Authorization: Bearer YourSuperSecretToken"

Configuration

Environment variables configuration:

# SQL Server Connection
SQLSERVER_SERVER=localhost
SQLSERVER_PORT=1433
SQLSERVER_DATABASE=master
SQLSERVER_USERNAME=sa
SQLSERVER_PASSWORD=YourStrong@Password

# Connection Options
SQLSERVER_ENCRYPT=false
SQLSERVER_TRUST_SERVER_CERTIFICATE=true

# Mutation Safety Modes (see "Safety Modes" below)
SQLSERVER_ALLOW_MUTATIONS=false
# SQLSERVER_ALLOW_ADDITIVE=true  # Enable append-only (INSERT allowed; UPDATE, ALTER, and destructive DDL blocked)

# Dynamic Skills feature (optional, off by default — see docs/DYNAMIC_SKILLS.md)
# SKILLS_ENABLED=false
# SKILLS_TABLE=tb_mcp_skills

# Knowledge Base feature (optional, off by default — see docs/KNOWLEDGE_BASE.md)
# KNOWLEDGE_ENABLED=false
# KNOWLEDGE_TABLE=tb_mcp_knowledge

# Autosave large tool results to the knowledge base (optional, off by
# default, requires KNOWLEDGE_ENABLED=true — see "Autosaving large results"
# in docs/KNOWLEDGE_BASE.md)
# AUTOSAVE_TO_KNOWLEDGE=false
# AUTOSAVE_THRESHOLD_CHARS=8000

# Connection Pool
SQLSERVER_CONNECTION_POOL_MIN=2
SQLSERVER_CONNECTION_POOL_MAX=10
SQLSERVER_REQUEST_TIMEOUT=30000

# Server Configuration & Transports
MCP_SERVER_NAME=mssql-mcp
LOG_LEVEL=info  # debug, info, warn, error
# MCP_SERVER_AUTH_TOKEN=YourSecretToken
# MCP_TRANSPORT=stdio  # 'stdio' (default) or 'http' (Streamable HTTP /mcp)
# PORT=3000

Development

For development with hot reload:

bun run dev

Usage

For detailed instructions on connecting this server to AI tools like Claude Desktop, Antigravity IDE, and Cursor, see our AI Client Integration Guide.

The MCP server provides the following tools:

1. Query Tool

Execute SELECT queries and retrieve results:

{
  "name": "query",
  "arguments": {
    "query": "SELECT TOP 10 * FROM your_table WHERE id > 5"
  }
}

Response:

{
  "success": true,
  "rowCount": 10,
  "columns": ["id", "name", "email"],
  "data": [
    {"id": 6, "name": "John", "email": "john@example.com"},
    ...
  ]
}

2. Execute Statement Tool

Execute INSERT, UPDATE, DELETE, or DDL statements:

{
  "name": "execute-statement",
  "arguments": {
    "statement": "INSERT INTO users (name, email) VALUES (@name, @email)",
    "params": {
      "name": "John Doe",
      "email": "john@example.com"
    }
  }
}

Response:

{
  "success": true,
  "rowsAffected": 1,
  "message": "Statement executed successfully. Rows affected: 1"
}

3. Get Metadata Tool

Retrieve database schema information:

{
  "name": "get-metadata",
  "arguments": {
    "type": "tables"
  }
}

Types:

• databases: List all databases

• tables: List all tables in current database

• columns: List columns for a specific table (requires filter)

• procedures: List all stored procedures

Example with filter:

{
  "name": "get-metadata",
  "arguments": {
    "type": "columns",
    "filter": "users"
  }
}

4. Execute Procedure Tool

Execute stored procedures with parameters:

{
  "name": "execute-procedure",
  "arguments": {
    "name": "sp_GetUserById",
    "params": {
      "userId": {
        "value": 123,
        "output": false
      },
      "userName": {
        "value": null,
        "output": true
      }
    }
  }
}

5. Get Status Tool

Check server connection status:

{
  "name": "get-status",
  "arguments": {}
}

6. Save Skill Tool

Define a new reusable SQL "skill" that becomes callable as its own tool. Explore the schema with get-metadata first, then describe the SQL and its input schema:

{
  "name": "save-skill",
  "arguments": {
    "tool_name": "cek-tagihan",
    "description": "Cek status tagihan pelanggan berdasarkan nomor pelanggan",
    "keywords": "tagihan, billing, invoice",
    "generated_prompt": "{\"type\":\"object\",\"properties\":{\"nomor\":{\"type\":\"string\",\"description\":\"Nomor pelanggan\"}},\"required\":[\"nomor\"]}",
    "generated_sql": "SELECT * FROM tb_tagihan WHERE nomor = @nomor"
  }
}

See docs/DYNAMIC_SKILLS.md for the full walkthrough, including how skills can also be inserted directly into tb_mcp_skills by hand.

Dynamic Skills

Beyond these 6 built-in tools, additional tools can be defined at runtime in a tb_mcp_skills database table — either via save-skill above, or by inserting directly into the table. See docs/DYNAMIC_SKILLS.md.

Knowledge Base

Beyond the SQL tools, this server can store and search free-form notes — table semantics, gotchas, SOP excerpts — via search-knowledge and save-knowledge, backed by a tb_mcp_knowledge database table — see docs/KNOWLEDGE_BASE.md.

Autosaving Large Results

When AUTOSAVE_TO_KNOWLEDGE=true (and KNOWLEDGE_ENABLED=true), any tool result whose text exceeds AUTOSAVE_THRESHOLD_CHARS (default 8000) — a big query, a large stored-procedure recordset, even a dynamic skill call — is saved into the knowledge base silently in the background without truncating the API response payload. This is off by default. See "Autosaving large results" in docs/KNOWLEDGE_BASE.md.

Safety Modes & MCP Annotations

To prevent AI clients (such as Gemini, Claude, or Cursor) from constantly prompting for execution confirmation on harmless queries, mssql-mcp implements dynamic MCP Tool Annotations (readOnlyHint, destructiveHint, openWorldHint) alongside three distinct safety tiers:

Temporary Tables (#temp, ##temp) and Staging

Creating, updating, and dropping session-local temporary tables (#temp, ##temp) or table variables (@table) is treated as read-only staging. It does not permanently alter database state or user tables, preventing false-positive permission blocks.

Architecture

Project Structure

mssql-mcp/
├── src/
│   ├── index.ts              # Main MCP server entry point
│   ├── config/
│   │   └── index.ts          # Configuration loader
│   ├── db/
│   │   └── connection.ts      # SQL Server connection manager
│   ├── logger/
│   │   └── index.ts          # Logger implementation
│   ├── tools/
│   │   └── handlers.ts       # Tool request handlers
│   └── types/
│       └── index.ts          # TypeScript type definitions
├── docker-compose.yml         # Docker Compose configuration
├── Dockerfile                 # Multi-stage Docker build
├── package.json              # Dependencies and scripts
├── tsconfig.json             # TypeScript configuration
└── .env.example              # Environment variables template

Connection Management

The connection manager implements:

• Connection Pooling: Configurable min/max pool size

• Automatic Reconnection: Retry logic with exponential backoff

• Error Handling: Graceful error handling and logging

• Keep-Alive: Continuous connection monitoring

Security Considerations

Creating a Read-Only User (T-SQL)

Run the following T-SQL script in your SQL Server to create a dedicated read-only user for this MCP server:

-- 1. Switch to your target database
USE [YourDatabaseName];
GO

-- 2. Create a login (Server level)
CREATE LOGIN [mcp_readonly_user] WITH PASSWORD = 'YourStrongPassword123!';
GO

-- 3. Create a user for the login (Database level)
CREATE USER [mcp_readonly_user] FOR LOGIN [mcp_readonly_user];
GO

-- 4. Grant read-only permissions (db_datareader)
ALTER ROLE [db_datareader] ADD MEMBER [mcp_readonly_user];
GO

-- 5. (Optional) Grant view definition if the AI needs to inspect schemas/tables structure
GRANT VIEW DEFINITION TO [mcp_readonly_user];
GO

Best Practices

1. Environment Variables: Never commit .env file with real credentials

2. Parameter Binding: Always use parameterized queries to prevent SQL injection

3. Connection Pooling: Limits resource consumption

4. Timeout Settings: Prevents long-running queries from blocking

API Reference

Tool Definitions

Each tool follows the MCP specification with:

• name: Unique tool identifier

• description: What the tool does

• inputSchema: JSON Schema for input validation

Error Handling

All tools return a consistent error format:

{
  "success": false,
  "error": "Descriptive error message"
}

Development

Running Tests

bun run test

Linting

bun run lint

Building for Production

bun run build

Deployment

Docker Compose

For quick deployment with SQL Server:

docker-compose up -d

Kubernetes

Example Kubernetes deployment coming soon.

Custom Environment

To use with an existing SQL Server instance:

1. Set environment variables

2. Run bun start

3. The server will connect via stdio transport

Performance Considerations

• Connection Pool Size: Adjust based on concurrent usage

• Query Timeouts: Configure SQLSERVER_REQUEST_TIMEOUT based on query complexity

• Database Indexes: Ensure proper indexing for query performance

• Logging Level: Use warn or error in production to reduce overhead

Troubleshooting

Connection Failures

Check environment variables:

env | grep SQLSERVER

Query Timeouts

Increase SQLSERVER_REQUEST_TIMEOUT:

SQLSERVER_REQUEST_TIMEOUT=60000  # 60 seconds

Pool Exhaustion

Increase pool size:

SQLSERVER_CONNECTION_POOL_MAX=20

Debug Logging

Set log level to debug:

LOG_LEVEL=debug

Contributing

1. Fork the repository

2. Create a feature branch

3. Make your changes

4. Submit a pull request

License

MIT License - see LICENSE file for details

Support

For issues and questions:

• GitHub Issues: Create an issue

• Discussions: Start a discussion

References

• Model Context Protocol

• mssql-js Documentation

• SQL Server Documentation