Content
# 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">
</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
