# MCP MSSQL Secure

A Model Context Protocol server for Microsoft SQL Server and Azure SQL Database with **permission-based access modes**. Choose how much database power the AI gets at install time.

## Access modes

| Mode | `MSSQL_ACCESS_MODE` | Tools | SQL allowed |
|------|---------------------|-------|-------------|
| **Read-only** | `readonly` (default) | `query`, schema introspection | `SELECT`, `WITH`, `EXPLAIN`, `TABLE`, `VALUES` |
| **Read + DML** | `dml` | above + `execute` | DML: `INSERT`, `UPDATE`, `DELETE`, `MERGE` |
| **Full access** | `full` | above + `execute` (DDL) | DML + DDL: `CREATE`, `ALTER`, `DROP`, `TRUNCATE`, etc. |

Defense in depth:

- **Application-level** SQL classification (blocks multi-statement queries, `GO` batch separators, and disallowed statement types)
- **Connection lock** via `MSSQL_LOCK_CONNECTION` so credentials cannot be swapped at runtime when using env config
- **Database user permissions** as the final authority (SQL Server has no session-level read-only SET equivalent to PostgreSQL)

Pair each mode with a SQL Server login/user that has matching grants. The server enforces intent; the database user is the final authority.

## Installation

### From npm

```bash
npm install mcp-mssql-secure
```

Or run directly:

```bash
npx mcp-mssql-secure --access-mode readonly
```

### From source

```bash
git clone https://github.com/pugltd/mcp-mssql-secure.git
cd mcp-mssql-secure
npm install
npm run build
```

Point Cursor at `node /absolute/path/to/mcp-mssql-secure/build/index.js`.

## Configuration

All modes use the same connection environment variables. Set the access level with **`--access-mode`** (CLI) or **`MSSQL_ACCESS_MODE`** (env). The CLI flag wins if both are set.

| Variable / flag | Required | Default | Description |
|-----------------|----------|---------|-------------|
| `--access-mode` | no | `readonly` | `readonly`, `dml`, or `full` (overrides env) |
| `MSSQL_ACCESS_MODE` | no | `readonly` | Same as `--access-mode` |
| `MSSQL_HOST` | yes | — | Database host |
| `MSSQL_PORT` | no | `1433` | Database port |
| `MSSQL_USER` | yes | — | SQL authentication login |
| `MSSQL_PASSWORD` | yes | — | Password |
| `MSSQL_DATABASE` | yes | — | Database name |
| `MSSQL_ENCRYPT` | no | `true` | Enable TLS (recommended for Azure SQL) |
| `MSSQL_TRUST_SERVER_CERTIFICATE` | no | `false` | Trust self-signed certs (on-prem dev) |
| `MSSQL_LOCK_CONNECTION` | no | `true` when env config is set | Disables `connect_db` at runtime |

```bash
# CLI examples
npx mcp-mssql-secure --access-mode readonly
npx mcp-mssql-secure --access-mode=dml
node build/index.js --help
```

### Azure SQL vs on-prem

**Azure SQL Database** (defaults work out of the box):

```json
{
  "env": {
    "MSSQL_HOST": "your-server.database.windows.net",
    "MSSQL_PORT": "1433",
    "MSSQL_USER": "mcp_readonly",
    "MSSQL_PASSWORD": "your_password",
    "MSSQL_DATABASE": "your_database",
    "MSSQL_ENCRYPT": "true",
    "MSSQL_TRUST_SERVER_CERTIFICATE": "false"
  }
}
```

**On-prem with self-signed certificate** (dev/local):

```json
{
  "env": {
    "MSSQL_HOST": "localhost",
    "MSSQL_PORT": "1433",
    "MSSQL_USER": "mcp_readonly",
    "MSSQL_PASSWORD": "your_password",
    "MSSQL_DATABASE": "your_database",
    "MSSQL_ENCRYPT": "true",
    "MSSQL_TRUST_SERVER_CERTIFICATE": "true"
  }
}
```

### 1. Read-only (recommended default)

Use for exploring schemas and running analytics without write risk.

```json
{
  "mcpServers": {
    "mssql-readonly": {
      "type": "stdio",
      "command": "npx",
      "args": ["-y", "mcp-mssql-secure", "--access-mode", "readonly"],
      "env": {
        "MSSQL_HOST": "localhost",
        "MSSQL_PORT": "1433",
        "MSSQL_USER": "mcp_readonly",
        "MSSQL_PASSWORD": "your_password",
        "MSSQL_DATABASE": "your_database",
        "MSSQL_LOCK_CONNECTION": "true"
      }
    }
  }
}
```

### 2. Read + DML

Use when the AI may insert, update, or delete rows but must not change schema.

```json
{
  "mcpServers": {
    "mssql-dml": {
      "type": "stdio",
      "command": "npx",
      "args": ["-y", "mcp-mssql-secure", "--access-mode", "dml"],
      "env": {
        "MSSQL_HOST": "localhost",
        "MSSQL_PORT": "1433",
        "MSSQL_USER": "mcp_dml",
        "MSSQL_PASSWORD": "your_password",
        "MSSQL_DATABASE": "your_database",
        "MSSQL_LOCK_CONNECTION": "true"
      }
    }
  }
}
```

### 3. Full access (DDL)

Use only when schema changes are required. Prefer a dedicated low-privilege admin user, not `sysadmin`.

```json
{
  "mcpServers": {
    "mssql-full": {
      "type": "stdio",
      "command": "npx",
      "args": ["-y", "mcp-mssql-secure", "--access-mode", "full"],
      "env": {
        "MSSQL_HOST": "localhost",
        "MSSQL_PORT": "1433",
        "MSSQL_USER": "mcp_admin",
        "MSSQL_PASSWORD": "your_password",
        "MSSQL_DATABASE": "your_database",
        "MSSQL_LOCK_CONNECTION": "true"
      }
    }
  }
}
```

You can register multiple MCP entries (e.g. `mssql-readonly` and `mssql-dml`) and enable only the one you need per project.

## Available tools

### `query`

Read-only T-SQL. Supports `@p1`, `@p2` placeholders and MySQL-style `?` aliases.

```javascript
use_mcp_tool({
  server_name: "mssql-readonly",
  tool_name: "query",
  arguments: {
    sql: "SELECT * FROM users WHERE id = @p1",
    params: [1]
  }
});
```

### `execute` (dml and full modes only)

Mutating T-SQL. In `dml` mode: `INSERT`, `UPDATE`, `DELETE`, `MERGE` only. In `full` mode: DML and DDL.

```javascript
use_mcp_tool({
  server_name: "mssql-dml",
  tool_name: "execute",
  arguments: {
    sql: "UPDATE users SET active = @p1 WHERE id = @p2",
    params: [true, 1]
  }
});
```

Returns `{ "rowsAffected": [N] }`.

### `list_schemas`, `list_tables`, `describe_table`

Schema introspection (all modes). Default schema is **`dbo`**.

### `list_programmable_objects`, `describe_programmable_object`

Read-only introspection for stored procedures, functions, views, and triggers (all modes). Does **not** execute objects — `EXEC` remains blocked.

**`list_programmable_objects`** — discover objects in a schema:

| Param | Default | Values |
|-------|---------|--------|
| `schema` | `dbo` | Schema name |
| `object_type` | `all` | `procedure`, `function`, `view`, `trigger`, `all` |

Returns object names and types. Triggers include `parent_schema` and `parent_object`.

**`describe_programmable_object`** — full T-SQL definition and metadata:

| Param | Required | Default |
|-------|----------|---------|
| `name` | yes | — |
| `schema` | no | `dbo` |
| `object_type` | no | auto-detect |

Returns `definition`, `parameters` (procedures/functions), `parent_object` (triggers), and timestamps. If `definition` is null, a `definition_note` explains that the object may be encrypted or the user lacks **VIEW DEFINITION** permission.

```javascript
use_mcp_tool({
  server_name: "mssql-readonly",
  tool_name: "describe_programmable_object",
  arguments: {
    schema: "dbo",
    name: "usp_GetOrders",
    object_type: "procedure"
  }
});
```

### `connect_db`

Optional runtime connection when `MSSQL_LOCK_CONNECTION=false` and env vars are not set. Disabled by default when using env-based config.

## SQL Server role examples

**Read-only user:**

```sql
CREATE LOGIN mcp_readonly WITH PASSWORD = '...';
CREATE USER mcp_readonly FOR LOGIN mcp_readonly;
ALTER ROLE db_datareader ADD MEMBER mcp_readonly;
GRANT VIEW DEFINITION TO mcp_readonly;
-- or schema-scoped:
-- GRANT VIEW DEFINITION ON SCHEMA::dbo TO mcp_readonly;
```

`VIEW DEFINITION` is required to read stored procedure, function, view, and trigger source via `describe_programmable_object`. Without it, listing still works but definitions may be null.

**DML user** (add write role, no DDL):

```sql
CREATE LOGIN mcp_dml WITH PASSWORD = '...';
CREATE USER mcp_dml FOR LOGIN mcp_dml;
ALTER ROLE db_datareader ADD MEMBER mcp_dml;
ALTER ROLE db_datawriter ADD MEMBER mcp_dml;
```

**Admin user** (migrations / DDL): grant `db_ddladmin` or schema-scoped ALTER permissions. Avoid `sysadmin`.

```sql
CREATE LOGIN mcp_admin WITH PASSWORD = '...';
CREATE USER mcp_admin FOR LOGIN mcp_admin;
ALTER ROLE db_datareader ADD MEMBER mcp_admin;
ALTER ROLE db_datawriter ADD MEMBER mcp_admin;
ALTER ROLE db_ddladmin ADD MEMBER mcp_admin;
```

## Security

- Parameterized queries for user-supplied values
- Single-statement enforcement (no `;`-chained batches or `GO` separators)
- Statement-type validation per access mode
- Blocks `EXEC`, `DBCC`, `BACKUP`, `RESTORE`, `BULK`, `OPENROWSET`, and other dangerous T-SQL (use `describe_programmable_object` to read procedure/function definitions instead)
- Runtime `connect_db` disabled when connection is env-locked
- Credentials via environment variables (not chat arguments)

**Limitations:** validation is keyword-based, not a full T-SQL parser. Edge cases like `WITH ... INSERT` or `SELECT INTO` may be misclassified. Use least-privilege DB users and non-production databases when possible.

Unlike PostgreSQL, SQL Server has no equivalent of `SET default_transaction_read_only = on`. Read-only mode relies on application validation and database user permissions.

## Error handling

The server returns clear errors for:

- Invalid or disallowed SQL for the current access mode
- Multiple statements or `GO` batch separators in one request
- Connection failures
- Missing or mismatched parameters
- Disabled tools (`execute` in `readonly`, `connect_db` when locked)

## License

MIT

## Related

Sibling project: [mcp-postgres-secure](https://github.com/pugltd/mcp-postgres-secure) — same security model for PostgreSQL.