sql-server-mcp

SQL Server MCP

An MCP (Model Context Protocol) server that connects AI assistants to Microsoft SQL Server databases. Enables AI tools like GitHub Copilot, Cursor, Cline, Claude Desktop, and Claude Code to explore database schemas and execute read-only queries safely.

---

Features

• 29 built-in tools for comprehensive database analysis and management
• Read-only by design — uses db_datareader role, blocks all destructive SQL
• SQL injection protection — AST-based query validation + keyword blocklist
• Automatic row limits — prevents memory overflow with smart TOP N injection
• AI-friendly errors — structured error responses with actionable suggestions
• Schema caching — 1-hour TTL metadata cache reduces redundant DB calls
• Unicode/Vietnamese support — full NVARCHAR UTF-8 passthrough
• Dual transport — stdio (default) for local AI tools, HTTP/SSE for web clients
• Data analysis — table statistics, index information, and column distribution
• Data export — export table data to JSON or CSV format
• Complete schema understanding — views, constraints, stored procedures, and server info
• Security auditing — user management and permission analysis
• Enhanced profiling — detailed column distribution with pattern recognition
• Dependency mapping — full dependency analysis for tables and columns
• Schema management — comprehensive schema organization and analysis
• Data quality validation — integrity checks and comprehensive profiling
• Documentation automation — generate schema documentation and ER diagrams

---

Available Tools

Database & Schema (3 tools)

Tool	Description
list_databases	List all user-accessible databases on the SQL Server instance
list_schemas	List all schemas in database with table and view counts
list_tables	List tables and views in a specific schema

Schema Exploration (5 tools)

Tool	Description
describe_table	Get column details: name, type, nullable, PK, description
get_table_relationships	Get foreign key mappings for a table (guides JOINs)
search_tables	Search tables/columns/descriptions by keyword
get_view_definition	Get SQL definition of a view along with referenced tables
list_constraints	List all constraints (PK, FK, Unique, Check) for a table

Data Analysis (6 tools)

Tool	Description
get_table_statistics	Get table statistics including row count, size, and timestamps
get_table_indexes	Get all indexes for a table with column details
analyze_table	Analyze table data to get distribution statistics for each column
get_column_distribution	Get detailed distribution statistics for a specific column with pattern recognition
list_stored_procedures	List all stored procedures and functions with parameters
get_procedure_definition	Get full SQL definition of a stored procedure or function

Dependencies & Usage (2 tools)

Tool	Description
get_table_dependencies	Get full dependency map for a table (what it depends on and what depends on it)
get_column_usage	Get detailed usage information for a specific column (views, procedures, foreign keys, indexes)

Data Quality (2 tools)

Tool	Description
validate_data_integrity	Validate data integrity for a table (FK violations, duplicates, null violations)
get_data_profile	Get comprehensive data profile for a table including quality metrics

Documentation (3 tools)

Tool	Description
generate_schema_documentation	Generate comprehensive schema documentation with tables, views, columns, and relationships
create_entity_relationship_diagram	Create entity relationship diagram in Mermaid, PlantUML, or DOT format
generate_api_documentation	Generate REST API documentation from database schema with inferred endpoints and schemas

Migration & Comparison (2 tools)

Tool	Description
create_migration_scripts	Generate migration scripts for schema changes with up/down migrations
compare_schemas	Compare two schemas to identify differences in tables, views, and procedures

Server & System (1 tool)

Tool	Description
get_server_info	Get SQL Server information including version, edition, and status

Security & Users (2 tools)

Tool	Description
list_users	List all database users and their roles
get_user_permissions	Get detailed permissions for a specific user

Data Export & Query (3 tools)

Tool	Description
execute_read_query	Execute a validated read-only SELECT query
export_table_data	Export table data to JSON or CSV format
clear_cache	Clear the metadata cache to force fresh data

---

Quick Start

1. Install

git clone https://github.com/az-coder-123/sql-server-mcp.git
cd sql-server-mcp
npm install
npm run build

2. Configure

Copy the environment template and fill in your SQL Server credentials:

cp .env.example .env

Edit .env:

DB_HOST=localhost
DB_PORT=1433
DB_NAME=MyDatabase
DB_USER=readonly_user
DB_PASSWORD=your_password
DB_ENCRYPT=true
DB_TRUST_SERVER_CERT=false

Important: The DB_USER should have the db_datareader role only. The server blocks all write operations at the code level, but defense-in-depth at the database level is strongly recommended.

3. Run

# stdio mode (default — for AI tools)
npm start

# Development mode (with hot reload)
npm run dev

# HTTP/SSE mode (for web clients)
MCP_TRANSPORT=http npm start

---

Integration with AI Tools

GitHub Copilot (VS Code)

Create .vscode/mcp.json in your project:

{
  "servers": {
    "sql-server-mcp": {
      "type": "stdio",
      "command": "node",
      "args": ["/path/to/sql-server-mcp/dist/index.js"],
      "env": {
        "DB_SERVER": "localhost",
        "DB_DATABASE": "MyDatabase",
        "DB_USER": "readonly_user",
        "DB_PASSWORD": "${input:dbPassword}"
      }
    }
  }
}

VS Code will securely prompt for the password at runtime via ${input:dbPassword}.

Cursor

Create .cursor/mcp.json:

{
  "mcpServers": {
    "sql-server-mcp": {
      "command": "node",
      "args": ["/path/to/sql-server-mcp/dist/index.js"],
      "env": {
        "DB_SERVER": "localhost",
        "DB_DATABASE": "MyDatabase",
        "DB_USER": "readonly_user",
        "DB_PASSWORD": "your_password"
      }
    }
  }
}

Cline (VS Code Extension)

1. Open Cline settings in VS Code
2. Go to MCP Servers → Add Server
3. Select Command (stdio)
4. Enter:
   • Command: node
   • Args: /path/to/sql-server-mcp/dist/index.js
   • Env: DB_SERVER=localhost, DB_DATABASE=MyDatabase, DB_USER=readonly_user, DB_PASSWORD=your_password

Claude Desktop

Add to ~/Library/Application Support/Claude/claude_desktop_config.json (macOS) or %AppData%\Claude\claude_desktop_config.json (Windows):

{
  "mcpServers": {
    "sql-server-mcp": {
      "command": "node",
      "args": ["/path/to/sql-server-mcp/dist/index.js"],
      "env": {
        "DB_SERVER": "localhost",
        "DB_DATABASE": "MyDatabase",
        "DB_USER": "readonly_user",
        "DB_PASSWORD": "your_password"
      }
    }
  }
}

Claude Code (CLI)

claude --mcp-config ./mcp-config.json "Show me all tables in database"

Where mcp-config.json uses the same mcpServers format as Claude Desktop above.

---

Configuration Reference

All settings are configured via environment variables (or .env file):

Variable	Default	Description
DB_HOST	localhost	SQL Server hostname or IP
DB_PORT	1433	SQL Server port
DB_NAME	master	Default database
DB_USER	—	SQL Server login username
DB_PASSWORD	—	SQL Server login password
DB_ENCRYPT	true	Enable TLS encryption
DB_TRUST_SERVER_CERT	false	Trust self-signed certificates
MCP_TRANSPORT	stdio	Transport mode: stdio or http
MCP_HTTP_PORT	3000	HTTP server port (when MCP_TRANSPORT=http)
QUERY_ROW_LIMIT	100	Max rows returned per query (auto-injected)
QUERY_TIMEOUT_MS	30000	Query timeout in milliseconds
PAYLOAD_MAX_BYTES	1048576	Max response payload size (1MB)
SCHEMA_CACHE_TTL_SECONDS	3600	How long schema metadata is cached

---

Security

This server is designed for read-only database access with multiple layers of protection:

1. Database-level: Uses a db_datareader-only account with readOnlyIntent connection flag
2. Query validation: AST-based parsing via node-sql-parser (with regex fallback for T-SQL edge cases)
3. Keyword blocklist: INSERT, UPDATE, DELETE, DROP, EXEC, TRUNCATE, ALTER, CREATE, GRANT, REVOKE, MERGE and more
4. Multi-statement blocking: Semicolons outside string literals are rejected
5. Row limiting: Automatic TOP N injection prevents memory exhaustion
6. Payload truncation: Responses exceeding 1MB are truncated with a warning
7. Connection timeout: 30-second hard timeout on all queries

---

Project Structure

sql-server-mcp/
├── src/
│   ├── index.ts                          # Server entry point, tool registration, transport
│   ├── config/database.ts                   # Connection pool, env vars
│   ├── tools/
│   │   ├── schemaTools.ts                 # list_databases, list_tables, describe_table, relationships, search
│   │   ├── schemaManagementTools.ts        # list_schemas
│   │   ├── queryTools.ts                  # execute_read_query
│   │   ├── tableStatisticsTools.ts          # get_table_statistics
│   │   ├── tableIndexTools.ts             # get_table_indexes
│   │   ├── tableAnalysisTools.ts          # analyze_table
│   │   ├── columnTools.ts                # get_column_distribution
│   │   ├── storedProcedureTools.ts          # list_stored_procedures
│   │   ├── exportTools.ts                 # export_table_data
│   │   ├── viewTools.ts                   # get_view_definition
│   │   ├── constraintTools.ts              # list_constraints
│   │   ├── serverInfoTools.ts             # get_server_info
│   │   ├── procedureDefinitionTools.ts     # get_procedure_definition
│   │   ├── userTools.ts                   # list_users, get_user_permissions
│   │   ├── dependencyTools.ts            # get_table_dependencies
│   │   ├── usageTools.ts                 # get_column_usage
│   │   ├── dataIntegrityTools.ts         # validate_data_integrity
│   │   ├── dataProfileTools.ts          # get_data_profile
│   │   ├── documentationTools.ts         # generate_schema_documentation
│   │   ├── apiDocumentationTools.ts     # generate_api_documentation
│   │   ├── migrationTools.ts            # create_migration_scripts
│   │   ├── schemaComparisonTools.ts     # compare_schemas
│   │   └── diagramTools.ts              # create_entity_relationship_diagram
│   ├── types/index.ts                     # Shared TypeScript interfaces
│   ├── utils/
│   │   ├── sqlValidator.ts                # AST + regex query validation, TOP injection
│   │   └── errorMapper.ts                 # SQL error code → AI-friendly messages
│   └── cache/schemaCache.ts              # In-memory TTL cache
├── tests/                                # Unit tests (vitest)
├── dist/                                 # Compiled output (npm run build)
├── .env.example                          # Environment variable template
└── package.json

---

Development

# Install dependencies
npm install

# Run in dev mode (hot reload)
npm run dev

# Run tests
npm test

# Build for production
npm run build

---

License

ISC