# mssql-mcp

A read-only Microsoft SQL Server MCP server using connection
details from **AWS Secrets Manager** at tool-call time — nothing
is read from disk or environment variables except the secret reference
itself.

This is a light-weight mcp meant for local development use. It avoids database credentials in your agent config, works nicely with a credential process and tools like aws-vault, and allows for readonly database access and schema discovery.

Cross-platform: it's a pure-JavaScript MCP server (no native or ODBC
dependencies), so it runs identically on macOS, Linux, and Windows via
`npx`.

## Configure in `.claude.json`

```json
{
  "mcpServers": {
    "my-db": {
      "command": "npx",
      "args": ["-y", "@dhipskind253/mssql-mcp"],
      "env": {
        "AWS_ACCOUNT_ID": "123456789012",
        "SECRET_NAME": "my-aws-secret",
        "AWS_REGION": "us-east-1",
        "TRUST_SERVER_CERTIFICATE": "false"
      }
    }
  }
}
```

| Env var                     | Required | Default | Description                                                                         |
| --------------------------- | -------- | ------- | ----------------------------------------------------------------------------------- |
| `AWS_ACCOUNT_ID`            | yes      | —       | The AWS account where the secret lives. Combined with name + region into a full ARN. |
| `SECRET_NAME`               | yes      | —       | The Secrets Manager secret name (no ARN suffix needed).                             |
| `AWS_REGION`                | yes      | —       | AWS region the secret is in (e.g. `us-east-1`). Also picked up by the AWS SDK as its default region. |
| `TRUST_SERVER_CERTIFICATE`  | no       | `false` | Skip TLS cert validation to the SQL Server. Accepts `true`/`false`/`1`/`0`/`yes`/`no`. Set `true` only if you understand why. |

Standard AWS SDK env vars (`AWS_PROFILE`, `AWS_ACCESS_KEY_ID`, etc.) are
honored via the default credential provider chain. Most users just need
`aws sso login` to be current.

## Required secret JSON

The secret value must be a JSON document with at least these fields:

```json
{
  "host": "myserver.database.windows.net",
  "port": 1433,
  "database": "mydb",
  "username": "ro_user",
  "password": "..."
}
```

`database` may also be supplied as **`dbname`** — the field name AWS uses
in its built-in RDS-credentials secret template. If both are present,
`database` wins.

Optional fields (with defaults shown):

| Field     | Default | Notes              |
| --------- | ------- | ------------------ |
| `port`    | `1433`  |                    |
| `encrypt` | `true`  | TLS to the server. |

> TLS cert trust is **not** read from the secret — set
> `TRUST_SERVER_CERTIFICATE` in the MCP server's `env` block instead.
> Any `trustServerCertificate` field in the secret JSON is ignored.

## Read-only by design

This server cannot insert, update, or delete data. Two layers enforce that:

1. The `run_select` tool lexically rejects anything that isn't a single
   `SELECT` or `WITH` (CTE) statement — including `INSERT`, `UPDATE`,
   `DELETE`, `EXEC`, `MERGE`, `DROP`, `ALTER`, `SELECT INTO`, etc.
2. No other tool emits write SQL. `get_procedure_definition` returns
   procedure source — it does not run procedures.

> **Courtesy note:** treat the lexical check as UX, not a security
> boundary. As a courtesy to your future self, configure the credentials
> you put in Secrets Manager to be a **read-only database login** — one
> with `SELECT` and `VIEW DEFINITION` only. That way an accidental write
> (or a future bug here) is rejected by SQL Server itself.

## Refreshing AWS credentials without restarting

Because the server uses the default AWS credential chain, an expired SSO
session can be recovered without restarting Claude or the MCP server:

1. Run `aws sso login` in any terminal.
2. Ask Claude to call the **`refresh_secret`** tool.
3. Continue working.

If a tool call fails because of AWS auth, the error message will tell
you exactly that and prompt the same flow. Errors are tagged with stable
prefixes:

| Prefix                    | Meaning                                            |
| ------------------------- | -------------------------------------------------- |
| `[AWS_AUTH_REQUIRED]`     | SSO session expired or no credentials available.   |
| `[AWS_ACCESS_DENIED]`     | Principal lacks `secretsmanager:GetSecretValue`.   |
| `[AWS_SECRET_NOT_FOUND]`  | Secret name / account / region mismatch.           |
| `[AWS_SECRET_INVALID]`    | Secret JSON is missing fields or malformed.        |
| `[DB_CONNECT_FAILED]`     | Could not reach the SQL Server instance.           |
| `[DB_QUERY_FAILED]`       | SQL Server returned an error executing the query.  |
| `[INVALID_QUERY]`         | The submitted query violated the read-only rules.  |

## Tools

| Tool                       | Purpose                                                  |
| -------------------------- | -------------------------------------------------------- |
| `list_schemas`             | User schemas in the database.                            |
| `list_tables`              | Tables, optionally filtered by schema.                   |
| `describe_table`           | Columns, types, nullability, identity, PK, defaults.     |
| `list_indexes`             | Indexes on a table (one row per index/column).           |
| `list_foreign_keys`        | Outgoing FKs from a table.                               |
| `list_views`               | Views, optionally filtered by schema.                    |
| `get_view_definition`      | View source SQL.                                         |
| `list_procedures`          | Stored procedures, optionally filtered by schema.        |
| `get_procedure_definition` | Procedure source SQL (does not execute).                 |
| `sample_rows`              | `SELECT TOP n * FROM schema.table` (default 10, max 100). |
| `run_select`               | Single SELECT/CTE, capped at `max_rows` (default 100, hard max 1000). |
| `refresh_secret`           | Re-fetch the secret and reconnect.                       |

## Local development

```bash
npm install
npm run build
# point your .claude.json command at the local build:
#   "command": "node",
#   "args": ["/absolute/path/to/mssql-mcp/dist/index.js"]
```