# mcp-sqlserver

A powerful read-only [Model Context Protocol (MCP)](https://modelcontextprotocol.io) server for **Microsoft SQL Server**. Connects AI assistants (Claude, Gemini, Kiro, OpenAI, Copilot, Cursor) directly to your SQL Server databases with enterprise-grade security controls and zero data mutation risk.

**35 tools** across 6 categories: schema discovery, query execution, stored procedures, performance/DBA diagnostics, developer utilities, and server management.

> **Built on top of existing work:** This project is built upon [@tugberkgunver/mcp-sqlserver](https://github.com/gunvertugberk/mcp-sqlserver).
>
> **What makes this repository different:**
> - **Strict Read-Only Enforcement**: All state-modifying tools (`execute_mutation`, `execute_ddl`, `execute_procedure`, `rebuild_index`) have been removed from the server, guaranteeing zero risk of altering database tables, schemas, or data.
> - **Safe Production & Analytical Access**: Specifically designed for secure database inspection, schema discovery, DBA diagnostics, code generation, and reporting without requiring elevated write or admin execution privileges.
> - **Streamlined 35-Tool Suite**: Focused toolset optimized for read queries, performance DMVs, ER diagram generation, schema comparisons, and test data script generation.

[![npm version](https://img.shields.io/npm/v/@tugberkgunver/mcp-sqlserver)](https://www.npmjs.com/package/@tugberkgunver/mcp-sqlserver)
[![GitHub release](https://img.shields.io/github/v/release/gunvertugberk/mcp-sqlserver)](https://github.com/gunvertugberk/mcp-sqlserver/releases)

> **Changelog**: See [CHANGELOG.md](CHANGELOG.md) for version history or [GitHub Releases](https://github.com/gunvertugberk/mcp-sqlserver/releases) for detailed release notes.

## What's New in v1.3

- **Multi-server support** — Define dev/staging/prod servers in one config, switch with `server` parameter
- **`list_servers` tool** — See all configured connections at a glance
- **Per-server security** — Each server gets its own security mode, row limits, and blocked databases
- **Backward compatible** — Existing single-server configs work without any changes

## What's New in v1.2

- **Read-only tools expansion** — DBA diagnostics, code generation, ER diagrams, schema diff, data sampling, and export
- **SQL injection protection** — All queries now use parameterized inputs and escaped identifiers
- **ISO date formatting** — Dates display as `2025-01-27` instead of raw JavaScript Date strings
- **Streamable HTTP transport** — Host the MCP server remotely with `--http <port>`
- **Health check** — Verify connection status and server responsiveness

## Features

### Server Management (1 tool)
| Tool | Description / Purpose & Usage |
|------|-------------|
| `list_servers` | **Purpose:** List all configured SQL Server connection instances and metadata (host, port, default database, auth type, security mode, max rows).<br>**Usage:** Inspect available target environments (e.g., dev, staging, prod) before executing queries. |

> **Multi-server:** Every tool accepts an optional `server` parameter to target a specific named server. Omit it to use the default server.

### Schema Discovery (9 tools)
| Tool | Description / Purpose & Usage |
|------|-------------|
| `list_databases` | **Purpose:** Enumerate all accessible databases on the SQL Server instance.<br>**Usage:** Discover database targets available on the server instance. Filtered by security allow/block lists. |
| `list_schemas` | **Purpose:** List all schemas (e.g. `dbo`, `sales`, `staging`) in a database.<br>**Usage:** Explore object grouping structures in a target database before listing or querying tables. |
| `list_tables` | **Purpose:** List tables along with row counts and total storage space in MB.<br>**Usage:** Discover primary entities and volume sizes. Filter by schema to locate target tables. |
| `list_views` | **Purpose:** List all database views with creation and modification dates.<br>**Usage:** Discover virtual tables and pre-aggregated views without scanning raw underlying tables. |
| `describe_table` | **Purpose:** Retrieve full column schema (types, max length, precision, scale, nullability, identity, computed definitions, and default constraints).<br>**Usage:** Inspect table column definitions to construct accurate SELECT queries and inspect data types. |
| `get_foreign_keys` | **Purpose:** Retrieve foreign key relationships, referenced columns, and delete/update cascade rules.<br>**Usage:** Inspect parent/child table relationships and foreign key constraints to build correct JOIN queries. |
| `get_indexes` | **Purpose:** List index metadata (clustered/nonclustered, uniqueness, primary keys, key columns, and included columns).<br>**Usage:** Evaluate table indexing strategies and optimize WHERE/JOIN query execution paths. |
| `get_constraints` | **Purpose:** Retrieve PK, Unique, Check, Default, and Foreign Key constraints.<br>**Usage:** Audit data validation rules, key constraints, and default column values defined on tables. |
| `get_triggers` | **Purpose:** Retrieve trigger definitions (INSTEAD OF / AFTER, event types, status, and full T-SQL code).<br>**Usage:** Inspect automated database logic, audit tracking, and trigger side-effects safely. |

### Query Execution (2 tools)
| Tool | Description / Purpose & Usage |
|------|-------------|
| `execute_query` | **Purpose:** Safely execute read-only SQL queries (`SELECT` or `WITH` CTE statements).<br>**Usage:** Run ad-hoc SELECT queries with automatic row limits (`maxRowCount`), SQL injection protection, blocked keyword enforcement, and column-level PII data masking. |
| `export_query` | **Purpose:** Execute a SELECT query and format output results as structured CSV or JSON.<br>**Usage:** Export datasets for downstream reporting, analysis, or data pipeline consumption. |

> **Note:** All write/mutation operations (`INSERT`, `UPDATE`, `DELETE`, `MERGE`, DDL) have been removed. `execute_query` strictly enforces read-only `SELECT` / `WITH` statements.

### Stored Procedures (2 tools)
| Tool | Description / Purpose & Usage |
|------|-------------|
| `list_procedures` | **Purpose:** List stored procedures in a database with creation and modification timestamps.<br>**Usage:** Discover available stored procedures across database schemas. |
| `describe_procedure` | **Purpose:** Retrieve parameter metadata (types, length, precision, direction, defaults) and full T-SQL source code of a stored procedure.<br>**Usage:** Analyze stored procedure logic, parameters, and underlying query logic without risk of execution. |

> **Note:** Stored procedure execution (`execute_procedure`) has been removed to guarantee zero side-effects or state modifications.

### Performance & DBA (15 tools)
| Tool | Description / Purpose & Usage |
|------|-------------|
| `get_query_plan` | **Purpose:** Retrieve estimated execution plan XML/text for a SELECT query using `SET SHOWPLAN_TEXT ON` without executing the query.<br>**Usage:** Analyze query execution paths, identify missing index warnings, scan operators, and heavy joins. |
| `get_active_queries` | **Purpose:** Monitor currently running requests on the server from `sys.dm_exec_requests`.<br>**Usage:** View active session IDs, status, elapsed time, wait types, CPU/IO reads, blocking session IDs, and current SQL statement text. |
| `get_table_stats` | **Purpose:** Retrieve table storage statistics including row count, total/used/unused size in MB, and index fragmentation percentage.<br>**Usage:** Evaluate table growth, storage allocation, and index fragmentation levels. |
| `get_index_usage` | **Purpose:** Query `sys.dm_db_index_usage_stats` for index seek, scan, lookup, and update counts.<br>**Usage:** Identify unused or high-overhead indexes to optimize storage and indexing strategies. |
| `get_missing_indexes` | **Purpose:** Query `sys.dm_db_missing_index_details` for missing index suggestions with ready-to-use `CREATE INDEX` DDL.<br>**Usage:** Get automated index recommendations sorted by improvement impact score. |
| `get_server_info` | **Purpose:** Retrieve SQL Server instance diagnostics: version, edition, OS platform, CPU count, memory allocation, collation, and uptime.<br>**Usage:** Inspect server environment specs and uptime diagnostics. |
| `get_database_info` | **Purpose:** Retrieve detailed database configuration: data/log file size (MB), status, recovery model, compatibility level, and object counts.<br>**Usage:** Monitor database file allocation, recovery settings, and object counts. |
| `get_wait_stats` | **Purpose:** Retrieve top server wait statistics from `sys.dm_os_wait_stats` (excluding background system waits).<br>**Usage:** Identify server bottlenecks (CPU, disk I/O, locking, memory). |
| `get_deadlocks` | **Purpose:** Retrieve recent deadlock reports from the `system_health` Extended Events ring buffer.<br>**Usage:** Analyze deadlock timestamps and full XML deadlock graphs to diagnose concurrency conflicts. |
| `get_blocking_chains` | **Purpose:** Show current active session blocking hierarchies.<br>**Usage:** Identify blocked session IDs, blocking lead session IDs, wait times, blocked query text, and lead blocker query text. |
| `get_long_transactions` | **Purpose:** Show open transactions running longer than 5 seconds from `sys.dm_tran_active_transactions`.<br>**Usage:** Detect uncommitted transactions holding locks or preventing log truncation. |
| `get_space_usage` | **Purpose:** Summarize top tables in a database by disk space usage (data MB, index MB, unused MB).<br>**Usage:** Identify largest tables and manage storage capacity. |
| `get_backup_history` | **Purpose:** Retrieve recent database backup history from `msdb` (Full, Differential, Log).<br>**Usage:** Inspect backup finish dates, duration, size, compression ratio, and device paths for compliance audits. |
| `get_query_store_stats` | **Purpose:** Query Query Store DMVs (SQL Server 2016+) for top resource-consuming queries.<br>**Usage:** Analyze historical query performance over the past 7 days ranked by CPU, duration, reads, writes, or executions. |
| `health_check` | **Purpose:** Perform a fast connection health check.<br>**Usage:** Returns status (OK/FAILED), latency in ms, active user session count, server time, batch requests/sec, and product version. |

> **Note:** Index rebuild (`rebuild_index`) has been removed as it is a mutating DBA operation.

### Developer Utilities (6 tools)

#### `compare_schemas` — Schema Diff
Compare two databases side-by-side. Shows tables, columns, and type differences — perfect for dev vs prod comparison.
- **Purpose**: Detect structural differences between two databases without modifying either database.
- **Usage**:
```
compare_schemas(source_database: "DevDB", target_database: "ProdDB", schema: "dbo")
```
- **Output**: Tables only in source/target, columns only in source/target, and column type/nullability differences.

#### `generate_code` — Code Generation
Generate typed code models or standard DDL from any table's schema:
- **Purpose**: Instantly convert database table schemas into strongly typed application code models or DDL scripts.
- **Usage**:
```
generate_code(table: "Products", language: "typescript")
generate_code(table: "Orders", language: "csharp")
generate_code(table: "Customers", language: "sql")
```
- **Supported Languages**:
  - **TypeScript** — interfaces with proper types (`number`, `string`, `Date`, `Buffer | null`)
  - **C#** — classes with nullable value types (`int?`, `DateTime?`, `decimal?`)
  - **SQL** — `CREATE TABLE` scripts with full column definitions

#### `generate_insert_scripts` — Data Export as INSERT
Generate INSERT statements from existing table data — useful for migration scripts, seed data, or backing up small reference tables.
- **Purpose**: Export existing table rows as executable SQL `INSERT` statements with parameterized value formatting.
- **Usage**:
```
generate_insert_scripts(table: "Categories", top: 10)
```
- **Output**: Formatted T-SQL script containing `INSERT INTO [schema].[table] (...) VALUES (...);` statements.

#### `generate_er_diagram` — ER Diagram
Generate a [Mermaid](https://mermaid.js.org/) ER diagram from foreign key relationships. Paste the output into any Mermaid-compatible renderer (GitHub, Notion, VS Code, etc.).
- **Purpose**: Visualize entity-relationship models and database structural diagrams directly from foreign key definitions.
- **Usage**:
```
generate_er_diagram(database: "Northwind", schema: "dbo")
```
- **Output**:
```
generate_er_diagram(database: "Northwind")
→ erDiagram
    Products }o--|| Categories : "CategoryID"
    Products }o--|| Suppliers : "SupplierID"
    Orders }o--|| Customers : "CustomerID"
    ...
```

#### `generate_test_data` — Test Data Generation
Generate realistic INSERT statements with fake data based on column names and types. Smart heuristics for common patterns (email, phone, name, city, price, etc.).
- **Purpose**: Create realistic T-SQL `INSERT` test data scripts for local development and mock testing.
- **Usage**:
```
generate_test_data(table: "Customers", count: 5)
```
- **Output**: `INSERT INTO [dbo].[Customers] (...) VALUES (N'Alice', N'user1@example.com', N'New York', ...);`

#### `sample_table` — Random Sampling
Get a random sample of rows from any table using `NEWID()` — useful for AI assistants to understand data patterns without scanning entire tables.
- **Purpose**: Fetch a representative random sample of table rows to understand data formats and values without table scans.
- **Usage**:
```
sample_table(table: "Orders", count: 5)
```

## Security

### Read-Only Architecture

This MCP server is built strictly for **read-only database access**. All state-modifying tools (`execute_mutation`, `execute_ddl`, `execute_procedure`, `rebuild_index`) have been removed from the server. AI assistants can safely discover schemas, query data, run DBA diagnostics, and generate code with zero risk of mutating database data or structures.

### SQL Injection Protection
All user-provided values are passed as **parameterized query inputs** (`@param`). Object identifiers (database, schema, table names) are escaped using SQL Server bracket notation (`[name]` with `]` → `]]`).

### Additional Security Features
- Database and schema **allow/block lists** (`allowedDatabases`, `blockedDatabases`, `allowedSchemas`, `blockedSchemas`)
- Automatic **row count limits** (configurable `maxRowCount`, default `1000`)
- **Blocked keyword** detection (`xp_cmdshell`, `SHUTDOWN`, `DROP DATABASE`, `RECONFIGURE`, `sp_configure`)
- Column-level **data masking** for PII protection
- Strict `SELECT`/`WITH` query type validation

### Data Masking

Mask sensitive columns in query results:

```yaml
security:
  maskColumns:
    - pattern: "*.password"
      mask: "***"
    - pattern: "*.ssn"
      mask: "XXX-XX-XXXX"
    - pattern: "dbo.users.email"
      mask: "***@***.***"
```

Pattern format: `[schema.]table.column` (use `*` as wildcard)

## Authentication

| Method | Config `type` | Requirements |
|--------|---------------|-------------|
| SQL Server | `sql` | `user` + `password` |
| Windows (NTLM) | `windows` | `user` + `password` + optional `domain` |
| Windows (SSPI) | `windows` | No credentials needed; requires [`msnodesqlv8`](https://www.npmjs.com/package/msnodesqlv8) |
| Azure AD | `azure-ad` | `clientId` + `clientSecret` + `tenantId` |

### Windows Authentication

**NTLM** — Works out of the box, no extra packages:
```yaml
connection:
  host: YOUR_SERVER\SQLEXPRESS
  authentication:
    type: windows
    user: YourUsername
    password: YourPassword
    domain: YOUR_DOMAIN
  trustServerCertificate: true
```

**SSPI / Integrated Security** — Uses current Windows login session:
```bash
npm install msnodesqlv8
```
```yaml
connection:
  host: YOUR_SERVER\SQLEXPRESS
  authentication:
    type: windows
  trustServerCertificate: true
```

> **Note:** When using `npx`, optional dependencies like `msnodesqlv8` may not be installed automatically. For SSPI, consider installing globally (`npm install -g @tugberkgunver/mcp-sqlserver msnodesqlv8`) or use NTLM mode instead.

## Transport

### stdio (Default)
Standard input/output transport — used by MCP clients like Claude Desktop, VS Code, Cursor, etc.

### Streamable HTTP
For remote hosting or web integrations:
```bash
mcp-sqlserver --config mssql-mcp.yaml --http 3000
```

This starts:
- **MCP endpoint**: `http://localhost:3000/mcp`
- **Health check**: `http://localhost:3000/health` → `{"status":"ok","mode":"readonly"}`

Includes CORS support for browser-based clients.

## Quick Start

### Install

```bash
npm install -g @tugberkgunver/mcp-sqlserver
```

### Configure

Create `mssql-mcp.yaml` in your working directory:

```yaml
connection:
  host: localhost
  port: 1433
  database: MyDatabase
  authentication:
    type: sql
    user: sa
    password: YourPassword123
  trustServerCertificate: true

security:
  mode: readonly
  maxRowCount: 1000
  blockedDatabases:
    - master
    - msdb
    - tempdb
    - model
```

See [config.example.yaml](config.example.yaml) for all options.

### Multi-Server Configuration

Define multiple named servers to manage dev/staging/prod from a single config:

```yaml
defaultServer: dev

connections:
  dev:
    host: dev-server.example.com
    database: MyDatabase
    authentication:
      type: sql
      user: sa
      password: DevPass123
    trustServerCertificate: true
    security:
      mode: readonly
      maxRowCount: 5000

  prod:
    host: prod-server.example.com
    database: MyDatabase
    authentication:
      type: sql
      user: readonly_user
      password: ProdReadOnly
    security:
      mode: readonly
      blockedDatabases: [master, msdb, tempdb, model]

# Global security defaults (applied to all servers unless overridden)
security:
  maxRowCount: 1000
  blockedKeywords: [xp_cmdshell, SHUTDOWN, DROP DATABASE]
```

Then use the `server` parameter in any tool call:
```
list_tables(server: "prod", database: "MyDatabase")
health_check(server: "dev")
compare_schemas(source_database: "DevDB", target_database: "StagingDB", server: "dev")
```

### MCP Client Configuration

<details>
<summary><strong>Claude Desktop / Claude Code</strong></summary>

```json
{
  "mcpServers": {
    "mssql": {
      "command": "npx",
      "args": ["-y", "@tugberkgunver/mcp-sqlserver"],
      "env": {
        "MSSQL_HOST": "localhost",
        "MSSQL_DATABASE": "MyDatabase",
        "MSSQL_USER": "sa",
        "MSSQL_PASSWORD": "YourPassword123"
      }
    }
  }
}
```

With a config file:
```json
{
  "mcpServers": {
    "mssql": {
      "command": "npx",
      "args": ["-y", "@tugberkgunver/mcp-sqlserver", "--config", "/path/to/mssql-mcp.yaml"]
    }
  }
}
```
</details>

<details>
<summary><strong>VS Code (Copilot / Continue)</strong></summary>

Add to `.vscode/mcp.json`:
```json
{
  "servers": {
    "mssql": {
      "command": "npx",
      "args": ["-y", "@tugberkgunver/mcp-sqlserver"],
      "env": {
        "MSSQL_HOST": "localhost",
        "MSSQL_DATABASE": "MyDatabase",
        "MSSQL_USER": "sa",
        "MSSQL_PASSWORD": "YourPassword123"
      }
    }
  }
}
```
</details>

<details>
<summary><strong>Cursor</strong></summary>

Add to `~/.cursor/mcp.json`:
```json
{
  "mcpServers": {
    "mssql": {
      "command": "npx",
      "args": ["-y", "@tugberkgunver/mcp-sqlserver"],
      "env": {
        "MSSQL_HOST": "localhost",
        "MSSQL_DATABASE": "MyDatabase",
        "MSSQL_USER": "sa",
        "MSSQL_PASSWORD": "YourPassword123"
      }
    }
  }
}
```
</details>

<details>
<summary><strong>Kiro</strong></summary>

Add to `.kiro/settings/mcp.json`:
```json
{
  "mcpServers": {
    "mssql": {
      "command": "npx",
      "args": ["-y", "@tugberkgunver/mcp-sqlserver"],
      "env": {
        "MSSQL_HOST": "localhost",
        "MSSQL_DATABASE": "MyDatabase",
        "MSSQL_USER": "sa",
        "MSSQL_PASSWORD": "YourPassword123"
      }
    }
  }
}
```
</details>

<details>
<summary><strong>Gemini CLI</strong></summary>

Add to `~/.gemini/settings.json`:
```json
{
  "mcpServers": {
    "mssql": {
      "command": "npx",
      "args": ["-y", "@tugberkgunver/mcp-sqlserver"],
      "env": {
        "MSSQL_HOST": "localhost",
        "MSSQL_DATABASE": "MyDatabase",
        "MSSQL_USER": "sa",
        "MSSQL_PASSWORD": "YourPassword123"
      }
    }
  }
}
```
</details>

<details>
<summary><strong>OpenAI (ChatGPT Desktop)</strong></summary>

```json
{
  "mcpServers": {
    "mssql": {
      "command": "npx",
      "args": ["-y", "@tugberkgunver/mcp-sqlserver"],
      "env": {
        "MSSQL_HOST": "localhost",
        "MSSQL_DATABASE": "MyDatabase",
        "MSSQL_USER": "sa",
        "MSSQL_PASSWORD": "YourPassword123"
      }
    }
  }
}
```
</details>

<details>
<summary><strong>Windsurf</strong></summary>

Add to `~/.windsurf/mcp.json`:
```json
{
  "mcpServers": {
    "mssql": {
      "command": "npx",
      "args": ["-y", "@tugberkgunver/mcp-sqlserver"],
      "env": {
        "MSSQL_HOST": "localhost",
        "MSSQL_DATABASE": "MyDatabase",
        "MSSQL_USER": "sa",
        "MSSQL_PASSWORD": "YourPassword123"
      }
    }
  }
}
```
</details>

<details>
<summary><strong>Windows (all clients)</strong></summary>

On Windows, use `cmd` as the command wrapper:
```json
{
  "mcpServers": {
    "mssql": {
      "command": "cmd",
      "args": ["/c", "npx", "-y", "@tugberkgunver/mcp-sqlserver", "--config", "path/to/config.yaml"]
    }
  }
}
```
</details>

## Environment Variables

| Variable | Description |
|----------|-------------|
| `MSSQL_HOST` | SQL Server hostname |
| `MSSQL_PORT` | SQL Server port (default: 1433) |
| `MSSQL_DATABASE` | Default database |
| `MSSQL_USER` | SQL auth username |
| `MSSQL_PASSWORD` | SQL auth password |
| `MSSQL_MCP_CONFIG` | Path to YAML config file |

Environment variables override config file values.

## Development

```bash
git clone https://github.com/gunvertugberk/mcp-sqlserver.git
cd mcp-sqlserver
npm install
npm run build
npm start -- --config ./mssql-mcp.yaml
```

## License

MIT