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.