mssql-mcp-server

A Model Context Protocol server for Microsoft SQL Server. Requires SQL Server 2017 or later (on-prem) or Azure SQL Database — several catalog queries (e.g. index/constraint/permission listings) use STRING_AGG, which does not exist before SQL Server 2017. Its tool surface — the 18 tool names, their operation values, parameters and output shapes — mirrors @henkey/postgres-mcp-server v1.0.7, the PostgreSQL MCP server many Claude/AI-client users already run, so a client that knows how to drive the Postgres server can drive this one the same way. henkey is AGPL-3.0; parity here is with the interface and behaviour, not the code — everything in this repository is written from scratch and licensed MIT (see LICENSE).

Quick start

npm install
npm run build

This produces build/index.js, a stdio MCP server. Point an MCP client at it.

Claude Code (.mcp.json or claude mcp add):

{
  "mssql-local": {
    "type": "stdio",
    "command": "node",
    "args": [
      "/path/to/mssql-mcp-server/build/index.js",
      "--connection-string", "Server=localhost,1433;Database=MyDb;User Id=app;Password=***;Encrypt=true;TrustServerCertificate=true",
      "--security-mode", "unsafe", "--allow-destructive"
    ]
  }
}

VS Code (mcp.json):

{
  "servers": {
    "mssql-local": {
      "type": "stdio",
      "command": "node",
      "args": [
        "/path/to/mssql-mcp-server/build/index.js",
        "--connection-string", "Server=localhost,1433;Database=MyDb;User Id=app;Password=***;Encrypt=true;TrustServerCertificate=true",
        "--security-mode", "unsafe", "--allow-destructive"
      ]
    }
  }
}

Azure SQL Database, read-only — the default --security-mode is already readonly, so it does not need to be passed explicitly; shown here for clarity:

{
  "mssql-azure": {
    "type": "stdio",
    "command": "node",
    "args": [
      "/path/to/mssql-mcp-server/build/index.js",
      "--connection-string", "Server=<server>.database.windows.net,1433;Database=MyDb;User Id=app;Password=***;Encrypt=true;TrustServerCertificate=false",
      "--security-mode", "readonly"
    ]
  }
}

Replace every placeholder (<server>.database.windows.net, MyDb, app, ***) with real values — never commit real credentials.

Connection strings may also be the ADO form shown above or a mssql:// URL (mssql://user:pass@host:1433/db?encrypt=true&trustServerCertificate=true); the server translates URL form itself (the underlying mssql driver only parses the ADO form).

Related MCP server: mcp-server-mssql

Security modes

Every tool call is classified into a risk category and checked against the server's --security-mode (default readonly) before it runs. destructive: true classifications additionally require --allow-destructive regardless of mode.

• arbitrary_sql always requires --allow-destructive as well as unsafe. A raw SQL fragment can contain any statement — a where of 1=1; DROP TABLE dbo.t -- on an mssql_execute_mutation update reaches the engine as a multi-statement batch — so every classification of risk arbitrary_sql is marked destructive, whatever produced it: where on execute_mutation/export_table_data/copy_between_databases, mssql_manage_query explain with analyze, a raw column default on manage_schema, a filtered-index where, a checkExpression, RLS predicates, and function/trigger bodies. In practice --security-mode unsafe on its own now grants nothing beyond admin; unsafe plus --allow-destructive is what enables raw SQL.

• --allow-destructive — permits classifications marked destructive (drop/delete/truncate/revoke/disable/reset-style operations, and every arbitrary_sql operation above), on top of whatever the mode already allows. Several argument flags additionally escalate an otherwise non-destructive operation into this category, because each one silently discards or disables something that already exists: replace: true on mssql_manage_functions create and on mssql_manage_triggers create (both emit CREATE OR ALTER, overwriting an existing body), replace: true on mssql_manage_rls create_policy (drops and recreates the policy and its predicate functions), and login: false on mssql_manage_users alter (ALTER LOGIN … DISABLE).

• --allow-tool-connection-string — permits a tool call to supply its own connectionString (or, for mssql_copy_between_databases, sourceConnectionString/targetConnectionString) instead of using the server's configured connection. mssql_copy_between_databases has no server-string fallback at all — both connection strings are required tool arguments, so the tool cannot be used unless this flag is set.

A denied call returns isError: true with one of two messages, taken verbatim from src/security/policy.ts:

Blocked by MSSQL MCP security policy: <reason>. Current mode "readonly" allows read operations.

or, when the mode would allow the risk but the destructive flag is missing:

Blocked by MSSQL MCP security policy: <reason>. Destructive operations require --allow-destructive.

Every denial is written to stderr as a single JSON line prefixed [MCP Audit] and, if --audit-file is set, appended to that file:

[MCP Audit] {"event":"mssql_mcp.security","outcome":"denied","reason":"security_policy_denied","toolName":"mssql_execute_sql","operation":null,"securityMode":"readonly","allowDestructive":false,"risk":"arbitrary_sql","destructive":true,"timestamp":"2026-08-26T12:00:00.000Z"}

Allowed calls are recorded on the two channels differently, so the file can be a complete record without flooding the client's log:

• --audit-file additionally records every allowed call whose risk is not read ("outcome":"allowed") — everything that could have changed state.

• stderr carries an allowed call only when MSSQL_MCP_AUDIT_ALLOWED=true.

MSSQL_MCP_AUDIT_ALLOWED is an environment variable only — there is no corresponding CLI flag. Audit lines never carry tool arguments, only the tool name, operation, mode and classification.

Warning at startup: in --security-mode admin or unsafe without --workspace-dir, the server prints once to stderr

[MCP Warning] File tools are enabled without --workspace-dir; export/import/copy can read and write any path this process can access. Set --workspace-dir to sandbox them.

Configuration

-cs/-tc are rewritten to --connection-string/--tools-config before argument parsing (Commander v15 rejects multi-character short flags outright); --cs/--tc are additionally registered as ordinary long-form aliases, so -cs, --cs and --connection-string are all equivalent (likewise -tc/--tc/--tools-config).

An invalid --security-mode exits with status 1 and prints:

securityMode must be one of readonly, write, admin, or unsafe.

Connection-string precedence (checked in this order): a tool-supplied connectionString (only when --allow-tool-connection-string is set) → --connection-string → MSSQL_CONNECTION_STRING → if none is set:

No connection string provided. Provide one in the tool arguments (requires --allow-tool-connection-string), via the --connection-string CLI option, or set the MSSQL_CONNECTION_STRING environment variable.

Tools config: if --tools-config points at a file with an enabledTools array, only those tools are registered; unknown names log [MCP Warning] Tool "<n>" specified in config file but not found in available tools.; calling a known-but-disabled tool returns Error: Tool "<name>" is available but not enabled by the current server configuration.

The allow-list fails closed: a file that cannot be read or is not { "enabledTools": string[] } stops the server rather than quietly enabling every tool, printing one of

[MCP Error] Could not read tools configuration file at <path>: <reason>
[MCP Error] Invalid tools configuration file format at <path>: expected { "enabledTools": string[] }

and exiting with status 1 before it serves anything.

Tools

All 18 tools are prefixed mssql_. Full parameter tables (types, defaults, whether each field is required) are generated from the server's own schemas in TOOL_SCHEMAS.md — run npm run docs to regenerate it after changing a tool's inputSchema.

Query execution notes

• mssql_execute_query runs exactly one SELECT/WITH statement. T-SQL does not need a ; between statements, so a second one can simply be appended to a query — SELECT 1 AS a USE master ran both and left the pooled session in master for every later call. Four rules enforce the single statement. The first three are applied to the query with the contents of string literals, comments and bracketed/quoted identifiers blanked out (so [Set], "Use" and '… use master' are data, not keywords); the fourth is a runtime check. Masking follows the engine's own lexing: a -- comment ends at the first carriage return or newline (SELECT 1 AS a --\rUSE master is not one long comment), block comments nest (/* a /* b */ c */ is blanked whole), and the delimiters ', ", [, ] are left in place so a blanked literal cannot be mistaken for "nothing was here":

  1. Statement keywords are rejected anywhere in the query: USE, SET, DECLARE, BEGIN, WHILE, IF, GOTO, RETURN, PRINT, RAISERROR, THROW, COMMIT, ROLLBACK, SAVE, OPEN, CLOSE, DEALLOCATE, RECONFIGURE, CHECKPOINT, BREAK, CONTINUE, DISABLE, ENABLE, RECEIVE, SEND, REVERT, ADD, alongside the data-changing set (INSERT/UPDATE/DELETE/MERGE/INTO/EXEC/DDL/DBCC/WAITFOR/WRITETEXT/UPDATETEXT/READTEXT/SETUSER).

  2. Multi-word phrases whose individual words must stay legal on their own: END CONVERSATION, MOVE CONVERSATION, GET CONVERSATION (a bare END still closes a CASE), and NEXT VALUE FOR — the one read-shaped expression that changes persistent state, because it advances a SEQUENCE (FETCH NEXT, (VALUES …), FOR XML and a column named next_value are all unaffected).

  3. Structure: exactly one statement SELECT — the first SELECT at bracket depth 0 (for a WITH query, the outer one after the CTE list). Any other SELECT must either sit inside brackets (a subquery, derived table, CTE body, EXISTS/IN, APPLY) or directly follow UNION/UNION ALL/EXCEPT/INTERSECT. A ( at depth 0 that opens a bracketed SELECT is itself only accepted where a subquery belongs — after a set operator, IN/EXISTS/ANY/ALL, FROM/JOIN/APPLY/AS/WHERE/ON, a comparison operator, a comma or another ( — so SELECT 1 AS a (SELECT DB_NAME()), which SQL Server runs as two statements, is rejected. Anything that fails these is query must contain exactly one statement.

  4. Result-set count, checked while the query runs. Every streamed path (select, and the count/exists fallbacks) refuses the query the moment the engine announces a second result set — query produced more than one result set; only a single SELECT statement is allowed — cancelling the request and returning that error instead of rows. This is what catches SELECT 1 AS a ((SELECT DB_NAME())), where the smuggled SELECT is bracketed and so indistinguishable, token by token, from the subquery in ISNULL((SELECT 1), 0). Precisely: a second SELECT smuggled inside doubled parentheses is refused when the engine announces its result set; if a row limit cancels the request before that happens, the smuggled statement may already have started executing server-side — it cannot return rows to the caller and, because the validator rejects every statement keyword and the NEXT VALUE FOR construct, it cannot change database or session state. (The limit cancels as soon as a row beyond it arrives and the driver announces nothing afterwards; draining the whole first result set so the guard always fires would turn every limited readonly select into a full scan.) Subqueries, IN (SELECT …), EXISTS (SELECT …), CTEs (including recursive ones), CROSS APPLY, derived tables, (VALUES …) with an alias column list, UNION [ALL]/EXCEPT/INTERSECT, CASE … END, OFFSET … FETCH NEXT, WITH (NOLOCK)/WITH TIES, OPENJSON … WITH, GROUPING SETS, FOR XML/FOR JSON, OPTION (RECOMPILE), IIF, DATEADD and columns such as EndDate/BeginDate/Settings/Address/EndConversation all still work. Rules 1–3 are a keyword and structure scan, not a T-SQL parser, and they err towards rejection: an unbracketed column literally named Open, Close, Set or Use is rejected — bracket it. Use mssql_execute_sql (unsafe + --allow-destructive) for anything else.

• mssql_execute_query's select operation accepts an optional limit, and defaults to 10000 rows when it is omitted; when the limit is reached the tool cancels the underlying request and returns exactly the rows collected so far. The success message then reads Query executed successfully. Retrieved <n> rows. Row limit of <limit> reached; more rows may exist. — that second sentence is added only when a row beyond the limit actually arrived, so a result of exactly limit rows never claims rows were left behind. count and exists are unaffected (they never materialise rows).

• mssql_execute_sql has no row limit or limit parameter at all; it returns every row every batch produces.

Export, import and copy

• CSV parsing is strict, not lenient. A " only opens quoted-field mode at the very start of a field; a " appearing elsewhere in an unquoted field is a literal character; a quoted field left open at end-of-file throws Unterminated quoted field in CSV input rather than silently merging the rest of the file into one record.

• mssql_import_table_data requires a JSON file to contain a top-level array of objects (Input file does not contain an array of records otherwise). Two input columns that collapse to the same table column case-insensitively (e.g. Name and name) are rejected as Column <c> is specified more than once. time columns are coerced from a bare HH:MM:SS[.fff] string by anchoring it to 1970-01-01 in UTC; a cell that cannot be coerced to its column's type (an unparsable number, date, or hex binary value) is rejected with Column <c>: cannot coerce "<v>" to <type> instead of reaching the driver as an opaque error or silently loading as NULL/zero.

• Computed and rowversion/timestamp columns are skipped, on both mssql_import_table_data and mssql_copy_between_databases: SQL Server refuses a supplied value for them (error 271), so any such column present in the input file or on the source table is dropped from the load and the target engine computes or stamps the value itself. This is silent — it is never reported as a missing or unknown column.

• Rows without an identity-column value present in the data are loaded through mssql's bulk sql.Table path with checkConstraints: true and fireTriggers: true — CHECK/FK constraints are enforced and INSERT triggers fire, just as they would for an ordinary INSERT. Rows whose data does include identity-column values are loaded instead via SET IDENTITY_INSERT ON and batched multi-row INSERT statements. mssql_copy_between_databases reuses the same loader on the target side.

• mssql_export_table_data never damages an existing file. Exporting to a path that already exists is an error — Output file already exists: <path>. Pass overwrite: true to replace it. — unless you pass overwrite: true. Either way rows are streamed into a temp file (<outputPath>.<pid>.<timestamp>.tmp) alongside the target and renamed onto it only once the export completed; if it fails partway (a permission error, a query error mid-stream, or the size cap being hit) only the temp file is removed, so the previous contents are still there and no corrupt/truncated file is left behind.

• CSV exports neutralise spreadsheet formulas. A string cell whose text starts with =, +, -, @, a tab or a carriage return, and does not look like a number, is written with a leading ' so Excel/Sheets treat it as text rather than evaluating it. Numeric-looking strings (-5, +62812) and real numbers are untouched, and mssql_import_table_data does not strip the quote — the exported file is the record.

• A cell that cannot be coerced on import is reported with at most 40 characters of its value (Column Qty: cannot coerce "…" to int), so an error message and its [MCP Error] log line never carry a whole cell.

• File paths for export/import: when --workspace-dir is set, every resolved path must stay inside it (segment-based .. and absolute-outside-workspace checks — Path "<path>" is outside the workspace directory.); a size cap from --max-file-bytes applies to both the source file being read (File exceeds max file size of <n> bytes.) and the file being written (Generated export exceeds max file size of <n> bytes.); only .json/.csv extensions matching the requested format are accepted.

PostgreSQL → SQL Server differences

Because the tool surface mirrors a Postgres server, several tools map a Postgres concept onto a different SQL Server mechanism:

manage_users behaviour notes

• drop always attempts DROP LOGIN for a login-type user once the database USER has been dropped, whenever a server login with that name exists — it does not check whether some other database on the instance still maps a user to that login. (Design note: the spec originally described a "no other database user maps to it" check; the shipped behaviour is the simpler unconditional drop, reported and accepted as the code of record.)

• alter maps superuser/createrole to db_owner/db_securityadmin database-role membership on Azure SQL Database (and to the sysadmin/securityadmin server roles elsewhere), the same mapping create uses; createdb has no Azure SQL Database equivalent and is reported under unsupported instead of being applied.

Authentication

Connection strings accept:

• SQL authentication: User Id=...;Password=... (SQL login or Azure AD login with a password).

• Azure AD: Authentication=Active Directory Password (with User Id/Password) or Authentication=Active Directory Default (uses the ambient Azure identity).

• NTLM: Authentication=NTLM;Domain=CORP with User Id/Password.

• Windows integrated authentication (SSPI) is not supported. The underlying driver (tedious) cannot do it; use a SQL login, NTLM with explicit credentials, or Azure AD instead.

Development

npm test              # unit + integration (integration skipped without MSSQL_TEST_CONNECTION_STRING)
npm run test:unit
npm run test:integration
npm run typecheck
npm run docs           # regenerate TOOL_SCHEMAS.md

Integration tests need a local SQL Server instance and a dedicated login. .env.test (gitignored) supplies MSSQL_TEST_CONNECTION_STRING. The login is created once with a generated password (PowerShell, run against localhost\SQLEXPRESS with Windows auth):

$pw = -join ((48..57 + 65..90 + 97..122) | Get-Random -Count 24 | ForEach-Object { [char]$_ }) + 'aZ9!'
sqlcmd -S "localhost\SQLEXPRESS" -E -Q "IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'mssql_mcp_test') BEGIN CREATE LOGIN [mssql_mcp_test] WITH PASSWORD = N'$pw', CHECK_POLICY = OFF; ALTER SERVER ROLE [sysadmin] ADD MEMBER [mssql_mcp_test]; END ELSE ALTER LOGIN [mssql_mcp_test] WITH PASSWORD = N'$pw';"
"MSSQL_TEST_CONNECTION_STRING=Server=localhost,1433;Database=master;User Id=mssql_mcp_test;Password=$pw;Encrypt=true;TrustServerCertificate=true" | Set-Content -Encoding utf8 .env.test

.env.test is gitignored; never commit or print its contents.

Known limitations

• No Windows integrated auth.

• OUTPUT on tables with triggers (returning in mutations) fails with SQL Server error 334.

• ONLINE = ON index operations need Enterprise/Azure; Express returns an error.

• count/exists on queries with ORDER BY but no TOP fall back to client-side streaming.

• Query Store statistics require Query Store to be enabled; plan-cache statistics are lost on restart / DBCC FREEPROCCACHE.

• rolledBack transaction count is not exposed by SQL Server DMVs and is always null.

• Computed and rowversion/timestamp columns are skipped on import and copy (SQL Server error 271 forbids supplying a value for them); the target engine assigns them.

• Symlinks and junctions inside --workspace-dir are followed: the sandbox validates the path you pass, not where a link inside the workspace points. Do not place links to sensitive locations in a workspace directory.

• INSTEAD OF triggers defined on views are not listed by mssql_manage_triggers get and cannot be toggled with set_state — both work through sys.tables and ALTER TABLE … ENABLE|DISABLE TRIGGER.

• The read-only validator for mssql_execute_query is a keyword and structure scan over masked SQL, not a T-SQL parser, and errs on the side of rejection: an unbracketed column named Open, Close, Set, Use, Save, Send or Return is refused — bracket it ([Open]) or alias it. (Add is not affected: ADD is reserved in T-SQL, so it can never be an unbracketed column name.)

• mssql_export_table_data checks that the output path does not exist and later renames its temp file onto it; those two steps are not atomic, so a file created by another process in between is replaced. The check guards against overwriting a file that was already there, not against a concurrent writer.

• Without --workspace-dir, the file tools in admin/unsafe can read and write anywhere the server process can reach; you are warned once at startup.

• Pools created from tool-supplied connection strings are closed after 60 seconds idle (swept every 15 seconds), measured from the last request issued on them and never while a request is in flight; the server's own --connection-string pool is never evicted. A call after eviction simply reconnects.