
APoint MS SQL
io.github.A-Point-Systems-ltd由 GitHub 账号 A-Point-Systems-ltd 发布v1.2.5更新于 Nov 21, 2026
Microsoft SQL Server for AI agents: schema introspection, safe queries and an AI Insights cache.
概览
让 AI 助手连接 Microsoft SQL Server 或 Azure SQL,进行架构查看、只读查询、DDL/DML 操作并维护 AI 洞察缓存。
- 可以做什么
- 列出并描述数据库对象:表、视图、存储过程、函数、触发器、索引、键、类型和安全主体
- 将对象脚本化为可直接执行的 T-SQL DDL,并读取模块定义与参数
- 执行带行数上限的只读 SELECT 查询,并获取服务器版本与硬件信息
- 通过带写入标记的工具创建、删除表,插入、更新数据并执行单条 DDL/DML
- 按数据库维护 AI 洞察缓存,支持安装、写入、历史与刷新
- 列出、打开和关闭命名连接,可选注册临时连接
- 适用场景
- 适合需要让助手探索或查询 SQL Server、Azure SQL 数据库,为现有对象生成 DDL,或在缓存洞察层中记录架构对象说明的场景。主要面向在 Windows 上使用 VS Code、Cursor 或 Claude Desktop 并连接已知数据库的用户。
- 运行要求
- 以本地 Windows x64 可执行文件运行(stdio);打包的 .mcpb 与 VS Code 扩展自带运行时,从源码构建需要 .NET 10.0。目标须为 SQL Server 2008 R2 及以上或 Azure SQL Database。必须设置 CONNECTION_STRING、MSSQL_CONNECTIONS 或 MSSQL_CONNECTIONS_FILE 之一;可选变量包括 MSSQL_MANAGED_CONNECTIONS_FILE、MSSQL_ALLOW_ADHOC_CONNECTIONS、MSSQL_ADHOC_ALLOW_WRITE、MSSQL_ADHOC_ALLOWED_HOSTS、USE_INSIGHTS_LAYER、MSSQL_TOON 和 LOG_FILE_PATH。
安装
在 SourceWeft 中
- 打开 控制台中的 APoint MS SQL,将其添加到工作区。
- 为需要使用其工具的对话启用该服务。
Desktop only,通过 STDIO。 STDIO 服务会启动本地进程,因此需要 SourceWeft 桌面宿主。
其他 MCP 客户端
参照 仓库 中的启动说明。
README
MSSQL MCP Server (.NET 10)
A Model Context Protocol (MCP) server for Microsoft SQL Server and Azure SQL Database. Built with the official MCP C# SDK (ModelContextProtocol 2.2.0) on .NET 10.
Forked from 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.
It ships in three forms:
See Install.
Table of contents
- Requirements
- Install
- Quick start (from source)
- MCP client configuration
- Environment variables
- Multiple connections
- MCP tools reference
- AI Insights layer
- Response shape
- Build, publish, and test
- Project layout
- Security notes
- Troubleshooting
- License
Requirements
Tested target versions include SQL Server 2008 R2 through 2022 and Azure SQL Database.
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.
Install
Option 1: VS Code / Cursor extension
- 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>.vsixfrom the repository's GitHub releases (tagext-v<version>). Then run Extensions: Install from VSIX... from the Command Palette, or: For Cursor, usecursor --install-extension.
- 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.
- Agents: VS Code agent mode and Cursor get the MCP server automatically for every open connection. Nothing else to configure.
- 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.
The extension's own documentation covers the explorer, query windows and settings: vscode-extension/README.md.
Option 2: Claude Desktop extension (.mcpb)
- Download
apoint-ms-sql-<version>.mcpbfrom the repository's GitHub releases (tagext-v<version>, 1.0.10 and later), or build it with.\packaging\mcpb\build-mcpb.ps1(see Package the Claude Desktop extension). - Install it: double-click the file, or open Claude Desktop Settings > Extensions and drag the file in. Then confirm the install.
- 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).
- AI Insights layer (
- 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
- Get
MssqlMcp.exe. Build it with.\publish-release.ps1(Publish single-file executable), or build from source (Quick start). - Add it to your client's MCP configuration with
CONNECTION_STRING,MSSQL_CONNECTIONSorMSSQL_CONNECTIONS_FILE. See MCP client configuration and Multiple connections. - Optional: to get the connection manager form in hosts that support MCP Apps (such as Claude Desktop), add
MSSQL_MANAGED_CONNECTIONS_FILE.
Quick start (from source)
Point your MCP client at the built executable:
Set CONNECTION_STRING (or MSSQL_CONNECTIONS, see Multiple connections) in the MCP server environment (see sample_mcp.json for a template).
First prompt to try: “List tables in the database” (the agent should call list_objects with objectType=Table).
MCP client configuration
Cursor
Add a project-level or user-level MCP config. Example (adjust paths and connection string):
Restart the MCP server after changing environment variables.
VS Code (GitHub Copilot Agent)
Open Settings (JSON) and add under mcp.servers:
Claude Desktop
Edit claude_desktop_config.json:
Azure SQL (Entra ID interactive)
Production-style single-file publish
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.
Environment variables
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).
MCP tools reference
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)). 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.
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.
Read-only inspection
Data modification and DDL
AI Insights (8 tools)
Requires USE_INSIGHTS_LAYER enabled (default) and install_insights_layer run once per database.
When USE_INSIGHTS_LAYER=false, insight-specific tools return errors or empty status; introspection tools skip insight enrichment.
Connection management (3 tools)
These three tools take no connection argument. Every other tool accepts an optional connection argument; see Multiple connections.
Read vs execute routing
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.
-
read_data — a single
SELECT/WITH … SELECT.SELECT … INTO,OPENQUERY/OPENROWSET/OPENDATASOURCEand linked-server (4-part) names are rejected. The query runs inside a transaction that is always rolled back. At mostmaxRowsrows are returned (default 500, max 10000).datais always the row array; when more rows exist the response also carries top-leveltruncated: trueandmaxRows. Rows come as TOON by default (see below). -
TOON row format —
read_data,list_objects,list_insightsandget_insight_historytaketoon(defaulttrue). A successful result whosedatais 2 or more uniform rows (or 2 or more plain values) is sent as TOON (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:Upgrading to 1.1.0: this is a breaking change for clients that parse these four tools as JSON. Pass
toon=false, or setMSSQL_TOON=false(Claude Desktop extension: turn off Compact row results (TOON)). It only shows with 2+ rows, so test with multi-row results.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 andtoon=falsegive the usual JSON. Clients that parse results as JSON must passtoon=false; the VS Code extension does so for all its own calls. -
insert_data / update_data / create_table / drop_table — only the matching statement type (
INSERT,UPDATE,CREATE TABLE,DROP TABLE) is accepted, soinsert_data("DROP TABLE x")is refused. -
execute_sql — single DDL/DML statements (including
SELECT … INTO; aCREATE PROCEDUREbody counts as one statement). Any plainSELECTis rejected with a message pointing toread_data.SET,DECLARE,USE,WAITFORandSHUTDOWNare rejected as unsupported.
This keeps destructive operations behind an explicitly flagged tool and prevents accidental full-table reads through the write path.
Script runner (extension only)
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.
-
Arguments:
script(required),maxRows(per result set, default 1000, clamped to 1..10000),connection. -
Splits the script on SSMS-style
GOlines (GO nrepeats a batch;GOinside strings, comments or[identifiers]does not split) and runs every batch on one session, soSEToptions and#temptables carry across batches. -
Returns
data: { resultSets, messages, hadErrors, batches, elapsedMs }. Each result set hasbatch,columns(name,type),rows(arrays in column order),rowCount(total) andtruncated. Messages havekind(info,rows,error,warning),textandline(1-based script line, or null). Errors that name a procedure, function, view or trigger use the SSMS headerMsg n, Level l, State s, Procedure p, Line n. When the batch is that module's ownCREATE/ALTER(a compile error), the line is mapped to the script; otherwise (for example an error raised inside anEXEC'd procedure) it is the module's own line number andlineis null. SQL errors areerrormessages and execution continues with the next batch, like SSMS;success: falseonly for an empty script or a connection that cannot be opened. -
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, otherSEToptions,sp_setapprole,EXECUTE ASwithoutREVERT) never reaches the next one. Within one run, all batches share that session. -
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
warningper run. -
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"), andbigintvalues outside +/-2^53 ("9223372036854775807"). Smallerbigintvalues,int,floatandbitstay JSON numbers or booleans; dates, times anduniqueidentifierare strings as before. Binary values (varbinary,binary,timestamp) are SSMS-style0x+ uppercase hex ("0x00FF10"), no longer base64. -
Cancel. Cancelling the call (the client sends
notifications/cancelledfor 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. -
On a read-only connection every batch must be a single read-only
SELECT(theread_datarules) and runs inside a transaction that is always rolled back; other batches are refused with anerrormessage that starts withRead-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. -
When
MSSQL_SCRIPT_RUNNERis not set,run_scriptis an unknown tool (no connection routing either). -
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, soCOMMITin the same run.
DDL history (extension only)
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).
-
Arguments:
action(required:status,install,listorget, case-insensitive),schema,name,id,top(default 100, clamped to 1..500),connection. -
statusreturnsdata: { tableExists, tableCompatible, triggerExists, triggerEnabled, canInstall, serverName, databaseName }, pluswarningswhen an existing table is compatible but lossy, or when an existingDDL_Audit_Writermust not be used (the same principal check asinstall, without failing;canInstallis then false), andloggingSuppressed: truewhendbo.DDL_AuditLoghas DML triggers (the installed trigger then records nothing).serverNameanddatabaseNameare the server's own@@SERVERNAMEandDB_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) namedDDL_Audit.canInstallis true when the connection is read/write, the table or the trigger is missing, and an existing table is compatible. -
tableCompatibleis true when the table exists and the trigger can write to it.max_lengthis read in bytes, so nvarchar counts 2 bytes per character.HostName,LoginName,SchemaName,ObjectName,ObjectTypeandProgramNamearevarcharornvarcharof at least 100 characters (ormax), andEventTypeof at least 64.CommandTextisnvarchar(max)orvarchar(max).varchar(max)is compatible, withwarnings: ["CommandText is varchar(max); non-Latin text in DDL will be stored lossy."].CommandXMLisxml. A characterCommandXMLis refused: SQL Server has no implicit xml-to-character conversion, soCREATE TRIGGERitself fails on such a table with error 257.- None of those nine columns is computed or an identity.
IDexists with an integer type, andPostTimewith a date/time type.- Every other column the trigger leaves out is an identity, has a default, is nullable, computed or
rowversion.
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. -
installcreatesdbo.DDL_AuditLogif it is missing, then theDDL_Audittrigger if it is missing (from the same embedded script asinstall_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. Returnsdata: { createdTable, createdTrigger, triggerEnabled }. When an existingDDL_Auditis disabled, it stays disabled:triggerEnabledis false anddata.warningisDDL_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 withCreating 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 indexIX_DDL_AuditLog_ObjectNameon(ObjectName, ID) INCLUDE (SchemaName, LoginName, PostTime), forlistand the trigger's own lookup. An existing table is never given an index. -
The
DDL_Audit_Writeruser. The trigger runsWITH 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 grantsINSERT, SELECT ON dbo.DDL_AuditLog, nothing else. It never runs asdbo: an orphaneddbo(a database restored after its owner login was dropped) would makeEXECUTE AS 'dbo'fail inside every DDL statement (15517), and code that runs inside the audit insert never gets more than those two permissions.- If a principal named
DDL_Audit_Writeralready exists and is not a loginless SQL user (type = 'S'; on 2012+ alsoauthentication_type = 0; on every version its SID must not belong to a login), install creates nothing and fails withA 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 loginlessDDL_Audit_Writeris reused (and granted again) only when it has no more rights than the trigger needs: no role membership (besidespublic), no owned schema, and no granted or grant-with-grant permission other thanCONNECTon the database andINSERT/SELECTondbo.DDL_AuditLog. Otherwise install creates nothing, alters or revokes nothing on it, and fails withA 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. - 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. install_insights_layeruses the same routine (user, grant, pre-flight, trigger script).
- If a principal named
-
Permission. Install needs
db_owner, orALTER ANY USER, permission to grant ondbo.DDL_AuditLog,IMPERSONATEon the new user andALTER ANY DATABASE DDL TRIGGER. Adb_ddladmin-only account getsCreating the DDL_Audit trigger failed: ...and nothing is left behind. -
The installed trigger does not block other users' DDL.
install_insights_layeruses the same hardened script. Existing triggers are never altered, so a trigger installed by an earlier version keeps its old behaviour.- It runs as
DDL_Audit_Writer, so callers need no permission ondbo.DDL_AuditLog; adb_ddladmin-only deployment account works. It recordsORIGINAL_LOGIN(), the real login, and the "last modified" message compares that login too. ADENYon the table topublicalso applies toDDL_Audit_Writer: the DDL still runs, unlogged. - When
dbo.DDL_AuditLoghas any DML trigger, the insert and the lookup are skipped and the session getsDDL_Audit: this change was not logged: DDL_AuditLog has triggers.Such a trigger could otherwise roll the DDL back or run code inside it. - It sets
ANSI_WARNINGS,ANSI_PADDING,CONCAT_NULL_YIELDS_NULLandARITHABORTON andNUMERIC_ROUNDABORTOFF itself, so sessions with non-ANSI options (old ODBC or MS Access clients) do not fail with error 1934.ANSI_NULLSandQUOTED_IDENTIFIERcannot be changed inside a module, so the script sets them ON beforeCREATE TRIGGER. - The audit
INSERTand the "last modified"PRINTrun insideTRY ... CATCH, withXACT_ABORT OFF. If they fail (a truncation, a constraint), the DDL still commits, unlogged, and the session getsDDL_Audit: this change was not logged (error N).XACT_ABORT OFFis needed because triggers start withXACT_ABORT ON, under which even a caught error dooms the transaction. - 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 ondbo.DDL_AuditLogthat 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 aROLLBACKthere ends it (3609). - Every DDL statement may print a
last modifiedline that names other logins who changed the object in the last month.
- It runs as
-
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, runDROP TABLE dbo.DDL_AuditLog;, which also dropsIX_DDL_AuditLog_ObjectName. None of these affects any other object. -
IDs are
bigint-safe end to end (gettakes a 64-bitid; an existing table with abigintIDworks). -
list(needsname;schemais optional) returns the newesttopentries for that object, newest first:data: [{ id, postTime, loginName, hostName, programName, eventType, objectType, schemaName, length }].postTimeis ISO 8601 server local time (yyyy-MM-ddTHH:mm:ss.fff);lengthis the length of the command text. Whenschemais given, entries with no schema are included too.nameandschemaare trimmed, then compared to their first 100 characters, as the trigger stores them. -
get(needsid) returns one entry with its full command text:data: { id, postTime, loginName, eventType, objectType, schemaName, objectName, commandText }. An unknown id is an error. -
listandgetfail withDDL history is not installed on this database (dbo.DDL_AuditLog is missing).when the table is missing. -
On read-only connections
status,listandgetare plainSELECTs and write nothing.
IntelliSense (extension only)
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 (MIT; each ported file under MssqlMcp/LanguageService/ names its source).
- Arguments:
action(required:completion,hover,signatureHelp,warmorrefresh, case-insensitive),text(the document),lineandcolumn(1-based caret position),connection. completionreturnsdata: { items: [{ label, kind, detail, insertText, sortText }], isIncomplete, cacheState }.kindis one oftable,view,column,procedure,function,keyword,schema,parameter,variable,database,type,snippet,other. It covers tables afterFROM/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).- Procedure parameters. SqlParser offers a procedure's parameters only as signature help, so after
EXEC proc/EXECUTE procthe server adds them fromResolver.FindMethods:label@x,detailthe type (withOUTPUTwhere 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. hoverreturnsdata: { contents, range? }:contentsis plain text (for examplecolumn a(int, null)), so render it as plain text or a code block.rangeis 1-based and end-exclusive.datais null when there is nothing to show.signatureHelpreturnsdata: { signatures: [{ label, documentation?, parameters: [{ label, documentation? }] }], activeSignature, activeParameter }for built-in functions (DATEADD() and user procedures (EXEC dbo.p 1,).activeParameteris 0-based, -1 when the caret is on none.datais null outside a call.warmstarts building the metadata cache for the connection and returns at once:data: { cacheState }.refreshdrops the cache for the connection and database, then warms it again (use it after DDL in another session).- 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,completionreturns the keyword list withcacheState: "loading"andisIncomplete: true, and the build carries on;hoverandsignatureHelpreturn null. Entries are dropped when the connection is closed or removed (or an ad-hoc connection replaced), onrefresh, and after 30 minutes unused. A failed build is retried after 30 seconds. - Database context. The database is the profile's database (
Initial Catalog; the login's default database when none is set). A leadingUSE xin the text is not followed for metadata. - 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. - 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.
- Licence. SqlParser is not open source: it ships under the SQL Server Shared Management Objects (SMO) License Terms, reproduced in 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.
- Limitations.
- 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 withoutVIEW DEFINITIONorEXECUTE). Entra ID (Azure AD) authentication is untested with the language service. - SqlParser 180.9.0 throws inside
Resolver.FindMethodsfor 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). - The first bind on a large database can take longer than 2 seconds; the extension sends
warmwhen 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) sendsrefresh. See the extension README, "IntelliSense". - Single-file publish grows by about 5.5 MB (44.9 MB to 50.5 MB).
- 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
Multiple connections
One server process can hold several named SQL Server connections. Nothing changes for existing single-connection setups.
1. Legacy: CONNECTION_STRING
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.
2. MSSQL_CONNECTIONS
A JSON array, one object per connection:
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.
3. MSSQL_CONNECTIONS_FILE
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.
Quoting. A placeholder can sit unquoted, inside double quotes or inside single quotes:
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.
Inside a JSON string the quotes must be escaped: "connectionString": "Server=sql01;Database=Crm;User Id=mcp_reader;Password=\"${env:CRM_PASSWORD}\"".
Unreleased: quoted placeholders (
Password="${env:X}"andPassword='${env:X}') are escaped as described above; unquoted placeholders behave as before.
4. The rule: when is connection required?
- Exactly one registered connection:
connectionis optional. - More than one registered connection:
connectionis mandatory on every data tool. There is no default connection.
"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.
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.
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).
5. MSSQL_ALLOW_ADHOC_CONNECTIONS
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.
The connection string is parsed before anything is sent, and the operator controls what the agent may do:
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.
6. Read-only profiles
A connection with "readOnly": true refuses these tools with an error:
execute_sql, insert_data, update_data, create_table, drop_table, upsert_insight, install_insights_layer, refresh_insights, rebuild_baseline_insights.
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.
7. Full mcp.json example
Two connections, one read-only, with the password kept out of the config through MSSQL_CONNECTIONS_FILE and ${env:...}:
with C:\Secrets\mssql-connections.json:
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.
8. Connection manager (Claude Desktop, MCP Apps)
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 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.
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.
The file is a JSON object, which the form is meant to edit:
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.
The Claude Desktop extension (.mcpb) turns this on by default.
AI Insights layer
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.
What gets installed
AIInsightsschema —SchemaInsights,InsightHistory,DdlChangeWatermark, and related objects (embedded SQL inInsightsLayer/SqlScripts/).dbo.DDL_AuditLog— captures DDL events via a database-level trigger.DDL_Audittrigger — runsWITH EXECUTE AS 'DDL_Audit_Writer', a loginless user the install creates with onlyINSERT, SELECT ON dbo.DDL_AuditLog, so installing it requiresdb_owner(orALTER ANY USER, grant rights on the table,IMPERSONATEon the new user andALTER ANY DATABASE DDL TRIGGER).- The trigger is database-wide: it fires for DDL from every application, not just this server. It records
ORIGINAL_LOGIN(), callers need no permission ondbo.DDL_AuditLog, and a failed audit insert does not roll back the caller's DDL. See DDL history (extension only) for the details and the limits.install_insights_layeris still marked destructive, because it adds a database-wide trigger and a user. Remove them withDROP TRIGGER [DDL_Audit] ON DATABASE; DROP USER [DDL_Audit_Writer];. - An existing
DDL_Audittrigger is never replaced, so databases installed by an earlier version keep their original trigger definition (which runs as the caller: a login withoutINSERTondbo.DDL_AuditLoghas its DDL rolled back). To move such a database to the hardened trigger, drop the old trigger and install again. LegacyAIInsightstables from older schema versions are dropped only when empty.
- The trigger is database-wide: it fires for DDL from every application, not just this server. It records
Install is idempotent; re-running is safe.
How it works
- Read path —
describe_table,describe_view, andget_objectattachinsight(compact: the authored text, or a few facts for a baseline),insightFreshness,enrichmentSuggestedand, only when an update is needed,insightEnrichment(best-effort; never fails the parent tool). - 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
InsightHistoryand returnsinsightFreshness: StaleArchived.modify_datealone (statistics, index rebuild, constraints) does not. - Auto-population — When
INSIGHTS_AUTOPOPULATEis enabled (default), an absent or stale insight is replaced by the newest authored insight inInsightHistorywith the same fingerprint (drop/re-create keeps its insight), otherwise by a mechanical baseline (LlmModel = "auto-mechanical",Confidence = 0.30) built fromsys.*metadata. - Enrichment rule — The agent is asked to call
upsert_insightonly in the cases below. OtherwiseenrichmentSuggestedisfalse, 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 for the agent workflow. - Write path — After successful writes, a background
InsightDdlProcessingQueuedrains 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.
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:
- 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 andDataPopulatednever fires. - 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.
- Read-only connections: they never get
insightEnrichment, because they refuseupsert_insight. - Older installs: databases installed before this column existed keep working without it, but with no
DataPopulatedtrigger. Runninginstall_insights_layeragain adds the column, and the server picks it up within a minute.
Recommended first-time workflow
insightFreshness values
Response shape
All tools return DbOperationResult:
success— whether the operation completed without error.error— message whensuccessis false.rowsAffected— for DML tools (insert_data,update_data,execute_sql, …).data— tool-specific payload (object metadata, row arrays, insight status, …).
Introspection tools may add top-level keys inside data for insights (insight, insightFreshness, enrichmentSuggested, insightEnrichment).
Build, publish, and test
Build
Test
For integration tests, set CONNECTION_STRING to a real database. The test project falls back to (localdb)\MSSQLLocalDB when unset.
Publish single-file executable
Output path is configured in MssqlMcp/MssqlMcp.csproj and Properties/PublishProfiles/ReleaseSingleFile.pubxml (default: C:\Development\MCPs\MS-SQL-Release\MssqlMcp.exe).
The script also packs the same exe as:
- the Claude Desktop extension,
<release folder>\ClaudeDesktop\APoint-ms-sql.mcpb; - the VS Code / Cursor extension,
<release folder>\extension\APoint-ms-sql.vsix(install it withinstall-APoint-ms-sql.ps1in that folder). The same file is left asvscode-extension\ms-sql-mcp-win32-x64-<version>.vsix, and older.vsixfiles 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.
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.
Versioning
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.
Release checklist
Every release does all five steps, in order:
- Merge the change PRs into
main(tests green locally:.\publish-release.ps1 -Test -DryRun). - Bump the version in a
chore/release-<version>PR (commitchore(release): version <version>):MssqlMcp/MssqlMcp.csproj<Version>,vscode-extension/package.jsonandpackage-lock.json(npm version <version> --no-git-tag-version), and a## <version>entry in bothCHANGELOG.mdandvscode-extension/CHANGELOG.md. Runnpm run check:version, then merge the PR. - Check
maincarries the new version before publishing (the release is built from localmain, which must equalorigin/main): - Dry run, then release. Both build, test and pack locally (nothing builds on GitHub):
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 atC:\Development\MCPs\MS-SQL-Public), creates its releaseext-v<version>withMssqlMcp.exe, the.mcpb, the VSIX andSHA256SUMS.txt, and then updates the local release folder. - 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 withgh 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 asMssqlMcp_yyyyMMdd_HHmm.exeand the previous bundles with the same suffix. Rollback is described at the top ofpublish-release.ps1andpackaging\public\publish-public.ps1.
Package the VS Code extension (VSIX)
publish-release.ps1 builds and releases both packages on this machine:
- publishes the exe and stages it into
vscode-extension/bin; - runs the tests (
-Test, implied by-PublishPublic) and packagesms-sql-mcp-win32-x64-<version>.vsix; - packs the same exe as
apoint-ms-sql-<version>.mcpb(build-mcpb.ps1 -ExePath); - with
-PublishPublic(and without-DryRun): creates the releaseext-v<version>in the public repo, whose publish-marketplaces workflow publishes the VSIX to the Visual Studio Marketplace and Open VSX.
To build a VSIX locally:
Before running dotnet publish, rebuild the embedded connections view if apps/connections-ui changed:
Package the Claude Desktop extension (.mcpb)
The script:
- Rebuilds the connections view and publishes the self-contained exe.
- Smoke-starts the exe (it must answer MCP
initialize). - Stamps
packaging/mcpb/manifest.jsonwith the csproj version. - Validates and packs the bundle with
@anthropic-ai/mcpb(vianpx).
The output is Publish\mcpb\apoint-ms-sql-<version>.mcpb, and the script prints its SHA-256.
The bundle:
- starts with no connections and enables the connection manager on
%APPDATA%\APoint-ms-sql\connections.json; - keeps ad hoc connections and the extension-only tools off;
- maps its AI Insights layer setting to
USE_INSIGHTS_LAYER.
Project layout
Security notes
- Connection strings live in MCP client config / environment variables — never commit real credentials to the repo.
- execute_sql, drop_table, and update_data are marked destructive in MCP metadata; agents should confirm intent with the user.
- 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.
- DDL audit trigger installation requires elevated database permissions; use a dedicated database role in production.
- Startup logs mask password fields in connection strings; other secrets in the connection string are still logged — prefer Windows / Entra authentication where possible.
Troubleshooting
MCP error -32000: Connection closed
The server exited before or during MCP handshake. Check the log file:
Logs include process info, masked connection string, SQL connection test results, and stack traces.
Common failures
Missing .NET runtime
Install .NET 10.0 Runtime on the machine running MssqlMcp.exe (not required for self-contained publish output).
License
Copyright (c) Microsoft Corporation. Licensed under the MIT license.
来源:README.md,提交 a42da38
工具
0版本历史
1- v1.2.5最新Nov 21, 2026
评分
已满足适用检查项的 50%
该评分衡量此收录条目的完整程度,依据注册中心条目、端点与公开来源计算得出。它不是对服务器本身的评价,也不表示服务器安全或当前可用:可连通性是带日期的检查结果,评分也不包含安装量或用户评价。
- 近 30 天内可连通25 分
请保持端点能够响应 MCP 握手;SourceWeft 每 30 天探测一次。
- 已声明认证方式10 分
请在注册中心条目中声明凭据请求头,或保持端点能够响应探测,以便判定其认证方式。
- 已列出工具15 分
请让服务器在无凭据时响应 tools/list,或发布可在沙箱中无凭据启动的软件包。
- 描述必需15 分
- 图标10 分
- 文档15 分
- 许可证10 分