Microsoft Copilot Studio for SQL Server DBA Work: The Complete Guide

---

This guide covers how Microsoft Copilot Studio, the low-code platform for building AI agents, actually connects to SQL Server, and what that connection is genuinely good for versus where it quietly falls short. There are two structurally different ways to wire SQL Server into a Copilot Studio agent, they behave differently at runtime, and confusing them is the single most common way this kind of project goes wrong. This guide also covers what it actually costs, the security controls specific to this platform, and how it relates to SQLYARD’s existing MCP and SQL Server coverage, since the two overlap but are not the same thing.

1Copilot Studio vs. the Other “Copilot”s

Microsoft uses the word “Copilot” for at least four distinct products, and a job posting or a meeting mentioning “agentic Copilot work” could mean any of them.

Product	What it is	Relevant to this guide?
Copilot Studio	Low-code platform for building custom AI agents with Topics, Actions, and knowledge sources	This is the subject of this guide
Microsoft 365 Copilot	The general AI assistant embedded across Word, Excel, Teams, Outlook	Related; can index SQL Server data separately via Copilot connectors, covered briefly in Section 3
GitHub Copilot Agent Mode (in VS Code, via the MSSQL extension)	A developer-focused agent that connects to SQL Server, retrieves schema, and executes queries inside VS Code	Different product, same underlying goal; not covered in depth here
Copilot in SSMS	AI assistance directly inside SQL Server Management Studio; as of SSMS 22.7 this includes a genuine Agent Mode, not just chat	Different product from Copilot Studio, but now a real agent platform in its own right; covered in Section 2

2GitHub Copilot Agent Mode in SSMS: The Other Agent Platform

Confirmed directly from Microsoft’s own Community Hub announcement and Microsoft Learn documentation: SSMS 22.7, shipped June 9, 2026, introduced GitHub Copilot Agent Mode as a preview feature. This is a genuinely different thing from the chat-based Copilot assistance that reached general availability in SSMS 22.4.1 in March 2026. Where the earlier Ask mode answers questions and helps draft T-SQL, Agent mode operates autonomously across multi-step tasks, requesting approval before running each query or command.

Three things make this directly relevant to DBA work specifically, not just developer productivity:

• It runs on a local MCP server. Confirmed from Microsoft Learn: Agent mode in SSMS uses a local MCP server named sql-tools, with a predefined set of tools. This is the same Model Context Protocol already covered in depth in SQLYARD’s MCP and SQL Server guide, implemented here as Microsoft’s own first-party client rather than a community-built server.
• It ships with DBA-specific built-in skills. Confirmed from Microsoft’s dedicated announcement on execution context: built-in skills include troubleshooting performance issues, analyzing missing indexes, and reviewing configuration, with the ability to create custom skills for additional workflows.
• It defaults to read-only in this preview. Corroborated across multiple sources covering the same release. Every proposed action requires explicit approval before it runs.

Governance: execution context, not just approval prompts

The same release introduced a configurable execution context, controlled through a CONSTITUTION.md file at the database level. Confirmed from Microsoft’s own documentation: when an agentExecuteAsUser value is specified in that file, all queries, in both Ask and Agent mode, execute under the context of that specified user or login rather than the connecting account’s own permissions.

Requirements

Confirmed from Microsoft Learn: SSMS 22.7 or later with the AI Assistance workload installed, and a GitHub account with Copilot access. Microsoft’s documentation notes GitHub Copilot can be used free of charge in SSMS, separate from a paid GitHub Copilot subscription used elsewhere.

This sits alongside, not in competition with, the rest of this guide. Copilot Studio (Sections 3 onward) is for building an agent that other people, business users, executives, support staff, use through Teams or a chat channel. SSMS Agent mode is a DBA’s own tool, running locally in the same application already used to manage the server. Someone could reasonably use both: an SSMS Agent mode session for personal, hands-on diagnostic work, and a Copilot Studio agent published for a wider team that should never need direct SSMS access at all.

3Two Fundamentally Different Ways to Reach SQL Server

Copilot Studio agents can reach SQL Server data through two structurally different mechanisms, and they are not interchangeable.

Mechanism	What it actually does	Best for
Knowledge Source	Adds SQL Server or Azure SQL as a grounding source for generative answers; the agent semantically searches over indexed schema and data	Natural-language Q&A over relatively stable reference data
Connector Action	Uses the Power Platform SQL Server connector to run defined operations: execute a query, run a stored procedure, get or insert a row	Live, dynamic, real-time queries against current data

Confirmed directly in Microsoft’s own current documentation for real-time knowledge connectors: this newer connector-based knowledge approach indexes only metadata, such as table and column names, with no data movement between systems, and each request executes against the target system at runtime. That is a meaningfully different architecture from older-style static knowledge indexing, and it is worth confirming which behavior an existing agent is actually using before assuming either way.

4The Knowledge Source Path, and Its Documented Limitation

Adding Azure SQL as a Knowledge Source is the simpler path to set up: select tables, authenticate (Microsoft Entra ID service principal is a supported method), and the agent can immediately answer natural-language questions grounded in that schema.

The practical takeaway, stated plainly by that same Microsoft guidance: when real-time SQL execution is actually required, the recommended approach is defined Connector Actions, not the Knowledge Source path. Reserve Knowledge Source for genuinely reference-style questions, not anything expected to reflect current, live database state.

5The Connector Action Path: Real-Time Queries

The Power Platform SQL Server connector is the reliable path for live queries. Confirmed directly from Microsoft’s official connector reference, its available actions include:

• Execute a SQL query (V2)
• Execute a stored procedure
• Get row (V2) / Get rows
• Insert row, Update row, Delete row (V2)

Several concrete, documented constraints matter before building on this:

Constraint	Detail, confirmed from Microsoft’s connector reference
Minimum on-premises SQL Server version	SQL Server 2005
Request size limit (on-premises)	2 MB
VNet-linked environments	Only a limited subset of actions are supported; not the full action list
Server-side triggers	Can silently prevent Insert/Update actions from working as expected; the documented workaround is to use a stored procedure or native query instead, or remove the trigger
Connection sharing	Connections are not shareable by default; each user of a shared app or flow is prompted to create their own connection

For an on-premises SQL Server instance specifically, reaching it from Copilot Studio requires the Power Platform on-premises data gateway, or a VNet data gateway for Azure-hosted infrastructure without public endpoints. This is standard Power Platform architecture, not something unique to Copilot Studio; the same gateway is used by Power Automate and Power BI to reach on-premises data.

Separately, calling SQL Server stored procedures is also available directly from Power Fx, confirmed via Microsoft’s own Power Platform blog, removing the earlier requirement to route every stored procedure call through a Power Automate flow. This was announced as a preview capability; confirm current status before relying on it in a new build.

6Building a Diagnostic Agent: Step by Step

A minimal, genuinely useful starting agent: one that answers “are there any blocking sessions right now” using a Connector Action, not a Knowledge Source, given the limitation in Section 3.

1. Create a dedicated, low-privilege SQL login for the connector to use. Grant VIEW SERVER STATE and VIEW DATABASE STATE only; do not use an administrative account. This mirrors the same least-privilege principle already established for MCP servers in SQLYARD’s MCP and SQL Server guide.
2. In Copilot Studio, add a new Action using the SQL Server connector, authenticated with that dedicated login.
3. Select Execute a SQL query (V2) as the operation, with a fixed, parameterized query against sys.dm_exec_requests and sys.dm_exec_sql_text to surface blocking session IDs, wait type, and query text. Do not expose an open-ended, agent-authored query string as the input; keep the SQL fixed and pass only safe parameters, such as a database name filter.
4. Build a Topic that triggers this Action on phrases like “check for blocking” or “any blocking sessions,” and have the agent format the returned rows into a readable summary.
5. Test with the actual DMV data, not just the Copilot Studio preview pane, since preview mode has documented limits described in Section 3 for the Knowledge Source path; Connector Actions do not share that specific limitation, but testing against live data before publishing remains standard practice regardless of path.

7Security and Governance Specific to Copilot Studio

The core database-security principles already covered in SQLYARD’s MCP and SQL Server guide apply here too: dedicated low-privilege accounts, never a shared administrative login, and audit visibility into what the agent actually did. Two things are specific to Copilot Studio and the Power Platform layer rather than the database layer:

• Data Loss Prevention (DLP) policies. Microsoft’s own guidance for real-time connector knowledge sources explicitly recommends governing usage with Power Platform DLP policies, which control which connectors can be combined in the same agent and can block a SQL Server connector from being paired with an outbound connector capable of exfiltrating results.
• Environment-level connection scope. Data connections in Copilot Studio are managed at the environment level, not the individual agent level. A broken or overly broad SQL connection can affect every agent in that environment, not just the one it was built for.

8Licensing: What This Actually Costs

Confirmed directly from Microsoft’s current official billing documentation:

• The Copilot Studio maker license, needed to build and edit agents, is free of charge, assigned by an admin.
• Usage is billed in Copilot Credits, the unit that replaced the older “messages” terminology starting September 1, 2025, with no change to the underlying pack quantity or pay-as-you-go rate.
• A prepaid capacity pack costs $200 per month for 25,000 Copilot Credits.
• Pay-as-you-go is billed at $0.01 per credit through an Azure subscription, with no upfront commitment.
• If a user already holds a Microsoft 365 Copilot license, their use of agents inside Copilot Chat, Teams, or SharePoint for classic answers, generative answers, or Microsoft Graph tenant grounding does not consume paid Copilot Studio credits.

9Where This Fits Next to MCP

SQLYARD’s existing MCP and SQL Server guide covers the Model Context Protocol in depth, including a working FastMCP-based read-only server, real CVEs, and a full governance checklist. That guide remains the reference for MCP-specific security controls; this guide does not repeat that content.

The relationship between the two: as of current documentation, SQL Server is available to Copilot Studio through a native Power Platform connector, not a native Microsoft-provided MCP server; SQL Server sits in the category of services with a connector but no first-party MCP server. Third-party MCP bridges to SQL Server exist commercially, but that is a different, additional path, not Microsoft’s native one. Separately, Copilot Studio can consume other MCP-based tools where they exist, and can also consume a Fabric Data Agent, which itself can be built on a mirrored SQL Server database inside Fabric, a path already covered in SQLYARD’s Fabric guide. SSMS Agent mode, covered in Section 2, is the clearest first-party counterexample to the connector-only pattern: it runs on a local MCP server (sql-tools) directly. Across these three products, Microsoft has not standardized on one integration model; each ships whatever architecture fit that specific product.

The practical decision point: Copilot Studio’s connector path is the stronger choice inside an existing Microsoft 365 and Power Platform environment, where licensing, gateway infrastructure, and governance are already in place. MCP is the stronger choice when the same database access needs to serve multiple AI clients at once, Claude, ChatGPT, and Copilot alike, through one open, vendor-neutral standard rather than a Microsoft-specific connector.

10A Real-World Account

A documented, dated account from a Microsoft partner organization describes building a natural-language-to-SQL agent in Copilot Studio that queries a live Azure SQL database from Microsoft Teams. This is cited here as one practitioner’s account, not as Microsoft-confirmed guidance. The operational lesson from that account is worth repeating regardless of source: before rolling a database-connected agent out to executives, they validated AI-generated answers against existing, established Power BI reports as a dedicated testing phase, treating the existing reports as the source of truth to verify against rather than trusting generated SQL output on faith.

11Key Takeaways

• “Copilot” refers to at least four distinct Microsoft products; confirm which one a job posting or meeting is actually referring to before assuming Copilot Studio.
• SSMS 22.7 (June 2026) introduced a genuine Agent Mode, not just chat assistance, running on a local MCP server with built-in DBA skills for performance troubleshooting, missing-index analysis, and configuration review. It is a separate product from Copilot Studio, built for a DBA’s own hands-on use rather than for publishing an agent to a wider team.
• Copilot Studio reaches SQL Server two structurally different ways: Knowledge Source (semantic grounding) and Connector Actions (live query execution). These are not interchangeable.
• The Knowledge Source path has a real, Microsoft-documented failure mode for dynamic conversational queries; Microsoft’s own guidance recommends Connector Actions when real-time SQL execution is actually required.
• The Connector Action path carries real, documented constraints: SQL Server 2005 minimum version on-premises, a 2 MB request size limit, restricted actions inside VNet-linked environments, and a documented trigger interaction that can silently break Insert/Update actions.
• Licensing is free to build; usage is billed in Copilot Credits ($200 per 25,000, or $0.01 pay-as-you-go), with Microsoft 365 Copilot-licensed users getting zero-rated usage inside Copilot Chat, Teams, and SharePoint.
• This approach fits best inside an existing Microsoft 365 and Power Platform environment, where the licensing, the on-premises data gateway, and the DLP governance are usually already in place. A shop without that existing investment, or one needing one connection reusable across multiple AI vendors, is generally better served starting with MCP instead.
• SQL Server currently reaches Copilot Studio through a native connector, not a native Microsoft MCP server; that is a distinct architecture from the MCP path already covered in SQLYARD’s MCP and SQL Server guide, not a replacement for it.

The technical information in this article was verified against Microsoft documentation at the time of publication. SQL Server features, cloud service capabilities, licensing terms, and configuration requirements can change between versions and cumulative updates. Always validate implementation details against current Microsoft Learn documentation before deploying to production. References in this article link directly to the authoritative Microsoft sources.

References

• Microsoft Docs: Microsoft Copilot Studio Documentation
• Microsoft Docs: SQL Server Connector Reference
• Microsoft Docs: Add Power Platform Connectors as Knowledge (Preview)
• Microsoft Docs: Copilot Connectors versus Power Platform Connectors as Knowledge Sources
• Microsoft Docs: Microsoft Q&A: Azure SQL Knowledge Source Connected but Agent Cannot Retrieve Data (documented runtime limitation)
• Microsoft Docs: Troubleshoot Enterprise Data Knowledge Sources in Copilot Studio
• Microsoft Docs: Troubleshoot Connector Request Failures in Copilot Studio
• Microsoft Docs: Standard Harness Licensing in Copilot Studio
• Microsoft Docs: Microsoft 365 Copilot Pricing: Copilot Studio
• Microsoft Docs: Azure SQL and Microsoft SQL Server Connectors for Microsoft 365 Copilot
• Microsoft Docs: GitHub Copilot Agent Mode, MSSQL Extension for VS Code
• Microsoft Docs: GitHub Copilot Agent Mode (Preview), SQL Server Management Studio
• Microsoft Docs: Announcing the Release of SSMS 22.7.0 (Microsoft Community Hub)
• Microsoft Docs: Introducing Execution Context for GitHub Copilot in SSMS
• Microsoft Docs: Call SQL Server Stored Procedures Directly in Power Fx (Preview)
• SQLYARD: MCP and SQL Server: What Every DBA Needs to Know About the Protocol Connecting AI to Your Database
• SQLYARD: Microsoft Fabric: The Complete Guide — OneLake, Lakehouse, Pipelines, Medallion Architecture, and the Full Workshop