# 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.

<div align="center">

![TypeScript](https://img.shields.io/badge/TypeScript-007ACC?logo=typescript&logoColor=white)
![Node.js](https://img.shields.io/badge/Node.js-339933?logo=node.js&logoColor=white)
![SQL Server](https://img.shields.io/badge/SQL%20Server-CC2927?logo=microsoftsqlserver&logoColor=white)
![MCP](https://img.shields.io/badge/MCP-Protocol-blue)

</div>

---

## 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

```bash
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:

```bash
cp .env.example .env
```

Edit `.env`:

```dotenv
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

```bash
# 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:

```json
{
  "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`:

```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):

```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"
      }
    }
  }
}
```

### Claude Code (CLI)

```bash
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

```bash
# 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