Skip to content

az-coder-123/sql-server-mcp

BranchesTags

Repository files navigation

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.

TypeScript Node.js SQL Server MCP

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

About

Model Context Protocol (MCP) server for MS SQL Server. Enables LLMs and AI agents (Cursor, Copilot, Claude) to safely explore schemas and execute read-only queries with advanced SQL injection protection.

Topics

sql sql-server mcp data-tools ai-agent llm model-context-protocol mcp-server

Resources

Readme

License

MIT license
Activity

Stars

3 stars

Watchers

0 watching

Forks

0 forks
Report repository

Releases

No releases published

Packages

 
 
 

Contributors

Languages