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

Protection Type	Level	What It Controls	How It Works
MAXDOP	Database	CPU cores per query	SQL Server query hint (OPTION (MAXDOP n))
MAX_GRANT_PERCENT	Database	Memory per query	SQL Server query hint (OPTION (MAX_GRANT_PERCENT = x))
NOLOCK	Database	Read locks	SQL Server table hint (WITH (NOLOCK))
Query Timeout	Database	Query execution time	ODBC timeout (MCP sets value, SQL Server enforces cancellation)
Concurrency Throttling	MCP	Concurrent queries	Application-level semaphore
Query Cost Check	MCP	Query complexity	Pre-execution plan analysis
Result Size Limits	MCP	Result set size	Application-level validation
AST Validation	MCP	Query type (read-only)	Pre-execution SQL parsing

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