Share
Rory Cornelius, October 12, 2026
Get the Best Cognos Solution
for Your Business Today!
Summary
- Connecting an AI agent to SQL Server through MCP takes about an afternoon, but access doesn’t tell the agent what your columns mean, so the same question can come back with different answers.
- Microsoft’s SQL MCP Server builds the T-SQL itself from fixed tools. Copilot Agent Mode in SSMS and the MSSQL extension for VS Code let the agent write queries with your approval, and the Azure MCP Server doesn’t read your data at all.
- To keep the agent from doing more than a report user, connect it through a SELECT-only login, turn off the create, update and delete tools, and set up authentication. Approval prompts aren’t a security boundary.
- Business-named views, column descriptions and lists of valid codes cut down the guessing.
- To measure accuracy, ask ten questions with certified answers five times each, then rerun the same test after each fix.
Table of Contents
A SQL Server MCP server lets Claude, GitHub Copilot or another AI agent query your database in plain language, and you can usually get one connected in an afternoon. Getting it to answer correctly takes longer. This guide compares the SQL Server MCP server options as of October 2026, shows how to set one up so the agent can’t do more than a report user, and gives you a test for measuring how often its answers are right.
The request usually sounds like the one on r/mcp: “I would like to use MCP to query my SQL database hosted in Azure, ideally I would like to use in VSC copilot.” An afternoon later it works. Claude or Copilot lists your tables, writes T-SQL and comes back with numbers.
Now ask it the same business question five times. Say the question is net revenue for Ohio last quarter. A plausible result: two answers match the finance report, two are confidently wrong, and one run gives up and asks which table you meant. An answer that’s right 40% of the time is worse than no answer, because nobody can tell which 40% they got.
Fixing that is what our [four-week SQL Server MCP accuracy sprint]([sprint page URL]) does, one business domain at a fixed price.
Why a connected SQL Server MCP server still gives wrong answers
Through an MCP server the agent can see dbo.FactSls with columns AmtNet, AmtNet2 and StsCd. Whether AmtNet is in cents or dollars, whether it includes returns, and whether StsCd = 5 means shipped or confirmed are all left for it to guess. The server hands over access to the data, and the meaning stays in people’s heads and in old report logic.
The SQL MCP Server tools reference warns that without field descriptions, “agents see only entity names and may guess column names incorrectly.” The descriptions guide adds that agents invent plausible values, like Production when the valid value is Prod. Gartner expects 60% of AI projects without AI-ready data to be abandoned.
Which SQL Server MCP server to pick (as of October 2026)
This category changes monthly, so treat what follows as a snapshot. (Searches for “MSSQL MCP server” turn up all of these; they differ mostly in who writes the SQL and what the agent is allowed to change.)
SQL MCP Server (Data API builder). This is Microsoft’s open source option, announced GA in April 2026 and shipped as part of Data API builder (version 1.7 and later, with 2.0 the current release). It works with SQL Server, Azure SQL and Fabric SQL databases. Instead of letting the model write free-form SQL, it exposes seven fixed tools: describe_entities, read_records, aggregate_records, execute_entity, and create, update and delete. It builds the T-SQL itself, deterministically. You list which tables, views and procedures it may touch, and you set role permissions down to the column. For analytics, note that read_records reads one table or view and doesn’t join, so Microsoft recommends exposing views or stored procedures.
GitHub Copilot Agent Mode in SSMS. As of SSMS 22.10.2 (September 28, 2026), Agent Mode is generally available. It runs a bundled local MCP server, defaults to READ_ONLY, and asks for your approval before each query. It suits DBAs and developers working interactively.
The MSSQL extension for VS Code. Its agent mode tools include run_query, run with your existing connection credentials, with approval on each call.
Azure MCP Server. Watch out if you searched for “Azure SQL MCP server.” Its Azure SQL tools create and delete servers, databases and firewall rules, but none of them reads your data.
Community servers. These mostly let the model write the SQL. RichardHan’s server runs SELECT, INSERT, UPDATE and DELETE. The PyPI mssql-mcp-server separates a read-only query_sql from an execute_sql that runs anything, DDL included. ConnorBritain’s server has a READONLY=true switch. Several sample configs show sa and a password in plain JSON, which is the one part not to copy.
How to make a SQL Server MCP server read-only and secure
- Use a dedicated low-privilege login. Microsoft’s execution context guide shows the pattern: CREATE USER … WITHOUT LOGIN, then GRANT SELECT on the reporting schema only. In SSMS you assign that user with agentExecuteAsUser. Microsoft is explicit that the approval system “isn’t a security boundary,” so the real control is what SQL Server lets that login do.
- Turn off the write tools. All seven DML tools in SQL MCP Server are on by default, delete included. For a reporting endpoint, set create-record, update-record and delete-record to false under runtime.mcp.dml-tools. Disabled tools disappear from the agent’s tool list.
- Fix authentication before anything leaves your laptop. In Data API builder 2.0 the default provider is Unauthenticated, which runs every request as anonymous. Configure Entra ID, keep secrets in @env() or Key Vault, and use managed identity to reach Azure SQL. If row-level security depends on who is asking, the On-Behalf-Of option passes the real user through to the database.
- Turn on tracing. DAB 2.0 writes MCP tool calls to OpenTelemetry, which gives you a record of what the agent asked for.
Describe your data before you connect an agent
- Put views with business names in front of base tables. A view named Sales.NetRevenueByState with a column called NetRevenueUSD gives the agent far less to guess than dbo.FactSls.AmtNet2.
- Give every exposed column a description that states units, grain and exclusions. For example: “Net revenue in USD after returns and discounts, one row per invoice line.”
- List the valid codes. Microsoft’s database instructions page uses 1 = Scheduled, 2 = Completed, 5 = Confirmed, 99 = Canceled as its example.
- Know where each agent reads. SQL MCP Server serves descriptions from dab-config.json, and its describe_entities tool reads that config, not the database. SSMS Copilot reads AGENTS.md extended properties on your objects. Neither one feeds the other, so if you serve two agents, decide where the master copy lives.
Run the five-times self-test to measure accuracy
- Pick ten questions your business already answers in a certified report, such as net revenue by state, open orders or headcount.
- Write down each certified number.
- Ask each question five times, in a fresh session each time, through the same MCP setup your users will have.
- Log the number, the tool calls and the SQL for every run.
- Score every run as right, wrong or gave up.
Fifty runs gives you a baseline, and a question that comes back right three times out of five usually points at a specific column or definition.
Doing that by hand gets old fast. We built SchemaIQ to run the test: it asks every question five times, with and without your definitions, and scores each answer with fixed rules instead of an AI judge.
To know whether a fix worked, rerun the same fifty questions after you add the views and descriptions, and score them with a script that compares each answer to the certified number rather than with another AI. Keep the scoring strict enough that the test can come back showing no improvement, because a test that can only show gains isn’t measuring anything.
Why no MCP server can settle your business definitions
AI can draft most of the descriptions in hours. What it can’t settle is that “net revenue” means one thing in the ERP, another in the warehouse and a third in the finance team’s month-end file. Microsoft’s own example instruction says revenue “is defined as SUM(NetAmount) excluding refunds.” That instruction is only as good as the decision behind it: someone with authority has to pick one of the three definitions, write it down and own it when finance changes the rule next year. No MCP server makes that call.
SQL Server MCP server FAQ
It’s a server that lets an AI agent such as Claude or GitHub Copilot work with a SQL Server database through the Model Context Protocol. Depending on the server, the agent either writes its own T-SQL or calls fixed tools that build the query for it.
Yes. SQL MCP Server ships as part of Data API builder and was announced GA in April 2026. Copilot Agent Mode in SSMS and the MSSQL extension for VS Code also connect agents to SQL Server with Microsoft’s own tooling.
No. Its Azure SQL tools manage servers, databases and firewall rules, and none of them reads the data inside your tables.
Connect it through a dedicated user that only has SELECT on a reporting schema, and turn off the create, update and delete tools if you use SQL MCP Server. Don’t rely on query approval prompts, because Microsoft says they aren’t a security boundary.
Usually because the schema doesn’t say what the columns mean, so the agent guesses differently from run to run. Business-named views, column descriptions and lists of valid codes cut down the guessing, and the five-times test shows how much.
Where PMsquare fits
We get the agent behind your SQL Server MCP server answering correctly in a four-week sprint, one domain at a fixed price. We describe that domain on top of the systems you already have, so nothing is migrated or renamed. You keep the described domain, exportable as JSON or YAML, along with correct answers in the AI you already own and a before-and-after scorecard built on the five-times test. The work never widens who can see what.
Not sure which domain to fix first? Start with a free readiness report. Answer a few questions about your data and AI setup, and within one business day you’ll know where your AI is most likely to guess, which domain to fix first, and which test questions to start with.
Be sure to subscribe to our newsletter for more PMsquare updates and insights delivered straight to your inbox.
