mssql-mcp
by eamonboyle
README.md
# MSSQL MCP Server
[](https://opensource.org/licenses/MIT)
[](https://www.npmjs.com/package/@eamonboyle/mssql-mcp)
[](https://nodejs.org/)
[](https://x.com/eamonyo)
[](https://cursor.com/en/install-mcp?name=mssql-local&config=eyJjb21tYW5kIjoibnB4IiwiYXJncyI6WyIteSIsIkBlYW1vbmJveWxlL21zc3FsLW1jcCJdLCJlbnYiOnsiU0VSVkVSX05BTUUiOiJsb2NhbGhvc3QiLCJEQVRBQkFTRV9OQU1FIjoiQXBwREIiLCJEQVRBQkFTRVMiOiJBcHBEQixSZXBvcnRpbmdEQiIsIkRCX1VTRVIiOiJ5b3VyX3VzZXJuYW1lIiwiREJfUEFTU1dPUkQiOiJ5b3VyX3Bhc3N3b3JkIiwiVFJVU1RfU0VSVkVSX0NFUlRJRklDQVRFIjoidHJ1ZSIsIlJFQURPTkxZIjoiZmFsc2UiLCJFTkFCTEVfRERMIjoiZmFsc2UifX0=)
[](https://intradeus.github.io/http-protocol-redirector?r=vscode%3Amcp%2Finstall%3F%257B%2522name%2522%253A%2522mssql-local%2522%252C%2522command%2522%253A%2522npx%2522%252C%2522args%2522%253A%255B%2522-y%2522%252C%2522%2540eamonboyle%252Fmssql-mcp%2522%255D%252C%2522env%2522%253A%257B%2522SERVER_NAME%2522%253A%2522localhost%2522%252C%2522DATABASE_NAME%2522%253A%2522AppDB%2522%252C%2522DATABASES%2522%253A%2522AppDB%252CReportingDB%2522%252C%2522DB_USER%2522%253A%2522your_username%2522%252C%2522DB_PASSWORD%2522%253A%2522your_password%2522%252C%2522TRUST_SERVER_CERTIFICATE%2522%253A%2522true%2522%252C%2522READONLY%2522%253A%2522false%2522%252C%2522ENABLE_DDL%2522%253A%2522false%2522%257D%257D)
> Experimental use only. This server is intended for education and evaluation, not production. Use a dedicated least-privilege SQL login and test all operations.
## Overview
`@eamonboyle/mssql-mcp` exposes Microsoft SQL Server tools, resources, and prompts through the [Model Context Protocol](https://modelcontextprotocol.io/). The MCP client and its language model interpret natural-language requests and choose tools. This package validates requests, executes SQL Server operations, and returns structured results.
Key capabilities:
- Schema, object, relationship, dependency, and storage discovery
- Validated reads, parameterized searches, and estimated execution plans
- Insert, update, and delete tools with confirmation and row limits
- Preview tokens for update and delete operations
- DDL tools with an explicit configuration gate
- Multiple allowed databases on one SQL Server
- Local stdio and stateless Streamable HTTP transports
Supported clients include Cursor, VS Code, Claude Desktop, and other MCP-compatible hosts.
## Quick start
### Prerequisites
- Node.js 22 or newer (check with `node --version`)
- Microsoft SQL Server
- An MCP-compatible client
> **Still on Node.js 20?** Version 1.6.0 is the last release that runs on Node 20, which reached end-of-life in April 2026. Newer releases stop at startup with a message explaining this. Upgrade Node.js, or pin the older release in your MCP configuration with `npx -y @eamonboyle/mssql-mcp@1.6.0`.
The recommended installation runs the published package directly:
```bash
npx -y @eamonboyle/mssql-mcp
```
A global installation also exposes the `mssql-mcp` command:
```bash
npm install -g @eamonboyle/mssql-mcp
mssql-mcp
```
The one-click links use the minimal configuration shown below. Replace the sample connection values before use.
The badge payloads are generated from `src/samples/claude_desktop_config.json`. After changing the sample, run `npm run docs:update-install-links`; use `npm run docs:check-install-links` to verify they are current.
## Minimal MCP configuration
The standard presets show connection placeholders and keep DDL disabled:
```json
{
"mcpServers": {
"mssql-local": {
"command": "npx",
"args": ["-y", "@eamonboyle/mssql-mcp"],
"env": {
"SERVER_NAME": "localhost",
"DATABASE_NAME": "AppDB",
"DATABASES": "AppDB,ReportingDB",
"DB_USER": "your_username",
"DB_PASSWORD": "your_password",
"TRUST_SERVER_CERTIFICATE": "true",
"READONLY": "false",
"ENABLE_DDL": "false"
}
}
}
}
```
Cursor uses `mcpServers` in `~/.cursor/mcp.json` or `.cursor/mcp.json`. Claude Desktop uses the same shape in its configuration file.
Set `ENABLE_DDL=true` only when the assistant specifically needs to create or remove schema objects.
VS Code uses `.vscode/mcp.json` or its user MCP configuration with a top-level `servers` object:
```json
{
"servers": {
"mssql-local": {
"type": "stdio",
"command": "npx",
"args": ["-y", "@eamonboyle/mssql-mcp"],
"env": {
"SERVER_NAME": "localhost",
"DATABASE_NAME": "AppDB",
"DATABASES": "AppDB,ReportingDB",
"DB_USER": "your_username",
"DB_PASSWORD": "your_password",
"TRUST_SERVER_CERTIFICATE": "true",
"READONLY": "false",
"ENABLE_DDL": "false"
}
}
}
}
```
See [`src/samples/`](src/samples/) for copyable Claude Desktop and VS Code files. Do not commit configuration files containing real credentials.
## Required configuration
The server validates these variables before starting. Missing or blank values produce an actionable startup error.
| Variable | Accepted format | Purpose |
| -------------------------- | ---------------------- | --------------------------------------------------------------------- |
| `DB_USER` | Nonblank string | SQL login (or NTLM) username; not used for Entra ID auth |
| `DB_PASSWORD` | Nonblank string | SQL login (or NTLM) password; not used for Entra ID auth |
At least one database variable is required: set `DATABASE_NAME`, `DATABASES`, or both. With only `DATABASE_NAME`, that database is both the default and the allowlist. With only `DATABASES`, the first entry is the default. When both are set, `DATABASE_NAME` is used if it appears in `DATABASES`; otherwise the first allowed database is the runtime default.
### Authentication
`SQL_AUTH_TYPE` selects how the server signs in to SQL Server. The default, `sql`, uses `DB_USER` and `DB_PASSWORD`.
| `SQL_AUTH_TYPE` | Also requires | Notes |
| ------------------------- | ---------------------------------------------------------- | -------------------------------------------------------------------------------------------------- |
| `sql` (default) | `DB_USER`, `DB_PASSWORD` | SQL Server login |
| `ntlm` | `DB_USER`, `DB_PASSWORD`, `DB_DOMAIN` | Windows domain account over NTLM; integrated (SSPI) auth of the current user is not supported |
| `azure-default` | Optional `AZURE_CLIENT_ID` | Microsoft Entra ID via `DefaultAzureCredential`: `az login`, managed identity, workload identity, environment credentials |
| `azure-service-principal` | `AZURE_CLIENT_ID`, `AZURE_CLIENT_SECRET`, `AZURE_TENANT_ID` | Entra ID app registration with a client secret |
| `azure-access-token` | `AZURE_ACCESS_TOKEN` | A pre-acquired Entra ID token; it is not refreshed, so restart the server when it expires |
Entra ID types default `ENCRYPT` to `true` and `TRUST_SERVER_CERTIFICATE` to `false`, because Azure SQL requires TLS and presents publicly trusted certificates. For Azure SQL with `azure-default` after `az login`:
```text
SERVER_NAME=myserver.database.windows.net
DATABASE_NAME=mydb
SQL_AUTH_TYPE=azure-default
```
### Hostname and port
> Do not include the port in `SERVER_NAME`. Values such as `localhost,1434` are not supported by the Node.js `mssql` driver configuration used by this package. Set `SERVER_NAME` to the hostname and use `SERVER_PORT` separately.
```json
{
"SERVER_NAME": "localhost",
"SERVER_PORT": "1434"
}
```
For a Docker port mapping such as:
```yaml
ports:
- "1434:1433"
```
use the published host port:
```text
SERVER_NAME=localhost
SERVER_PORT=1434
```
The container still listens on `1433`, but the MCP process connects through host port `1434`.
## Advanced configuration
Optional variables do not need empty placeholders. In-code defaults apply when they are absent.
| Variable | Accepted format | Default | Purpose |
| ----------------------- | ----------------------------- | --------------------- | -------------------------------------------------------------- |
| `SERVER_NAME` | Hostname | `localhost` | SQL Server hostname only |
| `SERVER_PORT` | Integer from `1` to `65535` | Driver default `1433` | SQL Server TCP port; omitted from the driver config when unset |
| `SQL_AUTH_TYPE` | See [Authentication](#authentication) | `sql` | SQL Server authentication method |
| `ENCRYPT` | `"true"` or `"false"` | `"false"` (`"true"` for Entra ID) | Enable TLS encryption in the `mssql` driver |
| `TRUST_SERVER_CERTIFICATE` | `"true"` or `"false"` | `"true"` (`"false"` for Entra ID) | Trust the SQL Server certificate without validating its chain |
| `READONLY` | `"true"` or `"false"` | `"false"` | Remove write and DDL tools when enabled |
| `ENABLE_DDL` | `"true"` or `"false"` | `"false"` | Allow registered DDL tools to execute |
| `CONNECTION_TIMEOUT` | Positive integer seconds | `30` | SQL Server connection timeout |
| `QUERY_TIMEOUT_MS` | Positive integer milliseconds | `30000` | SQL request timeout |
| `MAX_ROWS` | Positive integer | `10000` | Maximum rows returned by read tools |
| `MAX_WRITE_ROWS` | Positive integer | `100` | Maximum rows one write operation may affect |
| `REQUIRE_WRITE_PREVIEW` | `"true"` or `"false"` | `"true"` | Require a matching preview token for updates and deletes |
| `MCP_TRANSPORT` | `stdio` or `http` | `stdio` | MCP transport mode |
| `MCP_HTTP_HOST` | Host or IP string | `127.0.0.1` | Bind address for HTTP mode |
| `MCP_HTTP_PORT` | Integer from `1` to `65535` | `3333` | Bind port for HTTP mode |
| `MCP_BASE_URL` | Absolute HTTP or HTTPS URL | Unset | Public HTTP base advertised by the server; `/mcp` is appended |
| `MCP_HTTP_AUTH_TOKEN` | Nonblank string | Unset | Require `Authorization: Bearer <token>` on HTTP requests |
| `MCP_HTTP_ALLOWED_HOSTS`| Comma-separated hostnames | Loopback + bind host + `MCP_BASE_URL` host | Hostnames accepted in `Host`/`Origin` headers |
| `MCP_HTTP_ALLOW_UNAUTHENTICATED` | `"true"` or `"false"` | `"false"` | Allow a non-loopback `MCP_HTTP_HOST` without a token |
Blank optional values use their documented defaults. Explicit nonblank invalid integers, booleans, ports, URLs, or transport names fail validation rather than falling back silently.
`ENCRYPT=false` preserves the existing unencrypted connection behavior. Set `ENCRYPT=true` for TLS. With encryption enabled, keep `TRUST_SERVER_CERTIFICATE=false` for certificates that chain to a trusted authority. Use `TRUST_SERVER_CERTIFICATE=true` only when explicitly accepting a self-signed or otherwise untrusted certificate, such as local development.
## Multi-database behavior
`DATABASES` is an allowlist. Every tool accepts an optional `databaseName`. When a tool omits it, the server uses `DATABASE_NAME` if that name is allowed, otherwise it uses the first entry in `DATABASES`. A requested database outside the allowlist is rejected.
## Safe writes and DDL
`READONLY=true` removes insert, update, delete, and DDL tools. Read-only preview tools remain available because they do not modify data.
When writes are enabled:
1. `insert_data`, `update_data`, `delete_data`, and DDL tools ask the user to confirm through MCP elicitation when the client supports it (Cursor, VS Code, Claude). Clients without elicitation must pass `confirmed: true`. Over HTTP, prompts work for 2026-07-28 clients; 2025-era HTTP clients must pass `confirmed: true` because the stateless transport cannot carry their elicitation round trip.
2. `update_data` and `delete_data` require nonempty structured `filters`, not raw SQL WHERE text.
3. With the default `REQUIRE_WRITE_PREVIEW=true`, call `preview_update` or `preview_delete` first and pass its `previewToken` to the matching write.
4. Preview tokens expire after 10 minutes, are single-use, and are bound to the same tool, table, filters, and update payload.
5. `MAX_WRITE_ROWS` rejects oversized operations, and update/delete execution applies a row cap.
Supported filter operators are `=`, `!=`, `>`, `>=`, `<`, `<=`, `LIKE`, `IN`, `IS NULL`, and `IS NOT NULL`.
DDL tools are registered when `READONLY=false`, but calls fail with `DDL_DISABLED` unless `ENABLE_DDL=true`.
For `insert_data`, pass the table in `tableName` and the schema separately in `schemaName`. Do not use a dotted `schema.table` value for `tableName`.
## Streamable HTTP
The default transport is stdio. To run the stateless HTTP transport from a directory containing a configured `.env`:
```bash
MCP_TRANSPORT=http npx -y @eamonboyle/mssql-mcp
```
The default endpoint is:
```text
http://127.0.0.1:3333/mcp
```
The HTTP endpoint serves both the 2026-07-28 MCP protocol and 2025-era clients. A client must accept `application/json, text/event-stream`. Each HTTP request creates a fresh MCP server instance; preview tokens, stored query results, and query plans use process-wide stores, so they stay valid across requests to the same process.
HTTP security:
- Only `/mcp` is served. `Host` and `Origin` headers are checked against `MCP_HTTP_ALLOWED_HOSTS` (by default loopback names, the bind host, and the `MCP_BASE_URL` host) to block DNS-rebinding attacks from web pages.
- Set `MCP_HTTP_AUTH_TOKEN` to require `Authorization: Bearer <token>`.
- The server speaks plain HTTP. Off loopback, put a TLS-terminating reverse proxy in front so the bearer token and query results are encrypted, and set `MCP_BASE_URL` to its `https://` address. Startup logs a warning when a non-loopback bind has no `https://` `MCP_BASE_URL`.
- Binding `MCP_HTTP_HOST` to anything other than loopback fails at startup unless `MCP_HTTP_AUTH_TOKEN` is set, or `MCP_HTTP_ALLOW_UNAUTHENTICATED=true` acknowledges that another layer (reverse proxy, network policy) protects the endpoint.
For a reverse proxy or externally published path, set `MCP_BASE_URL` to the public base without the final `/mcp` segment:
```text
MCP_BASE_URL=https://example.com/services/mssql
```
The server continues binding to `MCP_HTTP_HOST:MCP_HTTP_PORT`, logs `https://example.com/services/mssql/mcp` as its public endpoint, and exposes that URL through `mssql://config/server`. `MCP_E2E_BASE_URL` is a separate test-harness variable used only by `scripts/e2e-mcp-tools.mjs`.
Cursor HTTP configuration:
```json
{
"mcpServers": {
"mssql-http": {
"url": "http://127.0.0.1:3333/mcp",
"headers": { "Authorization": "Bearer ${env:MSSQL_MCP_TOKEN}" }
}
}
}
```
Omit `headers` when `MCP_HTTP_AUTH_TOKEN` is not set.
## Tools
| Tool | Mode | Purpose |
| ------------------------ | ----- | ---------------------------------------------------------------- |
| `list_databases` | Read | List configured databases |
| `list_table` | Read | List tables, optionally filtered by schema names in `parameters` |
| `describe_table` | Read | Describe a table schema |
| `list_objects` | Read | List tables, views, procedures, functions, and triggers |
| `describe_object` | Read | Return object metadata and definitions |
| `summarize_schema` | Read | Summarize object counts by type and schema |
| `list_largest_tables` | Read | Rank tables by storage and row count |
| `list_foreign_keys` | Read | List foreign keys |
| `describe_relationships` | Read | Describe foreign keys involving one table |
| `describe_dependencies` | Read | List objects that depend on an object |
| `analyze_table` | Read | Return row counts, storage, and index details |
| `read_data` | Read | Execute a validated SELECT (or CTE) query in a rolled-back transaction |
| `search_data` | Read | Search columns with parameterized LIKE predicates |
| `explain_query` | Read | Generate an estimated SELECT execution plan |
| `preview_update` | Read | Preview an update and issue a token when required |
| `preview_delete` | Read | Preview a delete and issue a token when required |
| `insert_data` | Write | Insert rows |
| `update_data` | Write | Update rows selected by structured filters |
| `delete_data` | Write | Delete rows selected by structured filters |
| `create_table` | DDL | Create a table |
| `create_index` | DDL | Create an index |
| `drop_table` | DDL | Drop a table |
## Resources and prompts
Clients with MCP resource support can discover:
- `mssql://config/server`
- `mssql://config/prompts`
- `mssql://database/{databaseName}/tables`
- `mssql://database/{databaseName}/objects`
- `mssql://database/{databaseName}/schema-summary`
- `mssql://database/{databaseName}/foreign-keys`
- `mssql://table/{databaseName}/{schemaName}/{tableName}`
- `mssql://object/{databaseName}/{schemaName}/{objectName}`
- `mssql://database/{databaseName}/object/{schemaName}/{objectName}/dependencies`
- `mssql://query-plan/{planId}`
- `mssql://query-result/{resultId}`
- `ui://mssql/query-results.html` (MCP Apps view)
Table and object listings are cached for 30 seconds. Query plan and large query result resources are temporary process-local artifacts.
### Query results grid (MCP Apps)
`read_data` and `search_data` declare an [MCP Apps](https://modelcontextprotocol.io/extensions/apps) view. Hosts that support MCP Apps (Claude, VS Code, Cursor 2.6+, and others) render results as a sortable, filterable grid inline in the conversation. Other clients ignore the view and use the normal text and structured result.
### read_data rules
`read_data` accepts one `SELECT` statement, optionally preceded by common table expressions (`WITH ...`). String literals, comments, and bracketed or quoted identifiers are ignored when checking for disallowed keywords, so `[Update]` columns or `'Update pending'` values are fine. Statements that modify data, execute code (`EXEC`, `sp_`/`xp_` procedures), declare variables, or read server identity (`@@` variables, `SUSER_SNAME()`) are rejected.
Every query also runs inside a transaction that is always rolled back, and results stream until `MAX_ROWS` rows have been read, at which point the query is cancelled and the result is marked `truncated`. SQL Server error messages (for example `Invalid column name`) are returned so the model can correct the query. Cancelling a tool call in the client cancels the running SQL request.
Available prompts:
- `explore_schema`
- `draft_safe_select`
- `review_write_operation`
## Development
For local source development only:
```bash
git clone https://github.com/eamonboyle/mssql-mcp.git
cd mssql-mcp
npm install
npm run build
node /path/to/mssql-mcp/dist/index.js
```
The repository includes a seeded SQL Server 2022 Docker environment:
```bash
cp .env.example .env
npm run db:up
npm run test:e2e # every tool over HTTP, then protocol checks (2026-07-28 + 2025 stdio)
```
See [`docs/dev-database.md`](docs/dev-database.md) for database and E2E details and [`CONTRIBUTING.md`](CONTRIBUTING.md) for build, lint, and test commands.
## Troubleshooting
- `SERVER_PORT must be a valid TCP port`: use a whole number from `1` to `65535`.
- `getaddrinfo ENOTFOUND localhost,1434`: move `1434` from `SERVER_NAME` to `SERVER_PORT`.
- `DDL_DISABLED`: set `ENABLE_DDL` to `"true"` only when DDL access is intended.
- `PREVIEW_TOKEN_INVALID`: create a new matching preview and use its token once within 10 minutes.
- stdio JSON parse errors: ensure scripts and dependencies write logs to stderr, not stdout.
- HTTP `403 Forbidden`: the request's `Host` or `Origin` is not in `MCP_HTTP_ALLOWED_HOSTS`; add the public hostname (or set `MCP_BASE_URL`).
- HTTP `401 Unauthorized`: send `Authorization: Bearer <MCP_HTTP_AUTH_TOKEN>`.
- database rejected: add it to `DATABASES` and use the exact allowed name in `databaseName`.
## Security
- Use a dedicated SQL login with the minimum permissions required.
- Set `READONLY=true` whenever writes are unnecessary.
- Keep `ENABLE_DDL=false` unless schema changes are explicitly needed.
- Keep credentials out of source control and use your client's secret-input support or a secrets manager.
- Keep HTTP mode on loopback, or set `MCP_HTTP_AUTH_TOKEN` and `MCP_HTTP_ALLOWED_HOSTS` when exposing it. Prefer TLS termination at a reverse proxy.
- `read_data` validation and its rolled-back transaction are defense in depth; a least-privilege login is still the primary control.
- Set `ENCRYPT=true` for TLS deployments and keep `TRUST_SERVER_CERTIFICATE=false` when the server certificate is publicly or privately trusted.
- Report vulnerabilities through the [security policy](.github/SECURITY.md).
## Project links
- [Changelog](CHANGELOG.md)
- [Contributing](CONTRIBUTING.md)
- [Code of Conduct](CODE_OF_CONDUCT.md)
- [Security Policy](.github/SECURITY.md)
- [License](LICENSE)
This server cannot be deployed
Maintenance
ActivityMaintained
ResponsivenessNo issues