# sql-assistant-mcp

[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](https://opensource.org/licenses/MIT)
[![TypeScript](https://img.shields.io/badge/TypeScript-5-blue.svg)](https://www.typescriptlang.org/)
[![Node.js](https://img.shields.io/badge/Node.js-%3E%3D20-green.svg)](https://nodejs.org/)

A Model Context Protocol (MCP) server for **SQL Server / Azure SQL** — query, monitor, and
analyze databases directly from Claude. It is the data plane for the
[`sql-assistant-agent`](../sql-assistant-agent) lineage / reconciliation copilot.

> **Fork notice.** Derived from [`@fabriciofs/mcp-sql-server`](https://github.com/fabriciofs/mcp-sql-server)
> (MIT). The headline addition is **Microsoft Entra ID authentication**; all original tools are
> retained. See [LICENSE](LICENSE) for attribution.

## Features

- **Query Execution** — `SELECT`/`WITH` queries with parameterized inputs (read-only enforced)
- **Schema Exploration** — tables, columns, procedures, indexes
- **Database Monitoring** — active queries, blocking, wait stats, connections, sizes
- **Performance Analysis** — missing/unused/duplicate indexes, fragmentation, statistics
- **Microsoft Entra ID auth** — `az login`, service principal, managed identity, access token, or Entra password
- **Write Operations** — `INSERT`/`UPDATE`/`DELETE`, registered only when `READONLY=false`

## Installation

This server is run from a local build (not published to npm).

```bash
git clone <your-repo-url> sql-assistant-mcp
cd sql-assistant-mcp
npm install        # runs the build via the prepare script
npm run build      # (re)compile to build/
```

## Authentication modes

Set `SQL_AUTH_TYPE` (default `sql`). `SQL_SERVER` + `SQL_DATABASE` are always required.

| `SQL_AUTH_TYPE` | What it does | Required vars (besides server/database) |
|---|---|---|
| `sql` | Classic SQL login (original behavior) | `SQL_USER`, `SQL_PASSWORD` |
| `azure-default` | Entra ID via `DefaultAzureCredential` (az login / env / workload / managed identity) | — (optional `SQL_AZURE_CLIENT_ID` for a user-assigned identity) |
| `azure-service-principal` | Entra app registration | `SQL_AZURE_TENANT_ID`, `SQL_AZURE_CLIENT_ID`, `SQL_AZURE_CLIENT_SECRET` |
| `azure-password` | Entra username + password (non-MFA only) | `SQL_USER`, `SQL_PASSWORD`, `SQL_AZURE_CLIENT_ID`, `SQL_AZURE_TENANT_ID` |
| `azure-access-token` | Pre-fetched Entra access token | `SQL_ACCESS_TOKEN` |
| `azure-msi` | Azure Managed Identity | — (optional `SQL_AZURE_CLIENT_ID` for a user-assigned identity) |

The Entra modes acquire tokens through `@azure/identity`. Azure SQL requires encryption — keep
`SQL_ENCRYPT=true`. A `SQL_CONNECTION_URL` is shorthand for `sql` auth.

### Local-dev example (recommended): `az login`

```jsonc
{
  "mcpServers": {
    "dash2": {
      "command": "node",
      "args": ["/abs/path/to/sql-assistant-mcp/build/index.js"],
      "env": {
        "SQL_SERVER": "your-server.database.windows.net",
        "SQL_DATABASE": "Dash2",
        "SQL_AUTH_TYPE": "azure-default",
        "SQL_ENCRYPT": "true",
        "READONLY": "true"
      }
    }
  }
}
```

Run `az login` once (granting your Entra user access to the DB); the server picks up the
credential automatically.

### Service-principal example

```jsonc
{
  "env": {
    "SQL_SERVER": "your-server.database.windows.net",
    "SQL_DATABASE": "Dash2",
    "SQL_AUTH_TYPE": "azure-service-principal",
    "SQL_AZURE_TENANT_ID": "<tenant-guid>",
    "SQL_AZURE_CLIENT_ID": "<app-guid>",
    "SQL_AZURE_CLIENT_SECRET": "<secret>",
    "READONLY": "true"
  }
}
```

### SQL login (original behavior)

```jsonc
{
  "env": {
    "SQL_SERVER": "localhost",
    "SQL_DATABASE": "mydb",
    "SQL_USER": "sa",
    "SQL_PASSWORD": "yourpassword",
    "READONLY": "true"
  }
}
```

See [`.env.example`](.env.example) for the full variable reference.

### Other settings

| Variable | Default | Description |
|----------|---------|-------------|
| `READONLY` | **required** | `true` = read-only (SELECT/WITH); `false` also registers write tools |
| `SQL_PORT` | `1433` | Server port |
| `SQL_ENCRYPT` | `true` | Encrypt connection (required for Azure SQL) |
| `SQL_TRUST_CERT` | `false` | Trust self-signed certs (dev only) |
| `QUERY_TIMEOUT` | `30000` | Query timeout ms (max 120000) |
| `MAX_ROWS` | `1000` | Max rows returned (max 5000) |
| `POOL_MIN` / `POOL_MAX` | `2` / `10` | Connection pool bounds |
| `LOG_LEVEL` | `info` | `debug` \| `info` \| `warn` \| `error` |

## Available Tools

### Query
| Tool | Description |
|------|-------------|
| `sql_execute` | Execute SELECT/WITH queries with parameterized inputs |

### Schema
| Tool | Description |
|------|-------------|
| `schema_list_tables` | List tables and views |
| `schema_describe_table` | Columns, indexes, foreign keys for a table |
| `schema_list_columns` | Search columns across tables |
| `schema_list_procedures` | List stored procedures |
| `schema_list_indexes` | List indexes with usage stats |

### Monitor
| Tool | Description |
|------|-------------|
| `monitor_active_queries` | Currently running queries |
| `monitor_blocking` | Blocking sessions and lock chains |
| `monitor_wait_stats` | Wait statistics |
| `monitor_database_size` | Size and file usage |
| `monitor_connections` | Active connections |
| `monitor_performance_counters` | Performance counters |

### Analysis
| Tool | Description |
|------|-------------|
| `analyze_query` | Execution plan and statistics |
| `analyze_suggest_indexes` | Suggested missing indexes |
| `analyze_unused_indexes` | Unused indexes |
| `analyze_duplicate_indexes` | Duplicate/overlapping indexes |
| `analyze_fragmentation` | Index fragmentation |
| `analyze_statistics` | Stale statistics |

### Write (only when `READONLY=false`)
| Tool | Description |
|------|-------------|
| `sql_insert` | Insert a row |
| `sql_update` | Update rows (WHERE required) |
| `sql_delete` | Delete rows (WHERE required) |

## Security Considerations

- **Keep `READONLY=true`** unless writes are explicitly required — the read-only validator
  blocks anything that isn't a `SELECT`/`WITH`, plus stacked-query and `SELECT INTO` bypasses.
- Prefer **Entra ID auth** over SQL logins; avoid embedding secrets where you can use
  `azure-default` / managed identity.
- Grant the principal **minimal permissions** (read-only DB role for this copilot).

## Requirements

- Node.js >= 20
- SQL Server 2016+ or Azure SQL Database / Managed Instance

## Development

```bash
npm install        # install + build
npm run build      # compile
npm run dev        # watch compile
npm run typecheck  # tsc --noEmit
npm test           # vitest
npm run inspector  # MCP Inspector
```

## License

MIT — see [LICENSE](LICENSE). Original work © fabriciofs; modifications © Benrishty.