shaharwiener/mcp-sql-server

SQL Server MCP

A secure Model Context Protocol (MCP) server for Microsoft SQL Server that provides safe, read-only database access with comprehensive protection layers.

Features

• Read-Only Query Execution - Execute SELECT queries with automatic pagination support
• SQL Script Review - Comprehensive analysis with 42 automated best practice checks and risk scoring
• Schema Discovery - Get database metadata (tables, columns, types) in token-efficient format
• Execution Plan Analysis - Retrieve and analyze SQL Server query execution plans
• Multi-Environment Support - Switch between Int, Stg, and Prd environments
• Best Practices Engine - Access 50 DBA-defined SQL Server best practice rules

How the MCP Protects the Database

The MCP implements multi-layer protection at both the SQL Server (database) level and the MCP (application) level:

Database-Level Protections (SQL Server Query Hints)

These protections are enforced directly by SQL Server through query hints automatically injected into every query:

1. CPU Control (MAXDOP) - Limits CPU parallelism per query:

   • Production: MAXDOP 1 (single-threaded, uses 1 CPU core)
   • Staging: MAXDOP 2 (limited parallelism)
   • Development: MAXDOP 4 (allows parallelism)
   • Prevents: A single query from consuming all CPU cores

2. Memory Control (MAX_GRANT_PERCENT) - Limits memory allocation per query:

   • Production: MAX_GRANT_PERCENT = 10 (10% of available query memory)
   • Staging: MAX_GRANT_PERCENT = 25 (25% of available query memory)
   • Development: MAX_GRANT_PERCENT = 50 (50% of available query memory)
   • Prevents: A single query from consuming excessive SQL Server memory

3. Lock Prevention (NOLOCK) - Prevents read blocking in production:

   • Automatically adds WITH (NOLOCK) hints to all table references
   • Production only: Prevents queries from blocking write operations
   • Configurable: Can be enabled/disabled per environment

4. Query Execution Timeout - Enforces maximum query execution time:

   • Default: 60s (Int: 120s, Stg: 90s, Prd: 30s, Max: 300s)
   • Set via ODBC connection timeout (MCP sets the value)
   • SQL Server automatically cancels queries that exceed timeout
   • Prevents: Long-running queries from consuming resources indefinitely

How it works:

• Query hints (MAXDOP, MAX_GRANT_PERCENT, NOLOCK): The MCP automatically appends OPTION (MAXDOP n, MAX_GRANT_PERCENT = x) or WITH (NOLOCK) to queries. SQL Server enforces these limits during query execution.
• Query timeout: The MCP sets the timeout value via ODBC connection properties. SQL Server enforces it by automatically canceling queries that exceed the timeout.

MCP-Level Protections (Application-Level Limits)

These protections are enforced by the MCP application before and during query execution:

1. Concurrency Throttling - Limits concurrent queries per environment and user:

   • Max 5 concurrent queries per environment
   • Max 2 concurrent queries per user
   • Prevents: Resource exhaustion from too many simultaneous queries

2. AST Validation - Strict read-only enforcement using SQL parsing:

   • Blocks INSERT, UPDATE, DELETE, DDL operations
   • Blocks multi-statement batches
   • Prevents: Accidental or malicious write operations

3. Query Cost Checking - Blocks expensive queries before execution:

   • Analyzes SQL Server execution plan cost (estimates CPU, I/O, memory)
   • Default threshold: 50 (Int: 100, Stg: 50, Prd: 10)
   • Prevents: CPU-intensive queries from running

4. Result Set Size Limits - Prevents large result sets:

   • Payload Size Limit: Default 1MB per query result (configurable)
   • Row Limit: Default 1000 rows (Int: 10000, Stg: 5000, Prd: 500)
   • Batch Fetching: Uses fetchmany instead of fetchall to control memory
   • Automatic Truncation: Large text values (>1000 chars) are truncated
   • Prevents: Excessive memory consumption in the MCP application

5. Allowed Databases - Optional whitelist of databases that can be queried

   • Prevents: Access to unauthorized databases

Additional MCP Protections:

• Linked Server Blocking - Detects and blocks queries using OPENQUERY, OPENDATASOURCE, OPENROWSET, and four-part names
• Connection Pooling - Efficient connection management with configurable pool size
• Structured Logging - All operations logged in JSON format for audit and monitoring

Protection Summary

How to Use

Quick Start with Docker

1. Start the services:

   docker-compose up -d
   

2. Configure Cursor MCP:

   • Open Cursor Settings → Features → MCP
   • Add new MCP server:
     • Command: docker
     • Args: exec -i mcp-sql-server python server.py

3. Use the MCP tools:

   • Query data: "What tables exist in the database?"
   • Review SQL: "Review this query: SELECT * FROM Users"
   • Get schema: "Show me the database schema"

MCP Tools

query_readonly - Execute Safe Queries

query_readonly(
    query="SELECT id, username FROM dbo.Users WHERE is_active = 1",
    env="Int",  # Optional: Int, Stg, or Prd
    database="MyAppDB",  # Optional
    page_size=10,  # Optional: pagination
    page=1  # Optional: page number
)

review_sql_script - Analyze SQL Scripts

review_sql_script(
    script="SELECT * FROM Users WHERE YEAR(created_date) = 2024",
    env="Int"
)
# Returns: risk_score, findings, best_practice_warnings

schema_summary - Get Database Schema

schema_summary(
    env="Int",
    search_term="user"  # Optional: filter tables
)

explain - Get Execution Plan

explain(
    query="SELECT * FROM dbo.Users WHERE id = 1",
    env="Int"
)

get_best_practices - List All Rules

get_best_practices()
# Returns: Complete list of 50 best practice rules

config_info - Get Server Configuration

config_info()
# Returns: Current MCP server settings

Configuration

Environment Variables (Required):

DB_CONNECTION_STRING_INT="Driver={ODBC Driver 18...};Server=...;Database=...;Uid=...;Pwd=..."
DB_CONNECTION_STRING_STG="..."  # Optional
DB_CONNECTION_STRING_PRD="..."  # Optional

Config File: config/config.yaml - Defines limits, timeouts, and environment-specific settings.

Database User Permissions

The database user should have ONLY:

• db_datareader on target databases
• VIEW SERVER STATE for execution plan analysis

Never grant: db_datawriter, db_ddladmin, or sysadmin roles.

Requirements: Python 3.11+, Docker (for local testing), ODBC Driver 18 for SQL Server