Skip to content
Back to search

apoint

MarketplaceWorks with Claude CodeGitHub

Microsoft SQL Server MCP

Safety scan

A static text scan, not a guarantee. Select the badge to see what was scanned and every finding.

Badge for your README
findsafeskills safety rating

Markdown

[![findsafeskills safety rating](/api/listings/a-point-systems-ltd--ms-sql-mcp/badge.svg)](/listings/a-point-systems-ltd--ms-sql-mcp)

HTML

<a href="/listings/a-point-systems-ltd--ms-sql-mcp"><img src="/api/listings/a-point-systems-ltd--ms-sql-mcp/badge.svg" alt="findsafeskills safety rating" /></a>

Install

Install instructions from the README

As written by the repo's author. findsafeskills has not verified these steps.

Option 1: VS Code / Cursor extension

1. Install **APoint-ms-sql** (ID `apoint.ms-sql-mcp`). Use one of these sources: - **VS Code:** the Extensions view (Ctrl+Shift+X); the extension is on the Visual Studio Marketplace. - **Cursor:** its Extensions view; the extension is on Open VSX. - **Offline or pinned version:** download `ms-sql-mcp-win32-x64-<version>.vsix` from the repository's [GitHub releases](https://github.com/A-Point-Systems-ltd/ms-sql-mcp/releases) (tag `ext-v<version>`). Then run **Extensions: Install from VSIX...** from the Command Palette, or: ```powershell code --install-extension ms-sql-mcp-win32-x64-<version>.vsix ``` For Cursor, use `cursor --install-extension`. 2. Open the **APoint-ms-sql** view in the activity bar and click **Add connection**. Fill in the form, click **Test connection**, then **Save**. Passwords stay in VS Code SecretStorage. 3. **Agents:** VS Code agent mode and Cursor get the MCP server automatically for every open connection. Nothing else to configure. 4. **Optional:** to use the same connections from **Cursor CLI, Claude Desktop or Claude Code**, run **Register with Cursor / Claude...** from the view's title bar and restart those clients. For Claude Desktop this also turns on the [connection manager](#8-connection-manager-claude-desktop-mcp-apps).

The extension's own documentation covers the explorer, query windows and settings: [vscode-extension/README.md](vscode-extension/README.md).

Option 2: Claude Desktop extension (.mcpb)

1. Download `apoint-ms-sql-<version>.mcpb` from the repository's [GitHub releases](https://github.com/A-Point-Systems-ltd/ms-sql-mcp/releases) (tag `ext-v<version>`, 1.0.10 and later), or build it with `.\packaging\mcpb\build-mcpb.ps1` (see [Package the Claude Desktop extension](#package-the-claude-desktop-extension-mcpb)). 2. Install it: double-click the file, or open Claude Desktop **Settings > Extensions** and drag the file in. Then confirm the install. 3. **Optional:** the extension's settings have three switches, all on by default: - **AI Insights layer** (`USE_INSIGHTS_LAYER`). - **Compact row results (TOON)** (`MSSQL_TOON`): turn off to return JSON rows by default. - **Connection manager: require MCP Apps support** (`MSSQL_APPS_REQUIRE_UI_CAPABILITY`): turn off only if the connection manager form opens but its buttons fail (the server log then has a Warning naming the client). 4. In a chat, ask Claude to **"manage connections"**. A form opens in the chat: - Click **Add connection**, fill it in, then **Test connection** and **Save**. - The connection is usable in the next message, without restarting Claude. - Connections are read-only by default. SQL-login passwords are stored encrypted for your Windows account, and Claude never sees them.

Connections are saved in `%APPDATA%\APoint-ms-sql\connections.json`. The VS Code extension's Claude Desktop registration uses the same file. Use **either** the `.mcpb` **or** the VS Code registration for Claude Desktop, not both: with both, Claude lists the server twice.

To uninstall, use **Settings > Extensions** in Claude Desktop. The connections file stays; delete it if you no longer need it.

Option 3: Server executable with any MCP client

1. Get `MssqlMcp.exe`. Build it with `.\publish-release.ps1` ([Publish single-file executable](#publish-single-file-executable)), or build from source ([Quick start](#quick-start-from-source)). 2. Add it to your client's MCP configuration with `CONNECTION_STRING`, `MSSQL_CONNECTIONS` or `MSSQL_CONNECTIONS_FILE`. See [MCP client configuration](#mcp-client-configuration) and [Multiple connections](#multiple-connections). 3. **Optional:** to get the connection manager form in hosts that support MCP Apps (such as Claude Desktop), add `MSSQL_MANAGED_CONNECTIONS_FILE`.

What was scanned

The files the safety scan read, as written by the repository's author and shown as plain text. Lines with a finding are marked.

.claude-plugin/marketplace.json · Manifest · 14 lines
  1. {
  2. "name": "apoint",
  3. "owner": {
  4. "name": "A Point Systems"
  5. },
  6. "plugins": [
  7. {
  8. "name": "ms-sql-mcp",
  9. "source": "./plugins/ms-sql-mcp",
  10. "description": "Microsoft SQL Server MCP server for Claude Code on Windows, with the mssql-insights-ops skill.",
  11. "version": "1.2.5"
  12. }
  13. ]
  14. }
README.md · README · 799 lines
  1. # MSSQL MCP Server (.NET 10)
  2. [![MCP Registry](https://img.shields.io/badge/dynamic/json?url=https%3A%2F%2Fregistry.modelcontextprotocol.io%2Fv0.1%2Fservers%2Fio.github.A-Point-Systems-ltd%252Fms-sql-mcp%2Fversions%2Flatest&query=%24.server.version&label=MCP%20Registry&prefix=v&color=blue)](https://registry.modelcontextprotocol.io/v0.1/servers/io.github.A-Point-Systems-ltd%2Fms-sql-mcp/versions/latest)
  3. A [Model Context Protocol](https://modelcontextprotocol.io/) (MCP) server for Microsoft SQL Server and Azure SQL Database. Built with the official [MCP C# SDK](https://github.com/modelcontextprotocol/csharp-sdk) (`ModelContextProtocol` 2.2.0) on .NET 10.
  4. Forked from [Azure-Samples/SQL-AI-samples](https://github.com/Azure-Samples/SQL-AI-samples) and extended with unified object introspection, strict read/write SQL routing, and an optional **AI Insights** cache layer that helps LLM agents investigate databases with less repeated schema work.
  5. It ships in three forms:
  6. | Package | For | What you get |
  7. |---------|-----|--------------|
  8. | **VS Code / Cursor extension** (`apoint.ms-sql-mcp`) | Developers working in VS Code or Cursor | Bundled server for agent mode, connection form, object explorer, query windows, IntelliSense, and one-command registration with Cursor CLI, Claude Desktop and Claude Code |
  9. | **Claude Desktop extension** (`.mcpb`) | Claude Desktop users without VS Code | One file install; connections are managed in a form inside the chat |
  10. | **Server executable** (`MssqlMcp.exe`) | Any other MCP client, servers, CI | Configure it yourself with environment variables |
  11. See [Install](#install).
  12. ## Table of contents
  13. - [Requirements](#requirements)
  14. - [Install](#install)
  15. - [Quick start (from source)](#quick-start-from-source)
  16. - [MCP client configuration](#mcp-client-configuration)
  17. - [Environment variables](#environment-variables)
  18. - [Multiple connections](#multiple-connections)
  19. - [MCP tools reference](#mcp-tools-reference)
  20. - [AI Insights layer](#ai-insights-layer)
  21. - [Response shape](#response-shape)
  22. - [Build, publish, and test](#build-publish-and-test)
  23. - [Project layout](#project-layout)
  24. - [Security notes](#security-notes)
  25. - [Troubleshooting](#troubleshooting)
  26. - [License](#license)
  27. ## Requirements
  28. | Component | Version |
  29. |-----------|---------|
  30. | Operating system | **Windows x64** (the packaged server is a `win-x64` executable) |
  31. | .NET SDK | **.NET 10.0**, only to build from source. The VS Code extension, the `.mcpb` and `publish-release.ps1` output are self-contained and need no .NET install. |
  32. | SQL Server | **2008 R2 (10.50)** or later |
  33. | Azure SQL Database | Supported |
  34. Tested target versions include SQL Server 2008 R2 through 2022 and Azure SQL Database.
  35. With a single `CONNECTION_STRING` (legacy mode) the server validates it and opens a test connection **before** starting the MCP transport. If either check fails, the process exits with code `1` and writes diagnostics to the log file. With several connections, startup behaves differently; see [Multiple connections](#multiple-connections).
  36. ## Install
  37. ### Option 1: VS Code / Cursor extension
  38. 1. Install **APoint-ms-sql** (ID `apoint.ms-sql-mcp`). Use one of these sources:
  39. - **VS Code:** the Extensions view (Ctrl+Shift+X); the extension is on the Visual Studio Marketplace.
  40. - **Cursor:** its Extensions view; the extension is on Open VSX.
  41. - **Offline or pinned version:** download `ms-sql-mcp-win32-x64-<version>.vsix` from the repository's [GitHub releases](https://github.com/A-Point-Systems-ltd/ms-sql-mcp/releases) (tag `ext-v<version>`). Then run **Extensions: Install from VSIX...** from the Command Palette, or:
  42. ```powershell
  43. code --install-extension ms-sql-mcp-win32-x64-<version>.vsix
  44. ```
  45. For Cursor, use `cursor --install-extension`.
  46. 2. Open the **APoint-ms-sql** view in the activity bar and click **Add connection**. Fill in the form, click **Test connection**, then **Save**. Passwords stay in VS Code SecretStorage.
  47. 3. **Agents:** VS Code agent mode and Cursor get the MCP server automatically for every open connection. Nothing else to configure.
  48. 4. **Optional:** to use the same connections from **Cursor CLI, Claude Desktop or Claude Code**, run **Register with Cursor / Claude...** from the view's title bar and restart those clients. For Claude Desktop this also turns on the [connection manager](#8-connection-manager-claude-desktop-mcp-apps).
  49. The extension's own documentation covers the explorer, query windows and settings: [vscode-extension/README.md](vscode-extension/README.md).
  50. ### Option 2: Claude Desktop extension (.mcpb)
  51. 1. Download `apoint-ms-sql-<version>.mcpb` from the repository's [GitHub releases](https://github.com/A-Point-Systems-ltd/ms-sql-mcp/releases) (tag `ext-v<version>`, 1.0.10 and later), or build it with `.\packaging\mcpb\build-mcpb.ps1` (see [Package the Claude Desktop extension](#package-the-claude-desktop-extension-mcpb)).
  52. 2. Install it: double-click the file, or open Claude Desktop **Settings > Extensions** and drag the file in. Then confirm the install.
  53. 3. **Optional:** the extension's settings have three switches, all on by default:
  54. - **AI Insights layer** (`USE_INSIGHTS_LAYER`).
  55. - **Compact row results (TOON)** (`MSSQL_TOON`): turn off to return JSON rows by default.
  56. - **Connection manager: require MCP Apps support** (`MSSQL_APPS_REQUIRE_UI_CAPABILITY`): turn off only if the connection manager form opens but its buttons fail (the server log then has a Warning naming the client).
  57. 4. In a chat, ask Claude to **"manage connections"**. A form opens in the chat:
  58. - Click **Add connection**, fill it in, then **Test connection** and **Save**.
  59. - The connection is usable in the next message, without restarting Claude.
  60. - Connections are read-only by default. SQL-login passwords are stored encrypted for your Windows account, and Claude never sees them.
  61. Connections are saved in `%APPDATA%\APoint-ms-sql\connections.json`. The VS Code extension's Claude Desktop registration uses the same file. Use **either** the `.mcpb` **or** the VS Code registration for Claude Desktop, not both: with both, Claude lists the server twice.
  62. To uninstall, use **Settings > Extensions** in Claude Desktop. The connections file stays; delete it if you no longer need it.
  63. ### Option 3: Server executable with any MCP client
  64. 1. Get `MssqlMcp.exe`. Build it with `.\publish-release.ps1` ([Publish single-file executable](#publish-single-file-executable)), or build from source ([Quick start](#quick-start-from-source)).
  65. 2. Add it to your client's MCP configuration with `CONNECTION_STRING`, `MSSQL_CONNECTIONS` or `MSSQL_CONNECTIONS_FILE`. See [MCP client configuration](#mcp-client-configuration) and [Multiple connections](#multiple-connections).
  66. 3. **Optional:** to get the connection manager form in hosts that support MCP Apps (such as Claude Desktop), add `MSSQL_MANAGED_CONNECTIONS_FILE`.
  67. ## Quick start (from source)
  68. ```powershell
  69. # Clone and build
  70. cd MssqlMcp
  71. dotnet build
  72. # Run unit tests (no live DB required for most tests)
  73. cd ..
  74. dotnet test
  75. ```
  76. Point your MCP client at the built executable:
  77. ```
  78. MssqlMcp\bin\Debug\net10.0\MssqlMcp.exe
  79. ```
  80. Set `CONNECTION_STRING` (or `MSSQL_CONNECTIONS`, see [Multiple connections](#multiple-connections)) in the MCP server environment (see [sample_mcp.json](sample_mcp.json) for a template).
  81. **First prompt to try:** “List tables in the database” (the agent should call `list_objects` with `objectType=Table`).
  82. ## MCP client configuration
  83. ### Cursor
  84. Add a project-level or user-level MCP config. Example (adjust paths and connection string):
  85. ```json
  86. {
  87. "mcpServers": {
  88. "MSSQL-MCP": {
  89. "type": "stdio",
  90. "command": "C:\\Development\\MCPs\\MS-SQL\\MssqlMcp\\bin\\Debug\\net10.0\\MssqlMcp.exe",
  91. "env": {
  92. "CONNECTION_STRING": "Server=.;Database=MyDb;Trusted_Connection=True;TrustServerCertificate=True",
  93. "USE_INSIGHTS_LAYER": "true",
  94. "INSIGHTS_AUTOPOPULATE": "true",
  95. "LOG_FILE_PATH": "C:\\Logs\\mssql-mcp.log"
  96. }
  97. }
  98. }
  99. }
  100. ```
  101. Restart the MCP server after changing environment variables.
  102. ### VS Code (GitHub Copilot Agent)
  103. Open **Settings (JSON)** and add under `mcp.servers`:
  104. ```json
  105. "mcp": {
  106. "servers": {
  107. "MSSQL MCP": {
  108. "type": "stdio",
  109. "command": "C:\\path\\to\\MssqlMcp.exe",
  110. "env": {
  111. "CONNECTION_STRING": "Server=.;Database=test;Trusted_Connection=True;TrustServerCertificate=True"
  112. }
  113. }
  114. }
  115. }
  116. ```
  117. ### Claude Desktop
  118. Edit `claude_desktop_config.json`:
  119. ```json
  120. {
  121. "mcpServers": {
  122. "MSSQL MCP": {
  123. "command": "C:\\path\\to\\MssqlMcp.exe",
  124. "env": {
  125. "CONNECTION_STRING": "Server=.;Database=test;Trusted_Connection=True;TrustServerCertificate=True"
  126. }
  127. }
  128. }
  129. }
  130. ```
  131. ### Azure SQL (Entra ID interactive)
  132. ```json
  133. "CONNECTION_STRING": "Server=tcp:<server>.database.windows.net,1433;Initial Catalog=<database>;Encrypt=Mandatory;TrustServerCertificate=False;Connection Timeout=30;Authentication=Active Directory Interactive"
  134. ```
  135. ### Production-style single-file publish
  136. ```powershell
  137. .\publish-release.ps1
  138. ```
  139. This produces a self-contained `MssqlMcp.exe` (default output: `C:\Development\MCPs\MS-SQL-Release\`). Point MCP clients at that executable instead of the Debug build.
  140. ## Environment variables
  141. | Variable | Required | Default | Description |
  142. |----------|----------|---------|-------------|
  143. | `CONNECTION_STRING` | One of the three connection variables | — | ADO.NET connection string for a single database (legacy mode, profile name `default`). Validated at startup; the process exits with code `1` if it cannot connect. |
  144. | `MSSQL_CONNECTIONS` | One of the three connection variables | — | JSON array of named connections. See [Multiple connections](#multiple-connections). |
  145. | `MSSQL_CONNECTIONS_FILE` | One of the three connection variables | — | Path to a file holding the same JSON array. |
  146. | `MSSQL_MANAGED_CONNECTIONS_FILE` | No | — | Path of a file the server itself reads **and writes**: the connections the user adds, edits and removes in the connection manager view (MCP Apps, e.g. Claude Desktop). It may not exist yet; with it set the server also starts with no connection. `%VAR%` is expanded. See [Connection manager](#8-connection-manager-claude-desktop-mcp-apps). |
  147. | `MSSQL_ALLOW_ADHOC_CONNECTIONS` | No | disabled | Set to `true` to let `open_connection` register new connections from a raw connection string at runtime. |
  148. | `MSSQL_ADHOC_ALLOW_INTEGRATED_AUTH` | No | disabled | Set to `true` to allow ad-hoc connections that use `Integrated Security` / `Trusted_Connection` or any `Authentication=Active Directory*` method (they send the server's own identity to the target host). |
  149. | `MSSQL_ADHOC_ALLOW_WRITE` | No | disabled | Set to `true` to allow `open_connection` with `readOnly=false`. Without it, writable ad-hoc connections are refused. |
  150. | `MSSQL_ADHOC_ALLOWED_HOSTS` | No | any host | Comma-separated host names. When set, ad-hoc connections are refused unless the host part of `Data Source` (without `tcp:`, instance or port) matches one of them, case-insensitively. |
  151. | `USE_INSIGHTS_LAYER` | No | enabled | Opt-**out** switch. Set to `false`, `0`, `no`, `off`, or `disabled` to disable the AI Insights layer. Any other value (including unset) leaves it enabled. |
  152. | `INSIGHTS_AUTOPOPULATE` | No | enabled | Opt-out. When enabled (and insights layer is on), introspection auto-creates mechanical baseline insights and attaches enrichment directives. Set to a falsey value to disable auto-population only. |
  153. | `MSSQL_SCRIPT_RUNNER` | No | disabled | Set to `true` to register the extension-only `run_script`, `ddl_history` and `language_service` tools. For the VS Code extension's private runner process only; do not enable it for agent clients. See [Script runner (extension only)](#script-runner-extension-only), [DDL history (extension only)](#ddl-history-extension-only) and [IntelliSense (extension only)](#intellisense-extension-only). |
  154. | `MSSQL_APPS_REQUIRE_UI_CAPABILITY` | No | enabled | With `MSSQL_MANAGED_CONNECTIONS_FILE`: list and run the connection view's app-only `connections_ui_*` tools only for clients that advertise the MCP Apps extension. Set to `false`, `0`, `no`, `off` or `disabled` only for an MCP Apps host that does not advertise it (the tools then rely on the host to hide them from the model). See [Connection manager](#8-connection-manager-claude-desktop-mcp-apps). |
  155. | `MSSQL_TOON` | No | enabled | Default row format of `read_data`, `list_objects`, `list_insights` and `get_insight_history` when a call does not pass `toon`. Set to `false`, `0`, `no`, `off` or `disabled` to return JSON by default (calls can still pass `toon=true`); the server instructions follow the setting. See [TOON row format](#tool-reference). |
  156. | `MSSQL_PROBE_TOOLS` | No | disabled | Set to `true` to register the extension-only `probe_test`, `probe_list_databases`, `probe_database_state` and `probe_bring_online` tools used by the VS Code connection form. For the extension's short-lived probe process only; **never enable it for agent clients**: `probe_bring_online` runs `ALTER DATABASE … SET ONLINE`, even on a read-only connection. |
  157. | `MSSQL_CONSOLE_LOG_LEVEL` | No | `Warning` | Minimum level of the console logger, which writes to stderr (stdout carries the MCP protocol). One of `Trace`, `Debug`, `Information`, `Warning`, `Error`, `Critical`, `None` (case-insensitive). Some clients (Cursor) show every stderr line as `[error]`, so the default keeps routine `info:` lines out of their logs. When it is not set, the standard .NET configuration applies if it sets a level for the console: `Logging:Console:LogLevel:Default`, then `Logging:LogLevel:Default` (from `appsettings.json` or env such as `Logging__LogLevel__Default`); otherwise `Warning`. An invalid value writes one warning line to stderr and is ignored (the configuration, or `Warning`, applies). `FATAL:` startup messages are always written. The `LOG_FILE_PATH` log is not affected. |
  158. | `LOG_FILE_PATH` | No | `%LOCALAPPDATA%\MssqlMcp\Logs\` (Windows) or `~/.local/share/MssqlMcp/Logs/` (Linux/macOS) | Full file path, or a directory (timestamped log files are created inside it). |
  159. When both `USE_INSIGHTS_LAYER` and `INSIGHTS_AUTOPOPULATE` are enabled, the server also enables baseline row-count probing, baseline refresh during DDL scans, `insightEnrichment` response directives, and the insight-update rule in the server instructions (all derived from those two flags — there are no separate env vars for them).
  160. ## MCP tools reference
  161. The server exposes **23 tools** through a single partial `Tools` class (plus the opt-in, extension-only `run_script`, `ddl_history` and `language_service`; see [Script runner (extension only)](#script-runner-extension-only)). MCP wire names are **snake_case** (pinned explicitly in `MssqlMcp/ToolNames.cs`). Legacy per-type list/get helpers (`ListTables`, `GetStoredProc`, etc.) remain as internal C# methods; clients should use the unified tools below.
  162. > **Breaking change (.NET 10 / MCP SDK 2.x upgrade):** tool names changed from PascalCase (`ReadData`, `ExecuteSQL`, …) to snake_case (`read_data`, `execute_sql`, …). Update client tool allow-lists, auto-approve rules and saved prompts that reference the old names.
  163. ### Read-only inspection
  164. | Tool | Purpose |
  165. |------|---------|
  166. | **list_objects** | List objects by `objectType`: `Table`, `View`, `StoredProcedure`, `TableFunction`, `ScalarFunction`, `Function` (scalar + table), `TableTrigger`, `Index`, `ForeignKey` (each with its parent table or view), `SysObject`, `DatabaseTrigger`, `Type` (user-defined alias/table types), `Login`, `ServerRole`, `DatabaseUser`, `DatabaseRole`. **Privacy:** the security types return principal names (logins and users often identify people) and role membership to the calling agent and its LLM provider; passwords, hashes and SIDs are never returned. Optional `partialName` does a `LIKE` filter on name and `schema.name`. For `SysObject`, optional `sysObjectType` filters by `sys.objects.type` (`U`, `V`, `P`, `FN`, …). |
  167. | **describe_table** | Full table metadata: columns (type, nullability, descriptions), indexes, constraints, foreign keys, triggers. Preferred over ad-hoc `sys.*` queries for one table. |
  168. | **describe_view** | View metadata, column list, indexes (`name`, `type`, `isUnique`, `keys`), and full T-SQL definition. |
  169. | **script_object** | Ready-to-run T-SQL DDL for one object: `Table`, `View`, `Index` (with `parent`), `ForeignKey`, `TableTrigger`, `StoredProcedure`, `TableFunction`, `ScalarFunction`, `DatabaseTrigger`, `Type`, `Login`, `ServerRole`, `DatabaseUser`, `DatabaseRole`. Returns `{ objectType, schema, name, form, ddl, warnings }`; `form` is `Create`, `CreateOrAlter` (SQL Server 2016 SP1+) or `Alter` for programmable objects. The optional `form` parameter (`create` by default, or `alter`) returns a `View`, `StoredProcedure`, `TableFunction`, `ScalarFunction`, `TableTrigger` or `DatabaseTrigger` as `CREATE OR ALTER` (2016 SP1+) or `ALTER` (older servers), ready to edit and re-run; for a view with indexes the script carries a warning, because altering an indexed view drops its indexes (the index statements in the script recreate them). Any other type with `form: "alter"`, or any other `form` value, is an error. Passwords, hashes and SIDs are never scripted (placeholders instead); unsupported features (partitioning, compression, XML/columnstore indexes, encrypted modules, ...) are listed in `warnings`. Principal names are returned (see privacy note above). Read-only. |
  170. | **get_object** | Stored procedure, function, or trigger: parameters (where applicable) + definition. `objectType`: `StoredProcedure`, `Function`, or `Trigger`. Trigger names accept `name`, `schema.name`, or `schema.table.name`. |
  171. | **read_data** | **All** read-only `SELECT` / `WITH … SELECT` queries. Use for user tables, `sys.*`, `INFORMATION_SCHEMA`, and DMVs. |
  172. | **get_server_info** | Server version/edition, hardware DMVs (with graceful degradation), and user-database counts. |
  173. ### Data modification and DDL
  174. | Tool | MCP flags | Purpose |
  175. |------|-----------|---------|
  176. | **create_table** | write | Run a `CREATE TABLE` statement. |
  177. | **drop_table** | write, destructive | Run a `DROP TABLE` statement. Confirm with the user first. |
  178. | **insert_data** | write | Run a single `INSERT` statement. |
  179. | **update_data** | write, destructive | Run a single `UPDATE` statement. Always use a `WHERE` clause. |
  180. | **execute_sql** | write, destructive | DDL/DML only (`INSERT`, `UPDATE`, `DELETE`, `MERGE`, `CREATE`, `ALTER`, `DROP`, `TRUNCATE`, `EXEC`, …). **SELECT is rejected** — use `read_data`. Multi-batch `GO` scripts are not supported. |
  181. ### AI Insights (8 tools)
  182. Requires `USE_INSIGHTS_LAYER` enabled (default) and **install_insights_layer** run once per database.
  183. | Tool | Purpose |
  184. |------|---------|
  185. | **insights_check** | Reports layer install state, DDL audit presence, insight row count, and DDL processing watermark. |
  186. | **install_insights_layer** | Idempotent install of `AIInsights` schema, `SchemaInsights`, `InsightHistory`, `DdlChangeWatermark`, `dbo.DDL_AuditLog`, and the database-level `DDL_Audit` trigger. |
  187. | **get_insight** | Read cached insight + freshness for one object. |
  188. | **upsert_insight** | Create or update a cached insight (UPDATE-then-INSERT; fingerprint captured server-side). |
  189. | **list_insights** | Recent rows from `AIInsights.SchemaInsights`. |
  190. | **get_insight_history** | Archived rows from `AIInsights.InsightHistory`. |
  191. | **refresh_insights** | Process DDL backlog / fingerprint drift; returns recent summaries. |
  192. | **rebuild_baseline_insights** | Bulk warm mechanical baselines (`schemaName?`, `objectType?`, `take` 1–2000). |
  193. When `USE_INSIGHTS_LAYER=false`, insight-specific tools return errors or empty status; introspection tools skip insight enrichment.
  194. ### Connection management (3 tools)
  195. | Tool | MCP flags | Purpose |
  196. |------|-----------|---------|
  197. | **list_connections** | read-only | Lists every registered connection: name, open/closed, read-only, server, database (never credentials), plus `connectionRequired` and `count`. Call it first in a session. |
  198. | **open_connection** | write | Reopens a configured connection, or (only with `MSSQL_ALLOW_ADHOC_CONNECTIONS=true`) registers an ad-hoc one from `connectionString`. Ad-hoc connections are read-only unless `readOnly=false` (which needs `MSSQL_ADHOC_ALLOW_WRITE=true`); see [ad-hoc limits](#5-mssql_allow_adhoc_connections). The connection is tested (5 s cap) before it is registered. |
  199. | **close_connection** | write | Closes a connection. Ad-hoc connections are forgotten; configured ones stay listed as closed and can be reopened. The last open connection cannot be closed. |
  200. These three tools take no `connection` argument. Every other tool accepts an optional `connection` argument; see [Multiple connections](#multiple-connections).
  201. ### Read vs execute routing
  202. `SqlStatementClassifier` parses every statement with the T-SQL parser (`Microsoft.SqlServer.TransactSql.ScriptDom`) and enforces a strict split. Every tool accepts **exactly one statement**; T-SQL needs no `;` between statements, so text such as `SELECT 1 WAITFOR DELAY '…'` counts as two statements and is rejected.
  203. - **read_data** — a single `SELECT` / `WITH … SELECT`. `SELECT … INTO`, `OPENQUERY` / `OPENROWSET` / `OPENDATASOURCE` and linked-server (4-part) names are rejected. The query runs inside a transaction that is always rolled back. At most `maxRows` rows are returned (default 500, max 10000). `data` is always the row array; when more rows exist the response also carries top-level `truncated: true` and `maxRows`. Rows come as TOON by default (see below).
  204. - **TOON row format** — `read_data`, `list_objects`, `list_insights` and `get_insight_history` take `toon` (default `true`). A successful result whose `data` is 2 or more uniform rows (or 2 or more plain values) is sent as [TOON](https://github.com/toon-format/spec) (v4, comma delimiter) instead of JSON: column names appear once in a header instead of on every row (a 50-row test result is under 60% of the JSON's length). The envelope fields come first and nulls are dropped:
  205. ```
  206. success: true
  207. truncated: true
  208. maxRows: 500
  209. data[2]{InvoiceID,Customer,Amount}:
  210. 1,Acme Ltd,1250.00
  211. 2,"Smith, J",80.5
  212. ```
  213. **Upgrading to 1.1.0:** this is a breaking change for clients that parse these four tools as JSON. Pass `toon=false`, or set `MSSQL_TOON=false` (Claude Desktop extension: turn off **Compact row results (TOON)**). It only shows with 2+ rows, so test with multi-row results.
  214. Values that could be misread (commas, `:`, brackets, quotes, leading/trailing spaces, `true`/`false`/`null`, number-like text) are quoted and JSON-escaped. Numbers are copied exactly from the JSON (no rounding of decimals or bigints). Errors, single rows, nested shapes and `toon=false` give the usual JSON. Clients that parse results as JSON must pass `toon=false`; the VS Code extension does so for all its own calls.
  215. - **insert_data / update_data / create_table / drop_table** — only the matching statement type (`INSERT`, `UPDATE`, `CREATE TABLE`, `DROP TABLE`) is accepted, so `insert_data("DROP TABLE x")` is refused.
  216. - **execute_sql** — single DDL/DML statements (including `SELECT … INTO`; a `CREATE PROCEDURE` body counts as one statement). Any plain `SELECT` is rejected with a message pointing to `read_data`. `SET`, `DECLARE`, `USE`, `WAITFOR` and `SHUTDOWN` are rejected as unsupported.
  217. This keeps destructive operations behind an explicitly flagged tool and prevents accidental full-table reads through the write path.
  218. ### Script runner (extension only)
  219. `run_script` is the query-window runner of the MSSQL-MCP VS Code extension. It is **not** one of the 23 agent tools: the server lists it only when the process environment has `MSSQL_SCRIPT_RUNNER=true`. **Do not enable it for agent clients** (Cursor, Copilot, Claude Desktop): it runs arbitrary multi-statement scripts on read/write connections.
  220. - Arguments: `script` (required), `maxRows` (per result set, default 1000, clamped to 1..10000), `connection`.
  221. - Splits the script on SSMS-style `GO` lines (`GO n` repeats a batch; `GO` inside strings, comments or `[identifiers]` does not split) and runs every batch on one session, so `SET` options and `#temp` tables carry across batches.
  222. - Returns `data: { resultSets, messages, hadErrors, batches, elapsedMs }`. Each result set has `batch`, `columns` (`name`, `type`), `rows` (arrays in column order), `rowCount` (total) and `truncated`. Messages have `kind` (`info`, `rows`, `error`, `warning`), `text` and `line` (1-based script line, or null). Errors that name a procedure, function, view or trigger use the SSMS header `Msg n, Level l, State s, Procedure p, Line n`. When the batch is that module's own `CREATE` / `ALTER` (a compile error), the line is mapped to the script; otherwise (for example an error raised inside an `EXEC`'d procedure) it is the module's own line number and `line` is null. SQL errors are `error` messages and execution continues with the next batch, like SSMS; `success: false` only for an empty script or a connection that cannot be opened.
  223. - **New session per run.** Each call opens its own connection outside the connection pool (`Pooling=false`) and closes it at the end, so session state from an earlier run (`SET TRANSACTION ISOLATION LEVEL`, other `SET` options, `sp_setapprole`, `EXECUTE AS` without `REVERT`) never reaches the next one. Within one run, all batches share that session.
  224. - **Size caps.** A cap never stops execution: the script runs to the end, only what is kept and returned is limited, and errors are never lost silently (see the Errors row). Each cap adds one `warning` per run.
  225. | Cap | Limit | After the cap |
  226. |---|---|---|
  227. | Rows | 50000 per run, across all result sets | Result sets keep their columns and `rowCount` but have no rows, and `truncated: true`. |
  228. | Response size | about 32 MB per run (rendered cell or message text length x2 + 16 bytes each) | Later rows are counted in `rowCount` but not kept (`truncated: true`), and later `info` / `rows` messages are dropped. Errors and warnings are still kept: they do not need room in the budget. |
  229. | Result sets | 200 per run | Later result sets are read to the end but not returned. |
  230. | Messages | 10000 per run | Later `info` and `rows` messages are dropped; `error` and `warning` messages are kept. |
  231. | Errors | 1000 per run | After the first 1000 errors only the most recent one is kept (it replaces the previous one), after the warning `Error limit of 1000 reached; further errors were dropped (the last one is shown).` `hadErrors` stays true. |
  232. | String value | 65536 characters | Cut, with the suffix `… (truncated, N chars)` (N = full length). |
  233. | Binary value | 32768 bytes | Cut before hex encoding, with the suffix `… (truncated, N bytes)`. |
  234. | `GO n` | n at most 10000 | That batch is not run and gets an `error` message; later batches still run. |
  235. - **Value formats.** Values that a JSON number (a double in the extension) would round are sent as exact strings: `decimal` / `numeric`, `money` / `smallmoney` (invariant culture, for example `"12345678901234.5678"`), and `bigint` values outside +/-2^53 (`"9223372036854775807"`). Smaller `bigint` values, `int`, `float` and `bit` stay JSON numbers or booleans; dates, times and `uniqueidentifier` are strings as before. Binary values (`varbinary`, `binary`, `timestamp`) are SSMS-style `0x` + uppercase hex (`"0x00FF10"`), no longer base64.
  236. - **Cancel.** Cancelling the call (the client sends `notifications/cancelled` for its request id; the extension does this for Cancel and when the tab closes) cancels the running command on SQL Server, rolls back a transaction the script left open, and closes the session, so its locks are released. Statements that already completed outside a transaction, and transactions the script already committed, stay applied.
  237. - On a read-only connection every batch must be a single read-only `SELECT` (the `read_data` rules) and runs inside a transaction that is always rolled back; other batches are refused with an `error` message that starts with `Read-only connection: only a single read-only SELECT per batch can run here.` and gives a short reason (no agent-tool advice). Comment-only batches are skipped silently there.
  238. - When `MSSQL_SCRIPT_RUNNER` is not set, `run_script` is an unknown tool (no connection routing either).
  239. - On a read/write connection, a transaction the script leaves open is rolled back at the end with a `warning`: each run uses a new session, so `COMMIT` in the same run.
  240. ### DDL history (extension only)
  241. `ddl_history` backs the extension's per-connection DDL-history diff. Like `run_script`, it is **not** one of the 23 agent tools: it is listed and routed only when `MSSQL_SCRIPT_RUNNER=true`, and is otherwise an unknown tool. It reads `dbo.DDL_AuditLog`, which the `DDL_Audit` database trigger fills with one row per DDL statement (the same table and trigger that the AI Insights layer uses).
  242. - Arguments: `action` (required: `status`, `install`, `list` or `get`, case-insensitive), `schema`, `name`, `id`, `top` (default 100, clamped to 1..500), `connection`.
  243. - `status` returns `data: { tableExists, tableCompatible, triggerExists, triggerEnabled, canInstall, serverName, databaseName }`, plus `warnings` when an existing table is compatible but lossy, or when an existing `DDL_Audit_Writer` must not be used (the same principal check as `install`, without failing; `canInstall` is then false), and `loggingSuppressed: true` when `dbo.DDL_AuditLog` has DML triggers (the installed trigger then records nothing). `serverName` and `databaseName` are the server's own `@@SERVERNAME` and `DB_NAME()`, so the extension's consent modal names the database the connection actually reached. The trigger is the database-level trigger (`sys.triggers`, `parent_class = 0`) named `DDL_Audit`. `canInstall` is true when the connection is read/write, the table or the trigger is missing, and an existing table is compatible.
  244. - `tableCompatible` is true when the table exists and the trigger can write to it. `max_length` is read in bytes, so nvarchar counts 2 bytes per character.
  245. - `HostName`, `LoginName`, `SchemaName`, `ObjectName`, `ObjectType` and `ProgramName` are `varchar` or `nvarchar` of at least 100 characters (or `max`), and `EventType` of at least 64.
  246. - `CommandText` is `nvarchar(max)` or `varchar(max)`. `varchar(max)` is compatible, with `warnings: ["CommandText is varchar(max); non-Latin text in DDL will be stored lossy."]`.
  247. - `CommandXML` is `xml`. A character `CommandXML` is refused: SQL Server has no implicit xml-to-character conversion, so `CREATE TRIGGER` itself fails on such a table with error 257.
  248. - None of those nine columns is computed or an identity.
  249. - `ID` exists with an integer type, and `PostTime` with a date/time type.
  250. - Every other column the trigger leaves out is an identity, has a default, is nullable, computed or `rowversion`.
  251. Otherwise install creates nothing, and the error lists each problem, for example `dbo.DDL_AuditLog exists but the DDL_Audit trigger cannot write to it: ObjectName varchar(10), needs at least 100 characters; HostName missing. Nothing was created.`
  252. - `install` creates `dbo.DDL_AuditLog` if it is missing, then the `DDL_Audit` trigger if it is missing (from the same embedded script as `install_insights_layer`; SQL Server 2008 R2+). The batches run one by one, with no transaction around them. **It never alters, drops or enables an existing table or trigger**, which may belong to another team. Returns `data: { createdTable, createdTrigger, triggerEnabled }`. When an existing `DDL_Audit` is disabled, it stays disabled: `triggerEnabled` is false and `data.warning` is `DDL_Audit exists but is disabled; it was left unchanged.` When the table exists but is not compatible, install creates nothing (see above). If the trigger step fails, the error starts with `Creating the DDL_Audit trigger failed:`, and the user and the table this call created are dropped again (the message says whether that worked). Refused on read-only connections. A table this call creates also gets the index `IX_DDL_AuditLog_ObjectName` on `(ObjectName, ID) INCLUDE (SchemaName, LoginName, PostTime)`, for `list` and the trigger's own lookup. An existing table is never given an index.
  253. - **The `DDL_Audit_Writer` user.** The trigger runs `WITH EXECUTE AS 'DDL_Audit_Writer'`, a loginless database user that the install creates when it is missing (`CREATE USER [DDL_Audit_Writer] WITHOUT LOGIN WITH DEFAULT_SCHEMA = dbo`) and grants `INSERT, SELECT ON dbo.DDL_AuditLog`, nothing else. It never runs as `dbo`: an orphaned `dbo` (a database restored after its owner login was dropped) would make `EXECUTE AS 'dbo'` fail inside every DDL statement (15517), and code that runs inside the audit insert never gets more than those two permissions.
  254. - If a principal named `DDL_Audit_Writer` already exists and is not a loginless SQL user (`type = 'S'`; on 2012+ also `authentication_type = 0`; on every version its SID must not belong to a login), install creates nothing and fails with `A database principal named DDL_Audit_Writer already exists and is not a loginless SQL user (WITHOUT LOGIN); nothing was created. Rename or drop it, or ask a DBA.` An existing loginless `DDL_Audit_Writer` is reused (and granted again) only when it has no more rights than the trigger needs: no role membership (besides `public`), no owned schema, and no granted or grant-with-grant permission other than `CONNECT` on the database and `INSERT` / `SELECT` on `dbo.DDL_AuditLog`. Otherwise install creates nothing, alters or revokes nothing on it, and fails with `A user named DDL_Audit_Writer already exists with more rights than INSERT/SELECT on dbo.DDL_AuditLog; nothing was created. Remove its extra rights or drop it, then retry.`
  255. - **Pre-flight:** after creating the user and the grant, install runs `EXECUTE AS USER = N'DDL_Audit_Writer'; SELECT 1; REVERT;` before it creates the trigger. If that fails, the user and the table this call created are dropped again, and the error is returned.
  256. - `install_insights_layer` uses the same routine (user, grant, pre-flight, trigger script).
  257. - **Permission.** Install needs `db_owner`, or `ALTER ANY USER`, permission to grant on `dbo.DDL_AuditLog`, `IMPERSONATE` on the new user and `ALTER ANY DATABASE DDL TRIGGER`. A `db_ddladmin`-only account gets `Creating the DDL_Audit trigger failed: ...` and nothing is left behind.
  258. - **The installed trigger does not block other users' DDL.** `install_insights_layer` uses the same hardened script. Existing triggers are never altered, so a trigger installed by an earlier version keeps its old behaviour.
  259. - It runs as `DDL_Audit_Writer`, so callers need no permission on `dbo.DDL_AuditLog`; a `db_ddladmin`-only deployment account works. It records `ORIGINAL_LOGIN()`, the real login, and the "last modified" message compares that login too. A `DENY` on the table to `public` also applies to `DDL_Audit_Writer`: the DDL still runs, unlogged.
  260. - When `dbo.DDL_AuditLog` has any DML trigger, the insert and the lookup are skipped and the session gets `DDL_Audit: this change was not logged: DDL_AuditLog has triggers.` Such a trigger could otherwise roll the DDL back or run code inside it.
  261. - It sets `ANSI_WARNINGS`, `ANSI_PADDING`, `CONCAT_NULL_YIELDS_NULL` and `ARITHABORT` ON and `NUMERIC_ROUNDABORT` OFF itself, so sessions with non-ANSI options (old ODBC or MS Access clients) do not fail with error 1934. `ANSI_NULLS` and `QUOTED_IDENTIFIER` cannot be changed inside a module, so the script sets them ON before `CREATE TRIGGER`.
  262. - The audit `INSERT` and the "last modified" `PRINT` run inside `TRY ... CATCH`, with `XACT_ABORT OFF`. If they fail (a truncation, a constraint), the DDL still commits, unlogged, and the session gets `DDL_Audit: this change was not logged (error N).` `XACT_ABORT OFF` is needed because triggers start with `XACT_ABORT ON`, under which even a caught error dooms the transaction.
  263. - **Limit:** an error that dooms the transaction by itself still rolls the DDL back, because SQL Server allows nothing else once `XACT_STATE()` is -1: for example severity 20+ errors and deadlocks, or a DML trigger on `dbo.DDL_AuditLog` that appears between the skip check and the insert. Verified on LocalDB that, without the skip, an error inside such a trigger dooms the DDL (3616) and a `ROLLBACK` there ends it (3609).
  264. - Every DDL statement may print a `last modified` line that names other logins who changed the object in the last month.
  265. - **Rollback.** `DROP TRIGGER [DDL_Audit] ON DATABASE; DROP USER [DDL_Audit_Writer];` stops all logging at once. Optionally, after backing up any rows you want to keep, run `DROP TABLE dbo.DDL_AuditLog;`, which also drops `IX_DDL_AuditLog_ObjectName`. None of these affects any other object.
  266. - IDs are `bigint`-safe end to end (`get` takes a 64-bit `id`; an existing table with a `bigint` `ID` works).
  267. - `list` (needs `name`; `schema` is optional) returns the newest `top` entries for that object, newest first: `data: [{ id, postTime, loginName, hostName, programName, eventType, objectType, schemaName, length }]`. `postTime` is ISO 8601 server local time (`yyyy-MM-ddTHH:mm:ss.fff`); `length` is the length of the command text. When `schema` is given, entries with no schema are included too. `name` and `schema` are trimmed, then compared to their first 100 characters, as the trigger stores them.
  268. - `get` (needs `id`) returns one entry with its full command text: `data: { id, postTime, loginName, eventType, objectType, schemaName, objectName, commandText }`. An unknown id is an error.
  269. - `list` and `get` fail with `DDL history is not installed on this database (dbo.DDL_AuditLog is missing).` when the table is missing.
  270. - On read-only connections `status`, `list` and `get` are plain `SELECT`s and write nothing.
  271. ### IntelliSense (extension only)
  272. `language_service` gives the extension's query windows SSMS-style IntelliSense. Like `run_script`, it is **not** one of the 23 agent tools: it is listed and routed only when `MSSQL_SCRIPT_RUNNER=true` (26 tools then), and is otherwise an unknown tool. It is built on Microsoft's SqlParser (`Microsoft.SqlServer.Management.SqlParser`, the engine behind SSMS and Azure Data Studio IntelliSense) with an SMO metadata provider, through a small completion wrapper ported from [microsoft/sqltoolsservice](https://github.com/microsoft/sqltoolsservice) (MIT; each ported file under `MssqlMcp/LanguageService/` names its source).
  273. - Arguments: `action` (required: `completion`, `hover`, `signatureHelp`, `warm` or `refresh`, case-insensitive), `text` (the document), `line` and `column` (1-based caret position), `connection`.
  274. - `completion` returns `data: { items: [{ label, kind, detail, insertText, sortText }], isIncomplete, cacheState }`. `kind` is one of `table`, `view`, `column`, `procedure`, `function`, `keyword`, `schema`, `parameter`, `variable`, `database`, `type`, `snippet`, `other`. It covers tables after `FROM` / `JOIN`, `alias.` columns, schema-qualified names, CTE and derived-table columns, variables, built-in functions, and keywords where the parser has nothing else (no list inside comments).
  275. - **Procedure parameters.** SqlParser offers a procedure's parameters only as signature help, so after `EXEC proc ` / `EXECUTE proc ` the server adds them from `Resolver.FindMethods`: `label` `@x`, `detail` the type (with `OUTPUT` where it applies), `insertText` `@x = `, sorted ahead of the `@@` globals. Parameters already given by name (`@x = 1`), and leading positional arguments, are not offered again. Inside a value (`@x = |`) none are offered. This part is our code, not sqltoolsservice's.
  276. - `hover` returns `data: { contents, range? }`: `contents` is plain text (for example `column a(int, null)`), so render it as plain text or a code block. `range` is 1-based and end-exclusive. `data` is null when there is nothing to show.
  277. - `signatureHelp` returns `data: { signatures: [{ label, documentation?, parameters: [{ label, documentation? }] }], activeSignature, activeParameter }` for built-in functions (`DATEADD(`) and user procedures (`EXEC dbo.p 1, `). `activeParameter` is 0-based, -1 when the caret is on none. `data` is null outside a call.
  278. - `warm` starts building the metadata cache for the connection and returns at once: `data: { cacheState }`. `refresh` drops the cache for the connection and database, then warms it again (use it after DDL in another session).
  279. - **Metadata cache.** One SMO metadata provider per (connection name, database, connection-string generation), built lazily in the background and shared by all documents. Its SqlConnection is unpooled and has `Persist Security Info=true`. That only matters for SQL-authentication passwords: SMO keeps the password if it copies the connection string. The string stays in process memory and is never logged. A build that fails with a transient SQL error (596, 10053, 10054, 64) is retried once at once; login, permission and database errors are not, and neither is 233, which the server also reports for a rejected login. A request whose connection was closed, removed or replaced after it was routed builds nothing. One lock per entry serialises SqlParser, which is not thread-safe. A request waits for binding at most 2 seconds. After that, `completion` returns the keyword list with `cacheState: "loading"` and `isIncomplete: true`, and the build carries on; `hover` and `signatureHelp` return null. Entries are dropped when the connection is closed or removed (or an ad-hoc connection replaced), on `refresh`, and after 30 minutes unused. A failed build is retried after 30 seconds.
  280. - **Database context.** The database is the profile's database (`Initial Catalog`; the login's default database when none is set). A leading `USE x` in the text is **not** followed for metadata.
  281. - **Cancellation.** A cancelled call (`notifications/cancelled`, as the extension sends when the user keeps typing) stops waiting at once, and work that has not started is skipped.
  282. - **Read-only and logging.** It only reads metadata (SMO catalog queries) and works on read-only profiles. Logs carry counts and timings, never the document text or object definitions. Documents over 2,000,000 characters are refused.
  283. - **Licence.** SqlParser is not open source: it ships under the *SQL Server Shared Management Objects (SMO) License Terms*, reproduced in [THIRD-PARTY-NOTICES.txt](THIRD-PARTY-NOTICES.txt) (SMO itself and SmoMetadataProvider are MIT). The owner approved this dependency. Whoever owns licensing for your distribution should still confirm the flow-down terms; this README is not legal advice.
  284. - **Limitations.**
  285. - SMO 181.37.1 and SmoMetadataProvider 181.37.1 are built against Microsoft.Data.SqlClient 6.1.3; this server runs them on SqlClient 7.0.2 (assembly unification to the higher version). Verified on LocalDB with Windows authentication and with a SQL login that has only `db_datareader` (which also sees no procedures, since catalog views hide them without `VIEW DEFINITION` or `EXECUTE`). **Entra ID (Azure AD) authentication is untested** with the language service.
  286. - SqlParser 180.9.0 throws inside `Resolver.FindMethods` for some scalar function calls; procedure parameter lookup runs only in an EXEC argument position and treats such an exception as "no parameters", and hover and signature help return nothing there (logged once per cache entry at Debug).
  287. - The first bind on a large database can take longer than 2 seconds; the extension sends `warm` when a query window is bound or a bound tab becomes active (at most once per connection every 5 minutes), and its **Refresh IntelliSense Cache** command (Ctrl+Shift+R) sends `refresh`. See the extension README, "IntelliSense".
  288. - Single-file publish grows by about 5.5 MB (44.9 MB to 50.5 MB).
  289. ## Multiple connections
  290. One server process can hold several named SQL Server connections. Nothing changes for existing single-connection setups.
  291. ### 1. Legacy: `CONNECTION_STRING`
  292. Set only `CONNECTION_STRING` and the server behaves as before: one connection (internally named `default`, which is only a label, not a "default connection"), the `connection` argument is optional, and startup fails fast (exit code `1`) if the database is unreachable.
  293. ### 2. `MSSQL_CONNECTIONS`
  294. A JSON array, one object per connection:
  295. | Property | Required | Description |
  296. |----------|----------|-------------|
  297. | `name` | Yes | 1-64 letters, digits, `-`, `_` or `.`. Unique, compared case-insensitively. |
  298. | `connectionString` | Yes | ADO.NET connection string. |
  299. | `readOnly` | No (`false`) | When `true`, the write tools listed in [Read-only profiles](#6-read-only-profiles) are refused on this connection. |
  300. | `insights` | No (`true`) | Enables the AI Insights layer for this connection. |
  301. ```json
  302. [
  303. { "name": "prod", "connectionString": "Server=prod-sql;Database=App;Trusted_Connection=True;TrustServerCertificate=True", "readOnly": true },
  304. { "name": "dev", "connectionString": "Server=DC\\DEV;Database=App;Trusted_Connection=True;TrustServerCertificate=True" }
  305. ]
  306. ```
  307. The config is strict: unknown properties are rejected, and so is a `"default"` property (there is no default connection, see the rule below). Invalid config makes the server exit with code `1`. `CONNECTION_STRING`, `MSSQL_CONNECTIONS` and `MSSQL_CONNECTIONS_FILE` can be combined; all names must be unique.
  308. ### 3. `MSSQL_CONNECTIONS_FILE`
  309. Path to a file containing the same JSON array. Connection strings may contain `${env:VAR}` placeholders, expanded at load time from the server's environment, so secrets stay out of the file. An unset variable is a startup error. Placeholders are expanded in `MSSQL_CONNECTIONS` as well.
  310. ```json
  311. [
  312. { "name": "crm", "connectionString": "Server=sql01;Database=Crm;User Id=mcp_reader;Password=${env:CRM_PASSWORD};TrustServerCertificate=True", "readOnly": true }
  313. ]
  314. ```
  315. **Quoting.** A placeholder can sit unquoted, inside double quotes or inside single quotes:
  316. ```text
  317. Password=${env:CRM_PASSWORD} (unquoted: value substituted raw)
  318. Password="${env:CRM_PASSWORD}" (quoted: any " in the value is doubled to "")
  319. Password='${env:CRM_PASSWORD}' (quoted: any ' in the value is doubled to '')
  320. ```
  321. Use a quoted form for passwords. An unquoted value is inserted as is, so a password containing `;`, `=`, `"` or leading/trailing spaces breaks the connection string. When the placeholder is enclosed in double (or single) quotes, the server doubles every `"` (or `'`) in the substituted value, which is the ADO.NET escape rule, so the value is read back exactly as stored.
  322. Inside a JSON string the quotes must be escaped: `"connectionString": "Server=sql01;Database=Crm;User Id=mcp_reader;Password=\"${env:CRM_PASSWORD}\""`.
  323. > **Unreleased:** quoted placeholders (`Password="${env:X}"` and `Password='${env:X}'`) are escaped as described above; unquoted placeholders behave as before.
  324. ### 4. The rule: when is `connection` required?
  325. - **Exactly one registered connection:** `connection` is optional.
  326. - **More than one registered connection:** `connection` is **mandatory** on every data tool. There is **no default connection**.
  327. "Registered" counts every connection: configured, legacy and ad-hoc, **open or closed**, so the rule does not flip when a connection is closed. Closing an ad-hoc connection forgets it and lowers the count; closing a configured one does not.
  328. The JSON schema keeps `connection` optional so single-connection clients see no change; the rule is enforced when the call is made. A missing or unknown name returns an error that lists every connection (name, server, database, read-only, open), so an agent can retry in one step. Agents are told the rule by the server instructions, each tool's `connection` description, and `connectionRequired` in `list_connections`.
  329. Startup in multi-connection mode: all targets are probed in parallel with a 5 s timeout, and unreachable targets are logged but do **not** stop the server (unlike legacy mode).
  330. ### 5. `MSSQL_ALLOW_ADHOC_CONNECTIONS`
  331. Off by default. When set to `true`, `open_connection` accepts a `name` plus a raw `connectionString` and registers it at runtime. Because this lets an agent point the server at arbitrary hosts (an SSRF-like capability), ad-hoc connections are **read-only by default**, have the Insights layer disabled, and are probed with a 5 s timeout before registration. A configured connection cannot be redefined ad hoc. Adding an ad-hoc connection to a single-connection server makes `connection` mandatory from then on.
  332. The connection string is parsed before anything is sent, and the operator controls what the agent may do:
  333. | Agent asks for | Default | Operator switch |
  334. |----------------|---------|-----------------|
  335. | `Integrated Security` / `Trusted_Connection` / `Authentication=Active Directory*` | refused | `MSSQL_ADHOC_ALLOW_INTEGRATED_AUTH=true` |
  336. | `readOnly=false` | refused | `MSSQL_ADHOC_ALLOW_WRITE=true` |
  337. | Any host | allowed | `MSSQL_ADHOC_ALLOWED_HOSTS=host1,host2` limits it |
  338. | `AttachDBFilename`, `User Instance`, `Failover Partner` | always refused | none |
  339. An unparsable string is refused without echoing it. When the connection test fails, the agent only gets `Connection test failed for '<name>'.` (so the tool cannot be used to probe the network); the detail is written to the server log with the password masked. Re-opening an ad-hoc name with a new connection string replaces the entry and clears the old entry's connection pool.
  340. ### 6. Read-only profiles
  341. A connection with `"readOnly": true` refuses these tools with an error:
  342. `execute_sql`, `insert_data`, `update_data`, `create_table`, `drop_table`, `upsert_insight`, `install_insights_layer`, `refresh_insights`, `rebuild_baseline_insights`.
  343. Inspection tools, `read_data`, `get_insight`, `list_insights`, `get_insight_history` and `insights_check` still work. A read-only connection makes **no writes at all**, including to the `AIInsights` tables: inspection tools still return cached insights, but on a read-only connection they never create a baseline, never archive a stale insight (it is reported as `StaleArchived` and left in place for a writable connection to archive), and background DDL processing is skipped. Read-only is enforced by this server, not by SQL Server: also use a least-privilege database login for connections that must never write.
  344. ### 7. Full `mcp.json` example
  345. Two connections, one read-only, with the password kept out of the config through `MSSQL_CONNECTIONS_FILE` and `${env:...}`:
  346. ```json
  347. {
  348. "mcpServers": {
  349. "MSSQL-MCP": {
  350. "type": "stdio",
  351. "command": "C:\\Development\\MCPs\\MS-SQL\\MssqlMcp\\bin\\Release\\net10.0\\win-x64\\MssqlMcp.exe",
  352. "env": {
  353. "MSSQL_CONNECTIONS_FILE": "C:\\Secrets\\mssql-connections.json",
  354. "CRM_PASSWORD": "<set in your secret store>",
  355. "USE_INSIGHTS_LAYER": "true",
  356. "LOG_FILE_PATH": "C:\\Logs\\mssql-mcp.log"
  357. }
  358. }
  359. }
  360. }
  361. ```
  362. with `C:\Secrets\mssql-connections.json`:
  363. ```json
  364. [
  365. { "name": "crm-prod", "connectionString": "Server=prod-sql;Database=Crm;User Id=mcp_reader;Password=${env:CRM_PASSWORD};TrustServerCertificate=True", "readOnly": true },
  366. { "name": "crm-dev", "connectionString": "Server=DC\\DEV;Database=Crm;Trusted_Connection=True;TrustServerCertificate=True" }
  367. ]
  368. ```
  369. Inline `MSSQL_CONNECTIONS` works too (see `sample_mcp.json`), but nesting JSON inside a JSON string needs careful escaping; prefer the file for anything beyond a demo. No tool ever returns a connection string, and the startup log masks passwords.
  370. ### 8. Connection manager (Claude Desktop, MCP Apps)
  371. With `MSSQL_MANAGED_CONNECTIONS_FILE` set, the server offers a form in the chat where the user adds, edits, tests and removes connections. It is an [MCP Apps](https://github.com/modelcontextprotocol/ext-apps) view (`ui://apoint-ms-sql/connections`), so it shows up in hosts that support MCP Apps, such as Claude Desktop. The VS Code extension's **Register clients** command sets the variable for Claude Desktop only, to `%APPDATA%\APoint-ms-sql\connections.json`.
  372. Ask Claude to "manage connections". It calls `manage_connections`, the only one of these tools the model can see, and the form opens. The form uses six app-only tools (`_meta.ui.visibility: ["app"]`) that the host hides from the model: `connections_ui_list`, `connections_ui_save`, `connections_ui_remove`, `connections_ui_test`, `connections_ui_list_databases` and `connections_ui_bring_online`. The model therefore cannot add hosts, switch a connection to read-write, see a password or bring a database online: only the person at the form can. Every request is validated again on the server. As a backstop for clients that do not support MCP Apps (and so would show app-only tools to their model), the server lists and runs the `connections_ui_*` tools only for a client that advertises the MCP Apps extension (`io.modelcontextprotocol/ui`, under `extensions` or `experimental`) at initialize; other clients see only `manage_connections`, and a call is refused (logged at Warning). `MSSQL_APPS_REQUIRE_UI_CAPABILITY=false` turns this check off for a host that supports MCP Apps without advertising it.
  373. | | |
  374. |---|---|
  375. | **Fields** | The same as the VS Code form: name, authentication (Windows, SQL login, Entra interactive, Entra default, raw connection string without a password), server, database, user, password, encryption, trust server certificate, read-only (default on), AI insights. |
  376. | **Passwords** | SQL-login passwords are stored as `passwordProtected`, encrypted with Windows DPAPI for the current Windows user. They are never written in clear text and never returned. An entry another Windows account saved is listed with an error until the password is entered again. |
  377. | **Applies** | At once, with no restart: the server updates its connection list. Other server processes that share the file (another Claude window) reload it on their next tool call. |
  378. | **Databases** | **List databases** connects through the typed database (or `master` when it is empty or cannot be opened, so contained-database users can list) and lists every database; one that is not online is labelled, e.g. `Sales (offline)`. |
  379. | **Offline database** | When **Test connection** fails because the database is OFFLINE, or **Save** of a new connection finds it OFFLINE, the form offers **Bring online** (Save also offers **Save anyway**). It asks for a second click to confirm, then runs `ALTER DATABASE [db] SET ONLINE` through `master` (300 s timeout). Only `OFFLINE` qualifies; `RESTORING`, `SUSPECT`, `EMERGENCY` and other states are shown but never changed. It is allowed on read-only connections, because the read-only flag limits the model, not the person at the form. The login needs ALTER permission on the database (for example `dbcreator`). Undo, if the database was offline on purpose: `ALTER DATABASE [db] SET OFFLINE WITH ROLLBACK IMMEDIATE` (disconnects its sessions). |
  380. | **Other connections** | Connections from `MSSQL_CONNECTIONS_FILE` (the extension's file), `MSSQL_CONNECTIONS` or `CONNECTION_STRING` are listed read-only. A managed name that collides with one of them is reported, not registered. |
  381. | **File safety** | Changes are written under a cross-process lock, atomically, with the previous version kept as `<file>.bak`. A file that cannot be parsed is reported in the form and never overwritten; connections already loaded keep working. |
  382. The file is a JSON object, which the form is meant to edit:
  383. ```json
  384. {
  385. "version": 1,
  386. "connections": [
  387. { "name": "crm-dev", "auth": "windows", "server": "DC\\DEV", "database": "Crm", "encrypt": "optional", "trustServerCertificate": true, "readOnly": true, "insights": true },
  388. { "name": "crm-prod", "auth": "sql", "server": "prod-sql", "database": "Crm", "user": "mcp_reader", "passwordProtected": "AQAAANCMnd8B...", "encrypt": "mandatory", "trustServerCertificate": false, "readOnly": true, "insights": true }
  389. ]
  390. }
  391. ```
  392. Rollback: remove `MSSQL_MANAGED_CONNECTIONS_FILE` from the client's config and restart the client. The file is then ignored; delete it if it is no longer needed.
  393. The [Claude Desktop extension (.mcpb)](#option-2-claude-desktop-extension-mcpb) turns this on by default.
  394. ## AI Insights layer
  395. The AI Insights layer caches LLM-authored (or server-generated baseline) summaries of database objects so repeated investigations cost fewer tokens. It is **enabled by default** but **not auto-installed** — call **install_insights_layer** once per database.
  396. ### What gets installed
  397. - **`AIInsights` schema** — `SchemaInsights`, `InsightHistory`, `DdlChangeWatermark`, and related objects (embedded SQL in `InsightsLayer/SqlScripts/`).
  398. - **`dbo.DDL_AuditLog`** — captures DDL events via a database-level trigger.
  399. - **`DDL_Audit` trigger** — runs `WITH EXECUTE AS 'DDL_Audit_Writer'`, a loginless user the install creates with only `INSERT, SELECT ON dbo.DDL_AuditLog`, so installing it requires `db_owner` (or `ALTER ANY USER`, grant rights on the table, `IMPERSONATE` on the new user and `ALTER ANY DATABASE DDL TRIGGER`).
  400. - The trigger is **database-wide**: it fires for DDL from every application, not just this server. It records `ORIGINAL_LOGIN()`, callers need no permission on `dbo.DDL_AuditLog`, and a failed audit insert does not roll back the caller's DDL. See [DDL history (extension only)](#ddl-history-extension-only) for the details and the limits. `install_insights_layer` is still marked destructive, because it adds a database-wide trigger and a user. Remove them with `DROP TRIGGER [DDL_Audit] ON DATABASE; DROP USER [DDL_Audit_Writer];`.
  401. - An existing `DDL_Audit` trigger is never replaced, so databases installed by an earlier version keep their original trigger definition (which runs as the caller: a login without `INSERT` on `dbo.DDL_AuditLog` has its DDL rolled back). To move such a database to the hardened trigger, drop the old trigger and install again. Legacy `AIInsights` tables from older schema versions are dropped only when empty.
  402. Install is idempotent; re-running is safe.
  403. ### How it works
  404. ```mermaid
  405. flowchart LR
  406. subgraph introspect [Introspection]
  407. DT[describe_table / describe_view / get_object]
  408. end
  409. subgraph cache [AIInsights]
  410. SI[SchemaInsights]
  411. IH[InsightHistory]
  412. end
  413. subgraph writes [Write tools]
  414. W[create_table / drop_table / insert_data / update_data / execute_sql]
  415. end
  416. DT -->|attach insight| SI
  417. W -->|queue DDL processing| SI
  418. SI -->|stale / DDL change| IH
  419. ```
  420. 1. **Read path** — `describe_table`, `describe_view`, and `get_object` attach `insight` (compact: the authored text, or a few facts for a baseline), `insightFreshness`, `enrichmentSuggested` and, only when an update is needed, `insightEnrichment` (best-effort; never fails the parent tool).
  421. 2. **Freshness** — On read, the service compares the cached row’s schema fingerprint (columns for tables, definition for modules) to the live object. A mismatch, or a dropped object, archives the row to `InsightHistory` and returns `insightFreshness: StaleArchived`. `modify_date` alone (statistics, index rebuild, constraints) does not.
  422. 3. **Auto-population** — When `INSIGHTS_AUTOPOPULATE` is enabled (default), an absent or stale insight is replaced by the newest authored insight in `InsightHistory` with the same fingerprint (drop/re-create keeps its insight), otherwise by a mechanical baseline (`LlmModel = "auto-mechanical"`, `Confidence = 0.30`) built from `sys.*` metadata.
  423. 4. **Enrichment rule** — The agent is asked to call `upsert_insight` only in the cases below. Otherwise `enrichmentSuggested` is `false`, which means the cached insight is current. The rule is stated once in the server instructions, not in every tool description. See [.cursor/skills/mssql-insights-ops/SKILL.md](.cursor/skills/mssql-insights-ops/SKILL.md) for the agent workflow.
  424. 5. **Write path** — After successful writes, a background `InsightDdlProcessingQueue` drains DDL audit rows (or falls back to fingerprint scans). A DDL event archives an insight only when the object is gone or its fingerprint changed; other DDL (constraints, permissions, `ENABLE TRIGGER`) is consumed and the insight kept. When auto-population is on, archived objects are restored or re-baselined.
  425. | `insightEnrichment.trigger` | When | What the agent gets |
  426. |---|---|---|
  427. | `InitialBaselineOnly` | Only a baseline exists and no authored insight was ever written for the object. | Placeholders in `nextAction.args`. |
  428. | `StructureChanged` | A baseline replaced an authored insight because the structure changed. | `previousInsight` (pre-filled into `nextAction.args`) and up to 5 `structuralEvents` from `dbo.DDL_AuditLog` (event type, time, command text cut to 300 characters; no login or host). |
  429. | `DataPopulated` | The authored insight was written while a table or view held fewer than 100 rows, and it now holds 100 or more. | `previousInsight`, `rowsNow`. Insights written at 100 rows or more are never re-requested for data growth. |
  430. `AIInsights.SchemaInsights.RowCountAtAnalysis` (and the same column in `InsightHistory`) stores the row count at write time, and a restored insight keeps its original count and `LastAnalyzed`. The count is null for other object types and in these cases:
  431. - **Tables:** the count is approximate, read from `sys.dm_db_partition_stats`. That needs VIEW DATABASE STATE (VIEW DATABASE PERFORMANCE STATE on SQL Server 2022+). Without it the count is null and `DataPopulated` never fires.
  432. - **Views:** counting runs the view, capped at 101 rows, with a 2-second command timeout and a 1-second lock timeout. Baselines never count views. An authored view insight is re-checked at most once every 10 minutes per object.
  433. - **Read-only connections:** they never get `insightEnrichment`, because they refuse `upsert_insight`.
  434. - **Older installs:** databases installed before this column existed keep working without it, but with no `DataPopulated` trigger. Running `install_insights_layer` again adds the column, and the server picks it up within a minute.
  435. ### Recommended first-time workflow
  436. ```
  437. 1. get_server_info
  438. 2. insights_check
  439. 3. install_insights_layer (if schema or DDL trigger missing)
  440. 4. list_objects(objectType=Table)
  441. 5. describe_table(name=…) (inspect insight / enrichmentSuggested)
  442. 6. upsert_insight(…) (only when insightEnrichment is present)
  443. ```
  444. ### `insightFreshness` values
  445. | Value | Meaning |
  446. |-------|---------|
  447. | `Fresh` | Cached insight matches live object definition. |
  448. | `Absent` | No row in `SchemaInsights` (baseline may be created on next introspection if auto-pop is on). |
  449. | `StaleArchived` | Row was archived because the object was dropped or its structure changed. On a read-only connection the row is reported stale but not archived. |
  450. | `LayerDisabled` | `USE_INSIGHTS_LAYER=false`. |
  451. | `AccessDenied` | Could not read live definition (permissions). |
  452. | `DefinitionUnavailable` | Object exists but definition could not be resolved. |
  453. ## Response shape
  454. All tools return `DbOperationResult`:
  455. ```json
  456. {
  457. "success": true,
  458. "error": null,
  459. "rowsAffected": 0,
  460. "data": { }
  461. }
  462. ```
  463. - `success` — whether the operation completed without error.
  464. - `error` — message when `success` is false.
  465. - `rowsAffected` — for DML tools (`insert_data`, `update_data`, `execute_sql`, …).
  466. - `data` — tool-specific payload (object metadata, row arrays, insight status, …).
  467. Introspection tools may add top-level keys inside `data` for insights (`insight`, `insightFreshness`, `enrichmentSuggested`, `insightEnrichment`).
  468. ## Build, publish, and test
  469. ### Build
  470. ```powershell
  471. dotnet build MssqlMcp.sln
  472. dotnet build MssqlMcp.sln -c Release
  473. ```
  474. ### Test
  475. ```powershell
  476. dotnet test MssqlMcp.sln -c Release
  477. ```
  478. | Test project area | Coverage |
  479. |-------------------|----------|
  480. | `SqlStatementClassifierTests` | Read vs execute SQL routing |
  481. | `TriggerQualifiedNameTests` | `schema.table.trigger` name parsing |
  482. | `InsightsLayerEnvironmentTests` | Env var opt-out semantics |
  483. | `InsightsLayerNoOpTests` | Disabled-layer behavior |
  484. | `SqlBatchSplitterTests` | Embedded SQL script splitting |
  485. | `GetServerInfoTests` | Server info response shape |
  486. | `UnitTests` | General helpers |
  487. | `InsightsLayerDbSmokeTests` | Live DB integration (**skipped** unless `RUN_INSIGHTS_DB_TEST=1`) |
  488. For integration tests, set `CONNECTION_STRING` to a real database. The test project falls back to `(localdb)\MSSQLLocalDB` when unset.
  489. ```powershell
  490. $env:RUN_INSIGHTS_DB_TEST = "1"
  491. $env:CONNECTION_STRING = "Server=.;Database=TestDb;Trusted_Connection=True;TrustServerCertificate=True"
  492. dotnet test MssqlMcp.sln -c Release
  493. ```
  494. ### Publish single-file executable
  495. ```powershell
  496. .\publish-release.ps1
  497. # Optional: .\publish-release.ps1 --verbosity normal
  498. ```
  499. Output path is configured in `MssqlMcp/MssqlMcp.csproj` and `Properties/PublishProfiles/ReleaseSingleFile.pubxml` (default: `C:\Development\MCPs\MS-SQL-Release\MssqlMcp.exe`).
  500. The script also packs the same exe as:
  501. - the Claude Desktop extension, `<release folder>\ClaudeDesktop\APoint-ms-sql.mcpb`;
  502. - the VS Code / Cursor extension, `<release folder>\extension\APoint-ms-sql.vsix` (install it with `install-APoint-ms-sql.ps1` in that folder). The same file is left as `vscode-extension\ms-sql-mcp-win32-x64-<version>.vsix`, and older `.vsix` files there are removed. It is packaged locally the way the publish workflow does it: same content as the Marketplace build of that commit, but not byte-identical.
  503. Each previous bundle is kept as `<name>_yyyyMMdd_HHmm.<ext>`, as the script does for the exe. `-SkipClaudeDesktop` skips the `.mcpb` and `-SkipExtension` skips the `.vsix`. `-DryRun` builds and packs everything and shows what it would copy.
  504. ### Versioning
  505. The server `<Version>` in `MssqlMcp/MssqlMcp.csproj` and `version` in `vscode-extension/package.json` (and its `package-lock.json`) must be equal. `npm run check:version` in `vscode-extension` and CI enforce this. The `.mcpb` takes its version from the csproj.
  506. ### Release checklist
  507. Every release does all five steps, in order:
  508. 1. **Merge the change PRs** into `main` (tests green locally: `.\publish-release.ps1 -Test -DryRun`).
  509. 2. **Bump the version** in a `chore/release-<version>` PR (commit `chore(release): version <version>`): `MssqlMcp/MssqlMcp.csproj` `<Version>`, `vscode-extension/package.json` and `package-lock.json` (`npm version <version> --no-git-tag-version`), and a `## <version>` entry in both `CHANGELOG.md` and `vscode-extension/CHANGELOG.md`. Run `npm run check:version`, then merge the PR.
  510. 3. **Check `main` carries the new version** before publishing (the release is built from local `main`, which must equal `origin/main`):
  511. ```powershell
  512. git checkout main; git pull
  513. git show origin/main:vscode-extension/package.json | Select-String '"version"'
  514. ```
  515. 4. **Dry run**, then **release**. Both build, test and pack locally (nothing builds on GitHub):
  516. ```powershell
  517. .\publish-release.ps1 -PublishPublic -DryRun
  518. .\publish-release.ps1 -PublishPublic
  519. ```
  520. The second run commits the docs, the skill and the Claude Code plugin version to the public repo `A-Point-Systems-ltd/ms-sql-mcp` (clone at `C:\Development\MCPs\MS-SQL-Public`), creates its release `ext-v<version>` with `MssqlMcp.exe`, the `.mcpb`, the VSIX and `SHA256SUMS.txt`, and then updates the local release folder.
  521. 5. **Wait for the marketplaces**: the release starts the public repo's **publish-marketplaces** workflow (VS Code Marketplace and Open VSX, VSIX taken from the release and checked against `SHA256SUMS.txt`). Check it with `gh run list --repo A-Point-Systems-ltd/ms-sql-mcp`. Restart MCP clients that use the local exe (Cursor, Claude Desktop, Claude Code). The local release folder (`C:\Development\MCPs\MS-SQL-Release`) keeps the previous exe as `MssqlMcp_yyyyMMdd_HHmm.exe` and the previous bundles with the same suffix. Rollback is described at the top of `publish-release.ps1` and `packaging\public\publish-public.ps1`.
  522. ### Package the VS Code extension (VSIX)
  523. `publish-release.ps1` builds and releases both packages on this machine:
  524. - publishes the exe and stages it into `vscode-extension/bin`;
  525. - runs the tests (`-Test`, implied by `-PublishPublic`) and packages `ms-sql-mcp-win32-x64-<version>.vsix`;
  526. - packs the same exe as `apoint-ms-sql-<version>.mcpb` (`build-mcpb.ps1 -ExePath`);
  527. - with `-PublishPublic` (and without `-DryRun`): creates the release `ext-v<version>` in the public repo, whose **publish-marketplaces** workflow publishes the VSIX to the Visual Studio Marketplace and Open VSX.
  528. To build a VSIX locally:
  529. ```powershell
  530. dotnet publish MssqlMcp\MssqlMcp.csproj -c Release -p:PublishProfile=ReleaseSingleFile -o $env:TEMP\mssqlmcp-exe
  531. cd vscode-extension
  532. npm ci
  533. node scripts\stage-exe.mjs $env:TEMP\mssqlmcp-exe\MssqlMcp.exe
  534. npm run package
  535. ```
  536. Before running `dotnet publish`, rebuild the embedded connections view if `apps/connections-ui` changed:
  537. ```powershell
  538. cd apps\connections-ui
  539. npm ci
  540. npm run build
  541. ```
  542. ### Package the Claude Desktop extension (.mcpb)
  543. ```powershell
  544. .\packaging\mcpb\build-mcpb.ps1
  545. # Optional: -OutDir D:\out
  546. # Optional: -ExePath <published single-file MssqlMcp.exe> to pack that exe instead of building one
  547. ```
  548. The script:
  549. 1. Rebuilds the connections view and publishes the self-contained exe.
  550. 2. Smoke-starts the exe (it must answer MCP `initialize`).
  551. 3. Stamps `packaging/mcpb/manifest.json` with the csproj version.
  552. 4. Validates and packs the bundle with `@anthropic-ai/mcpb` (via `npx`).
  553. The output is `Publish\mcpb\apoint-ms-sql-<version>.mcpb`, and the script prints its SHA-256.
  554. The bundle:
  555. - starts with no connections and enables the [connection manager](#8-connection-manager-claude-desktop-mcp-apps) on `%APPDATA%\APoint-ms-sql\connections.json`;
  556. - keeps ad hoc connections and the extension-only tools off;
  557. - maps its **AI Insights layer** setting to `USE_INSIGHTS_LAYER`.
  558. ## Project layout
  559. ```
  560. MS-SQL/
  561. ├── MssqlMcp/ # MCP server (.NET 10 exe)
  562. │ ├── Program.cs # Startup, logging, DI, stdio transport
  563. │ ├── SqlConnectionFactory.cs # current connection profile → SqlConnection
  564. │ ├── Connections/ # Registry, config loader, routing filter, masker
  565. │ ├── SqlStatementClassifier.cs
  566. │ ├── TriggerQualifiedName.cs
  567. │ ├── DbOperationResult.cs
  568. │ ├── LanguageService/ # language_service: SqlParser IntelliSense, metadata cache
  569. │ ├── InsightsLayer/ # AI Insights service + embedded SQL
  570. │ │ ├── InsightsLayerService.cs
  571. │ │ ├── InsightDdlProcessingQueue.cs
  572. │ │ └── SqlScripts/
  573. │ └── Tools/ # MCP tools (partial class Tools)
  574. ├── MssqlMcp.Tests/ # xUnit tests
  575. ├── sample_mcp.json # Example MCP config
  576. ├── publish-release.ps1
  577. └── .cursor/skills/mssql-insights-ops/SKILL.md # Agent playbook for insights
  578. ```
  579. ## Security notes
  580. - **Connection strings** live in MCP client config / environment variables — never commit real credentials to the repo.
  581. - **execute_sql**, **drop_table**, and **update_data** are marked destructive in MCP metadata; agents should confirm intent with the user.
  582. - **read_data** and **execute_sql** do not support parameterized queries — literals are embedded in SQL. Do not pass untrusted user input through these tools without sanitization.
  583. - **DDL audit trigger** installation requires elevated database permissions; use a dedicated database role in production.
  584. - Startup logs mask password fields in connection strings; other secrets in the connection string are still logged — prefer Windows / Entra authentication where possible.
  585. ## Troubleshooting
  586. ### `MCP error -32000: Connection closed`
  587. The server exited before or during MCP handshake. Check the log file:
  588. | Config | Location |
  589. |--------|----------|
  590. | `LOG_FILE_PATH` set to file | That file |
  591. | `LOG_FILE_PATH` set to directory | New `mssql-mcp-*.log` inside it |
  592. | Default (Windows) | `%LOCALAPPDATA%\MssqlMcp\Logs\mssql-mcp-*.log` |
  593. | Default (Linux/macOS) | `~/.local/share/MssqlMcp/Logs/mssql-mcp-*.log` |
  594. Logs include process info, masked connection string, SQL connection test results, and stack traces.
  595. ### Common failures
  596. | Symptom | Likely cause | Fix |
  597. |---------|--------------|-----|
  598. | Immediate exit code 1 | No connection configured, invalid `MSSQL_CONNECTIONS` JSON, or (legacy mode) unreachable database | Set `CONNECTION_STRING` or `MSSQL_CONNECTIONS`; check the log file |
  599. | Error that `connection` is required | More than one connection is registered | Call `list_connections` and pass one of the names as `connection` |
  600. | Ad-hoc connections are disabled | `MSSQL_ALLOW_ADHOC_CONNECTIONS` is not `true` | Set it in the MCP `env`, or add the connection to `MSSQL_CONNECTIONS` |
  601. | Connection test failed | Wrong server/database/auth | Verify string outside MCP (`sqlcmd`, SSMS) |
  602. | install_insights_layer / ddl_history install permission error | The install creates the user `DDL_Audit_Writer`, grants it on `dbo.DDL_AuditLog` and creates a database trigger that runs as it | Install as `db_owner` |
  603. | execute_sql rejects SELECT | By design | Use **read_data** for all queries that return rows |
  604. | Insight tools return "layer is disabled" | `USE_INSIGHTS_LAYER=false` | Remove or set to `true`; restart server |
  605. | Hardware fields null in get_server_info | Missing `VIEW SERVER STATE` | Expected on restricted accounts; see `hardware.warning` |
  606. ### Missing .NET runtime
  607. Install [.NET 10.0 Runtime](https://dotnet.microsoft.com/download/dotnet/10.0) on the machine running `MssqlMcp.exe` (not required for self-contained publish output).
  608. ## License
  609. Copyright (c) Microsoft Corporation. Licensed under the [MIT license](LICENSE).

Ratings and comments

Sign in to rate this skill.

Sign in

Free to use. If it’s helped, pass it on.