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

npm install mcp-mssql-secure

Or run directly:

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

From source

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 |

# 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):

{
  "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):

{
  "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.

{
  "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.

{
  "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.

{
  "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.

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.

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.

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:

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):

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.

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 — same security model for PostgreSQL.