# mcp-sqlserver

MCP server for Microsoft SQL Server with **explicit port support**. Works from Claude Code, Cursor, and CLI; default port is 9123 (override with `MSSQL_PORT`).

## Why this exists

Generic `mssql-mcp-server` packages can fail when run from Claude Code with a custom port: connection works from the CLI but not when the client spawns the process. This server reads config from environment variables and passes **port as a number** into the driver so behavior is consistent everywhere.

## Requirements

- Node.js 18+
- SQL Server reachable via TCP (default port 9123, or set `MSSQL_PORT`)
- For **Windows Integrated Auth** (current user, no password): `npm install msnodesqlv8` (Windows native driver)

## Setup

```bash
cd mcp-sqlserver
npm install
npm run build
```

## Configuration

### Option A: Connections map file (per environment / client)

Use a JSON file so each MCP server block only sets **environment** and **client** (and optionally the file path). One server (e.g. `192.168.100.65`) can host **multiple databases**; each is a **client** with its own database name and credentials. You run one MCP server block per database (same server, different `MSSQL_CLIENT`).

**File structure** (e.g. `connections.json`): `environment -> { server, port, encrypt?, trustServerCertificate?, windowsIntegrated?, clients: { clientName -> { database, user?, password?, domain?, windowsIntegrated? } } }`. Server settings are shared; each client has database and either SQL credentials (`user`/`password`), NTLM (`domain` + `user`/`password`), or Windows Integrated (`windowsIntegrated: true`, no user/password).

| Variable | Required | Description |
|----------|----------|-------------|
| `MSSQL_ENVIRONMENT` | When using file | Environment key (e.g. `staging`, `prod`) |
| `MSSQL_CLIENT` | When using file | Client key under `clients` (e.g. database name `QASandbox8`) |
| `MSSQL_CONFIG_PATH` | No | Path to the JSON file (default `connections.json`, resolved from current working directory) |

If **both** `MSSQL_ENVIRONMENT` and `MSSQL_CLIENT` are set, the server loads the file, uses the environment’s server/port/encrypt/trustServerCertificate, and the client’s database and auth. Any of `MSSQL_SERVER`, `MSSQL_PORT`, `MSSQL_DATABASE`, `MSSQL_USER`, `MSSQL_PASSWORD`, `MSSQL_DOMAIN`, `MSSQL_ENCRYPT`, `MSSQL_TRUST_CERT`, `MSSQL_WINDOWS_INTEGRATED` in `env` **override** the file values.

#### Windows authentication

- **NTLM (domain user)**  
  In the client entry set `domain` with `user` and `password` (e.g. `domain: "MYDOMAIN"` for `MYDOMAIN\myuser`). Uses the default driver (tedious). Env override: `MSSQL_DOMAIN`.

- **Windows Integrated (current OS user)**  
  In the client entry set `windowsIntegrated: true`; omit `user` and `password`. Uses the **msnodesqlv8** driver (Windows native). Install it with `npm install msnodesqlv8`. You can set `windowsIntegrated: true` at the environment level to apply to all clients, or per client. Env override: `MSSQL_WINDOWS_INTEGRATED=true`.

Example `connections.example.json` (copy to `connections.json` and fill in real values):

```json
{
  "staging": {
    "server": "192.168.100.65",
    "port": 9123,
    "encrypt": true,
    "trustServerCertificate": true,
    "clients": {
      "QASandbox8": {
        "database": "QASandbox8",
        "user": "usrQASandbox8",
        "password": "your-password"
      },
      "OtherDatabase": {
        "database": "OtherDatabase",
        "user": "usrOtherDb",
        "password": "your-password"
      },
      "NTLM_Database": {
        "database": "MyDb",
        "domain": "MYDOMAIN",
        "user": "myuser",
        "password": "my-password"
      },
      "WindowsIntegratedDb": {
        "database": "TrustedDb",
        "windowsIntegrated": true
      }
    }
  }
}
```

Example MCP blocks: one per database on the same server (same `staging` server, different `MSSQL_CLIENT`):

```json
"mssql-staging-qa": {
  "command": "node",
  "args": ["C:\\Code\\AI-Examples\\mcp\\mcp-sqlserver\\dist\\index.js"],
  "env": {
    "MSSQL_ENVIRONMENT": "staging",
    "MSSQL_CLIENT": "QASandbox8",
    "MSSQL_CONFIG_PATH": "C:\\Code\\AI-Examples\\mcp\\mcp-sqlserver\\connections.json"
  }
},
"mssql-staging-other": {
  "command": "node",
  "args": ["C:\\Code\\AI-Examples\\mcp\\mcp-sqlserver\\dist\\index.js"],
  "env": {
    "MSSQL_ENVIRONMENT": "staging",
    "MSSQL_CLIENT": "OtherDatabase",
    "MSSQL_CONFIG_PATH": "C:\\Code\\AI-Examples\\mcp\\mcp-sqlserver\\connections.json"
  }
}
```

### Option B: Environment variables only

| Variable | Required | Description |
|----------|----------|-------------|
| `MSSQL_SERVER` | Yes | Server host (e.g. `192.168.100.65`) |
| `MSSQL_PORT` | No | Port (default `9123`). |
| `MSSQL_DATABASE` | No | Database name (default `master`) |
| `MSSQL_USER` | Yes* | Login user (omit for Windows Integrated) |
| `MSSQL_PASSWORD` | Yes* | Login password (omit for Windows Integrated) |
| `MSSQL_DOMAIN` | No | NTLM domain (e.g. `MYDOMAIN` for domain\user) |
| `MSSQL_WINDOWS_INTEGRATED` | No | Set to `true` to use current Windows user (requires `msnodesqlv8`) |
| `MSSQL_ENCRYPT` | No | `true` or `false` (default `false`) |
| `MSSQL_TRUST_CERT` | No | Set to `true` for self-signed / dev (or use `MSSQL_TRUST_SERVER_CERTIFICATE`) |

\* Omit when using Windows Integrated auth (`MSSQL_WINDOWS_INTEGRATED=true`).

## Claude Code / Cursor

Add your MCP server block under `mcpServers` in Claude Code or Cursor (e.g. **Settings** → **MCP**). Use **one** of these:

- **With a connections file (Option A)** – Put `connections.json` next to the project (or set `MSSQL_CONFIG_PATH`). In the MCP config you only set environment, client, and path:

```json
"mssql-staging-qa": {
  "command": "node",
  "args": ["C:\\Code\\AI-Examples\\mcp\\mcp-sqlserver\\dist\\index.js"],
  "env": {
    "MSSQL_ENVIRONMENT": "staging",
    "MSSQL_CLIENT": "QASandbox8",
    "MSSQL_CONFIG_PATH": "C:\\Code\\AI-Examples\\mcp\\mcp-sqlserver\\connections.json"
  }
}
```

- **Without a file (Option B)** – Pass everything via env (server, port, database, user, password, etc.):

```json
"mssql": {
  "command": "node",
  "args": ["C:\\Code\\AI-Examples\\mcp\\mcp-sqlserver\\dist\\index.js"],
  "env": {
    "MSSQL_SERVER": "192.168.100.65",
    "MSSQL_PORT": "9123",
    "MSSQL_DATABASE": "QASandbox8",
    "MSSQL_USER": "usrQASandbox8",
    "MSSQL_PASSWORD": "your-password",
    "MSSQL_ENCRYPT": "true",
    "MSSQL_TRUST_CERT": "true"
  }
}
```

Replace `args[0]` with the **absolute path** to `dist/index.js` on your machine.

## Tools

- **query** – Run a read-only SQL query (e.g. `SELECT ...`). Returns results as a text table.
- **list_tables** – List tables in the current database. Optional `schema` argument (e.g. `dbo`).
- **describe_table** – Column names and types for a table. Arguments: `table`, optional `schema` (default `dbo`).

## Run locally (stdio)

```bash
# Set env vars, then:
npm run start
# or without building:
npm run dev
```

The server speaks MCP over stdio; a client (Claude Code, Cursor, or another MCP client) must start it and connect to stdin/stdout.

## License

MIT