From Talk to Task: Build a Copilot Studio Agent That Drives SQL Actions
The message lands in the PAW IT Helpdesk Assistant mid-morning: "My VPN keeps disconnecting. I raised a support ticket yesterday. Can you check whether IT has updated it?" A plain chatbot can only apologize and list troubleshooting steps. Our assistant already does better, because it can search the approved PAW Labs VPN Troubleshooting Guide. But neither answer tells the employee what is happening to their ticket, because the ticket lives in a database the assistant cannot see.
This chapter closes that gap and shows you exactly how to set it up. In the earlier Microsoft Copilot Studio Tutorial: Build an AI Agent That Troubleshoots Problems and Creates Support Tickets, we built the PAW Labs VPN assistant and its first ticket-creation action. Here we give it a SQL Server backend, teach the SQL as we go, and walk through the Copilot Studio setup for every tool: look up tickets, check one ticket, create a ticket after confirmation, and send an escalation request. Each SQL script comes with its own Copilot Studio setup sheet, so you can build the database side and the agent side together.
What You Get Here That Generic SQL Tutorials Skip
Most guides stop at "add the connector and pick an action." We kept going: a complete schema, copy-ready stored procedures, a setup sheet for each Copilot Studio tool, confirmation wiring inside the ticket topic, permission grants, duplicate handling, and a test plan, all inside one continuous VPN helpdesk project. You can run every SQL script in a sandbox and learn the database side even before you open Copilot Studio.
The agent works out what the employee wants
A narrow SQL tool returns the employee's own records
The agent reports what SQL returned, nothing more
The employee approves the exact summary before any write
A stored procedure validates and writes the record
The agent checks the returned status and ticket ID
Only now does the employee hear "done" and a ticket number
See how the PAW IT Helpdesk Assistant moves beyond answering questions to actually retrieving ticket records, creating support tickets, and handling escalations through SQL Server.
What We Are Building: Four Requests, Four Controlled Operations
An employee uses normal language and never needs to know SQL. Behind each sentence sits exactly one stored procedure, and nothing else is exposed.
The read path is Employee, then the PAW IT Helpdesk Assistant, then the SQL Server connector tool, then the database, then a structured result, then the agent's reply. Writes add two stops: the agent gathers details and asks for confirmation before the procedure runs, and it reports only what the procedure returns.
Dataverse Then, SQL Now: Pick One Source of Truth
The first tutorial wrote its sample ticket to a Dataverse table. This chapter introduces a SQL Server-backed version of the same ticket process. That is an architectural step forward for our sample beta project, not a correction of the first build. PAW-1042 first appeared in the Dataverse walkthrough; here we seed it as a SQL record so the SQL demo has a familiar reference. The Dataverse record is not automatically in SQL, and nothing here synchronizes the two.
Which Capability Should Handle the Request?
Before any setup, this is the decision path we used for every request the agent can receive.
Yes: knowledge source (the VPN guide). No: continue.
Yes: a read stored procedure with an owner filter. No: continue.
Yes: confirm with the employee, then one write stored procedure. No: continue.
Yes: an agent flow that calls the procedure. No: you are done.
Your Build Map: The Order We Set Everything Up
Follow the stages in this order. Each one adds a single capability and ends with a quick test, so a problem is always easy to place.
Stage 1: Create the database, tables, and sample data
Stage 2: Build the two read procedures and test them in SQL
Stage 3: Add the read tools in Copilot Studio and test the answers
Stage 4: Build the create procedure, then wire confirmation into the ticket topic
Stage 5: Lock down permissions with roles and EXECUTE grants
Stage 6: Add the escalation procedure (optional) and an agent flow if needed
Stage 7: Run the test plan, then pilot with a small group
Before You Open Copilot Studio
Decide What the Agent May Do
We wrote the boundaries down before building anything. Connecting to SQL should never turn an agent into a database administrator, and technical permissions are stronger than prompt wording: a prompt saying "never show another employee's data" is a wish, while a procedure that filters by owner is a rule.
Knowledge, SQL, Tools, Flows, and People: Who Does What
The same project now has several moving parts, so we keep each job separate. The VPN guide is not the ticket database, and SQL does not replace the guide. A tool is the controlled bridge between the agent and SQL; if you want the background on how tools are picked and invoked, read Microsoft Copilot Studio Tools Explained: Give Your AI Agent the Power to Search, Act, and Automate. On the knowledge side, Microsoft Copilot Studio Knowledge Base: Build AI Agents That Find the Right Answers covers how the VPN guide is retrieved: the guide explains what to do, and SQL reports what has already happened.
The model must never be the source of truth for ticket status or business rules. If the agent says PAW-1042 is In Progress, it must be because SQL said so moments ago. This is the same reason we cover AI hallucinations separately: an agent that has to quote a database row has far less room to invent an answer.
Choosing the SQL Environment
The tutorial works with either option below, but the experience differs. We recommend Azure SQL Database for the main walkthrough because it fits a cloud Copilot Studio setup most naturally.
Execute a SQL query (V2) is not supported for on-premises SQL Server or gateway connections.
For stored procedures over a gateway: OUTPUT parameter values are not returned, only the first result set comes back, and the return value is unavailable.
That is why every PAW Labs procedure returns exactly one result set.
Stage 1: Design the PAW Labs Support Database
Database design comes before Copilot Studio, because the agent is only as safe as the structure behind it. Two tables: SupportTickets holds one row per ticket, and TicketAudit holds many rows per ticket, one per important action. TicketId is the primary key of SupportTickets, and TicketAudit.TicketId is a foreign key back to it.
→ many ←
audit events
(TicketId links them)
Run this in a sandbox database. Read the constraints line by line: each is a rule the database enforces whatever the agent sends.
CREATE TABLE dbo.SupportTickets (
TicketId INT IDENTITY(1,1) NOT NULL
CONSTRAINT PK_SupportTickets PRIMARY KEY,
TicketRef AS (N'PAW-' + CAST(TicketId AS NVARCHAR(10))) PERSISTED,
EmployeeUPN NVARCHAR(256) NOT NULL,
IssueCategory NVARCHAR(50) NOT NULL
CONSTRAINT DF_SupportTickets_Category DEFAULT (N'VPN'),
Description NVARCHAR(1000) NOT NULL,
Device NVARCHAR(150) NOT NULL,
ErrorMessage NVARCHAR(500) NOT NULL,
BusinessImpact NVARCHAR(500) NOT NULL,
Priority NVARCHAR(20) NOT NULL
CONSTRAINT CK_SupportTickets_Priority
CHECK (Priority IN (N'Low', N'Medium', N'High')),
Status NVARCHAR(30) NOT NULL
CONSTRAINT DF_SupportTickets_Status DEFAULT (N'New')
CONSTRAINT CK_SupportTickets_Status
CHECK (Status IN (N'New', N'In Progress', N'Resolved')),
EscalationFlag BIT NOT NULL
CONSTRAINT DF_SupportTickets_Escalation DEFAULT (0),
RequestKey UNIQUEIDENTIFIER NULL,
CreatedAtUTC DATETIME2(0) NOT NULL
CONSTRAINT DF_SupportTickets_Created DEFAULT (SYSUTCDATETIME()),
UpdatedAtUTC DATETIME2(0) NOT NULL
CONSTRAINT DF_SupportTickets_Updated DEFAULT (SYSUTCDATETIME())
);
-- Only one ticket per RequestKey (NULL keys are ignored)
CREATE UNIQUE INDEX UX_SupportTickets_RequestKey
ON dbo.SupportTickets (RequestKey)
WHERE RequestKey IS NOT NULL;
CREATE TABLE dbo.TicketAudit (
AuditId INT IDENTITY(1,1) NOT NULL
CONSTRAINT PK_TicketAudit PRIMARY KEY,
TicketId INT NOT NULL
CONSTRAINT FK_TicketAudit_SupportTickets
FOREIGN KEY REFERENCES dbo.SupportTickets (TicketId),
ActorUPN NVARCHAR(256) NOT NULL,
ActionType NVARCHAR(50) NOT NULL,
ActionDetails NVARCHAR(500) NULL,
CreatedAtUTC DATETIME2(0) NOT NULL
CONSTRAINT DF_TicketAudit_Created DEFAULT (SYSUTCDATETIME())
);
SQL assigns the next number, so the model can never invent a ticket ID.
Rejects any Priority or Status outside the approved list.
A ticket cannot exist without its required details.
No two tickets can share a RequestKey, which blocks duplicates.
Every audit row must point at a real ticket.
The tables never store passwords, MFA codes, tokens, or other sensitive data a VPN ticket does not need. This is a teaching schema for our sample beta project, not a production design for every organization.
Seed the Sample Data
We seeded four sample rows: one ticket belonging to a different employee (so we can test ownership later), and three for the sample identity demo.user@pawlabs.example. PAW-1042 is the In Progress one. All of this is clearly sample data.
SET IDENTITY_INSERT dbo.SupportTickets ON;
INSERT INTO dbo.SupportTickets
(TicketId, EmployeeUPN, Description, Device, ErrorMessage,
BusinessImpact, Priority, Status, CreatedAtUTC, UpdatedAtUTC)
VALUES
(1039, N'other.user@pawlabs.example',
N'VPN client crashes at sign-in', N'Company laptop',
N'Client stopped responding', N'Cannot start work on time',
N'Medium', N'In Progress',
DATEADD(DAY, -3, SYSUTCDATETIME()), DATEADD(DAY, -2, SYSUTCDATETIME())),
(1040, N'demo.user@pawlabs.example',
N'VPN would not connect after a password change', N'Company laptop',
N'Authentication failed', N'No access to internal apps',
N'High', N'Resolved',
DATEADD(DAY, -10, SYSUTCDATETIME()), DATEADD(DAY, -9, SYSUTCDATETIME())),
(1041, N'demo.user@pawlabs.example',
N'VPN rejects my sign-in on the home network', N'Company laptop',
N'Authentication failed', N'Cannot reach the file share',
N'Medium', N'New',
DATEADD(DAY, -2, SYSUTCDATETIME()), DATEADD(DAY, -2, SYSUTCDATETIME())),
(1042, N'demo.user@pawlabs.example',
N'VPN disconnects about every 20 minutes', N'Company laptop',
N'Connection timed out', N'Unable to access internal applications',
N'High', N'In Progress',
DATEADD(DAY, -1, SYSUTCDATETIME()), DATEADD(HOUR, -5, SYSUTCDATETIME()));
SET IDENTITY_INSERT dbo.SupportTickets OFF;
Here is how those rows look, as ticket cards (illustrative sample data):
Now read it back with the three clauses you will see throughout the article:
SELECT TicketRef, EmployeeUPN, Priority, Status, UpdatedAtUTC FROM dbo.SupportTickets -- where the data lives WHERE EmployeeUPN = N'demo.user@pawlabs.example' -- filter: only this employee ORDER BY UpdatedAtUTC DESC; -- sort: newest update first
Chooses which columns come back.
Filters the rows.
Sorts the result.
Replaces the typed email, making the query reusable and predictable.
Stage 2 and 3: Tool 1, Retrieve My Open VPN Tickets
The employee says "Show me my open VPN tickets." The tool behind that sentence is a procedure with one input, the employee's identity, returning a short list of approved columns. Open means New or In Progress.
CREATE OR ALTER PROCEDURE dbo.usp_GetMyOpenVpnTickets
@EmployeeUPN NVARCHAR(256)
AS
BEGIN
SET NOCOUNT ON;
SELECT TicketRef, IssueCategory, Description,
Priority, Status, EscalationFlag,
CreatedAtUTC, UpdatedAtUTC
FROM dbo.SupportTickets
WHERE EmployeeUPN = @EmployeeUPN
AND IssueCategory = N'VPN'
AND Status IN (N'New', N'In Progress')
ORDER BY UpdatedAtUTC DESC;
END;
@EmployeeUPN is the only value that varies.
WHERE limits results to that employee's open VPN tickets.
Only fields the conversation needs.
Stops row-count messages that can confuse a connector.
Test it in SQL first, before you touch Copilot Studio:
EXEC dbo.usp_GetMyOpenVpnTickets @EmployeeUPN = N'demo.user@pawlabs.example';
Expected sample result: PAW-1042 (In Progress) and PAW-1041 (New). PAW-1040 is resolved and PAW-1039 belongs to someone else, so neither appears.
Set It Up in Copilot Studio: Add the SQL Tool
This is the full walkthrough. We use it once here; the later tools reuse steps 2 to 9 with different values.
Open the existing PAW IT Helpdesk Assistant. Do not create a new one.
Search for the SQL Server connector.
The V2 operations are the current ones; older versions are marked deprecated.
Choose the authentication type deliberately (see the permissions section).
You may need to type the database name as a custom value instead of picking from a list. Parameters appear once the procedure is chosen.
For every parameter, choose whether it is a fixed value, a verified-identity value, or something the agent collects from the conversation.
Business name, "use when" and "do not use for" wording.
With generative orchestration, the agent can now consider the tool.
Use the test pane, then open the activity map to see the tool's inputs and the actual output.
Here is the setup sheet for Tool 1. It is an illustrative layout of what to enter, not a screenshot of Microsoft's screen.
Keep this color legend in mind; we use it in every setup sheet.
A Warning About Identity
In production, the employee identity must come from the verified sign-in context, and the database must enforce ownership. In our sandbox we pass demo.user@pawlabs.example as a fixed sample value; that demonstrates the SQL, not production-grade isolation. We did not hardcode a Copilot Studio expression for the signed-in user's UPN. Check Microsoft's current documentation for the supported identity variable and your agent's authentication settings before you map one.
Give the Tool a Name and Description the Agent Can Use
The orchestrator chooses tools from their names and descriptions. For the wider idea of how agents choose among tools, see our agentic AI guide.
Paste this as the strong description:
Retrieves the signed-in PAW Labs employee's open or in-progress VPN support tickets from the approved SQL ticket database. Use this tool when an employee asks to see their own active VPN tickets or wants to check which VPN issues remain unresolved. Return only the approved ticket fields needed for the conversation. Do not retrieve another employee's tickets, create or update records, or answer questions about procedures that belong to the VPN knowledge guide.
The name describes the task, the description says when to use it and what it must not do, and the inputs and outputs are predictable. Overlapping descriptions across tools make selection less reliable, so give each of the four tools a clearly different purpose. Descriptions guide the model but do not enforce security; SQL permissions and authentication do.
Test Tool 1 in the Test Pane
Illustrative expected behavior, not a recorded test. Run it yourself and check the activity map for the tool's inputs and output.
Tool 2: Look Up One Ticket With an Ownership Check
"What is the status of PAW-1042?" needs a second procedure. Knowing a ticket number must never be enough to read someone else's ticket, so the procedure filters on both the ticket and the owner.
CREATE OR ALTER PROCEDURE dbo.usp_GetVpnTicketStatus
@EmployeeUPN NVARCHAR(256),
@TicketRef NVARCHAR(20)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @TicketId INT =
TRY_CAST(REPLACE(UPPER(LTRIM(RTRIM(@TicketRef))), N'PAW-', N'') AS INT);
SELECT TicketRef, Description, Device, Priority, Status,
EscalationFlag, CreatedAtUTC, UpdatedAtUTC
FROM dbo.SupportTickets
WHERE TicketId = @TicketId
AND EmployeeUPN = @EmployeeUPN
AND IssueCategory = N'VPN';
END;
TRY_CAST returns NULL instead of an error when the text is not a number, so "PAW-banana" matches nothing. A ticket owned by someone else also returns nothing, so the agent cannot tell the difference and the employee learns nothing about other people's tickets. Try three cases:
EXEC dbo.usp_GetVpnTicketStatus N'demo.user@pawlabs.example', N'PAW-1042'; -- 1 row EXEC dbo.usp_GetVpnTicketStatus N'demo.user@pawlabs.example', N'PAW-1039'; -- other owner: 0 rows EXEC dbo.usp_GetVpnTicketStatus N'demo.user@pawlabs.example', N'PAW-9999'; -- missing: 0 rows
1 row returned
Agent reports the status
0 rows (someone else's)
Agent: no matching ticket for your account
0 rows (missing)
Agent: no matching ticket, please check the reference
If the employee does not give a ticket reference, the agent should ask for it, or fall back to Tool 1 to list their open tickets. Test the three cases above through the chat, plus the mixed request "Troubleshoot my VPN and check my ticket", where the activity map should show the knowledge guide and the SQL tool used for separate parts.
Stage 4: Tool 3, Create a VPN Support Ticket
"My VPN keeps disconnecting. Create a support ticket." Now the agent changes something real, so the work is split cleanly: the agent handles the conversation, and the procedure handles the data.
Confirmation Comes First
The agent checks what the employee already said, asks only for what is missing (device, error message, business impact), uses the verified identity, and then shows a summary. Nothing is written until the employee agrees.
Ask only for the missing fields
Issue, device, error, impact, proposed priority
Yes goes forward. No ends the flow with no record. A changed detail loops back to the summary.
Once, with the confirmed values
Report the ticket number only after Created or Duplicate
The summary the employee sees:
Here is the VPN support ticket I am about to create:
Issue: VPN disconnects every 20 minutes
Device: Company laptop
Error: Connection timed out
Business impact: Unable to access internal applications
Priority: High (proposed)
Would you like me to submit this ticket?
The confirmation is tied to the exact values shown, captured in the topic itself.
A casual yes elsewhere in the chat must never trigger the write.
If the employee edits a detail, show a revised summary and ask again.
The Stored Procedure
The procedure takes the identity, the ticket details, the proposed priority, and a RequestKey. The topic creates one RequestKey per confirmed submission and reuses the same value on a retry, which is how SQL tells a retry from a genuinely new request.
CREATE OR ALTER PROCEDURE dbo.usp_CreateVpnSupportTicket
@EmployeeUPN NVARCHAR(256),
@Description NVARCHAR(1000),
@Device NVARCHAR(150),
@ErrorMessage NVARCHAR(500),
@BusinessImpact NVARCHAR(500),
@Priority NVARCHAR(20),
@RequestKey UNIQUEIDENTIFIER
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @NewId INT;
-- 1. Validate required inputs
IF NULLIF(LTRIM(RTRIM(@EmployeeUPN)), N'') IS NULL
OR NULLIF(LTRIM(RTRIM(@Description)), N'') IS NULL
OR NULLIF(LTRIM(RTRIM(@Device)), N'') IS NULL
OR NULLIF(LTRIM(RTRIM(@BusinessImpact)), N'') IS NULL
BEGIN
SELECT N'ValidationFailed' AS ResultStatus,
N'Employee, description, device and business impact are required.' AS ResultMessage,
CAST(NULL AS INT) AS TicketId,
CAST(NULL AS NVARCHAR(20)) AS TicketRef;
RETURN;
END;
IF @Priority NOT IN (N'Low', N'Medium', N'High')
BEGIN
SELECT N'ValidationFailed', N'Priority must be Low, Medium or High.',
CAST(NULL AS INT), CAST(NULL AS NVARCHAR(20));
RETURN;
END;
-- 2. Duplicate check: same employee, same request key
SELECT @NewId = TicketId
FROM dbo.SupportTickets
WHERE RequestKey = @RequestKey AND EmployeeUPN = @EmployeeUPN;
IF @NewId IS NOT NULL
BEGIN
SELECT N'Duplicate', N'This request was already submitted.',
@NewId, N'PAW-' + CAST(@NewId AS NVARCHAR(10));
RETURN;
END;
-- 3. Insert ticket and audit row together
BEGIN TRY
BEGIN TRANSACTION;
INSERT INTO dbo.SupportTickets
(EmployeeUPN, IssueCategory, Description, Device, ErrorMessage,
BusinessImpact, Priority, Status, RequestKey)
VALUES
(LTRIM(RTRIM(@EmployeeUPN)), N'VPN', @Description, @Device,
COALESCE(NULLIF(LTRIM(RTRIM(@ErrorMessage)), N''), N'Not provided'),
@BusinessImpact, @Priority, N'New', @RequestKey);
SET @NewId = SCOPE_IDENTITY();
INSERT INTO dbo.TicketAudit (TicketId, ActorUPN, ActionType, ActionDetails)
VALUES (@NewId, @EmployeeUPN, N'Created',
N'VPN ticket created through the helpdesk assistant');
COMMIT TRANSACTION;
SELECT N'Created', N'Ticket created.',
@NewId, N'PAW-' + CAST(@NewId AS NVARCHAR(10));
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
-- Do not return ERROR_MESSAGE() to the employee
SELECT N'Error', N'The ticket could not be created. Nothing was saved.',
CAST(NULL AS INT), CAST(NULL AS NVARCHAR(20));
END CATCH;
END;
Required fields and priority are checked before any write. Failures return a structured status, not a raw error.
A repeated RequestKey returns Duplicate and the original ticket number.
Status is set to New by SQL, and timestamps come from the database clock.
Ticket and audit row are saved together or not at all.
The CATCH block hides SQL internals from the employee.
SQL generates and returns the ticket ID; the model never invents one.
Test it twice with the same key. The second call should return Duplicate and the original reference, not a new row:
DECLARE @Key UNIQUEIDENTIFIER = NEWID();
EXEC dbo.usp_CreateVpnSupportTicket
@EmployeeUPN = N'demo.user@pawlabs.example',
@Description = N'VPN disconnects every 20 minutes',
@Device = N'Company laptop',
@ErrorMessage = N'Connection timed out',
@BusinessImpact = N'Unable to access internal applications',
@Priority = N'High',
@RequestKey = @Key;
-- Run the same EXEC again with the same @Key: expect ResultStatus = Duplicate
Never let the model write a free-form INSERT from the employee's words. The procedure has a fixed shape with typed parameters, so text typed into the chat can only ever be a value, never a command. For the broader split between flexible AI steps and deterministic ones, see our guide to automating repetitive tasks with AI.
Set It Up in Copilot Studio: Wire the Create Tool Into the Ticket Topic
Add the tool using steps 2 to 9 from Tool 1, then connect it to the Create IT Support Ticket topic from the first tutorial. We reuse that topic rather than build a second path, because two competing creation paths recreate the duplicate-record problem.
Create IT Support Ticket from the first tutorial
Description, device, error, impact. Store each answer in a variable.
Use a unique value (a GUID function if your environment offers one). Set it before the confirmation question so a retry reuses it.
A message node with the variables, then a yes/no question node
Yes: call the create tool. No: send a cancel message and end the topic.
Created or Duplicate: report TicketRef. ValidationFailed: show the message. Error: say nothing was saved.
Disable the Dataverse action on this path so only one system creates tickets
Some builds also offer a setting on the tool to ask the user before running. Use it if you have it, but keep the topic confirmation, because only the topic shows the exact summary. Also check whether your environment lets you limit a write tool to being called from a topic; if the tool is exposed to generative orchestration, its description should say it must only be used after the confirmation step.
What Each ResultStatus Means for the Agent
The Two Ways We Built the Write Step
We built the creation step two ways to see which behaved better, using the same request: "My VPN keeps disconnecting. Create a support ticket." The observations are qualitative notes from our sample beta project, not benchmark results.
Stage 6: Tool 4 (Optional, Advanced), Escalate a Ticket
"This VPN issue is still blocking my work. Can you escalate PAW-1042?" The agent must not update arbitrary columns, so escalation is its own procedure with one job: record that an eligible ticket's owner requested escalation.
CREATE OR ALTER PROCEDURE dbo.usp_EscalateVpnSupportTicket
@EmployeeUPN NVARCHAR(256),
@TicketRef NVARCHAR(20),
@Reason NVARCHAR(300)
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @TicketId INT =
TRY_CAST(REPLACE(UPPER(LTRIM(RTRIM(@TicketRef))), N'PAW-', N'') AS INT);
DECLARE @Status NVARCHAR(30), @Flag BIT;
SELECT @Status = Status, @Flag = EscalationFlag
FROM dbo.SupportTickets
WHERE TicketId = @TicketId
AND EmployeeUPN = @EmployeeUPN -- ownership check
AND IssueCategory = N'VPN';
IF @Status IS NULL
BEGIN
SELECT N'NotFound' AS ResultStatus,
N'No matching VPN ticket was found for your account.' AS ResultMessage,
CAST(NULL AS NVARCHAR(20)) AS TicketRef;
RETURN;
END;
IF @Status = N'Resolved'
BEGIN
SELECT N'NotEligible', N'Resolved tickets cannot be escalated.', @TicketRef;
RETURN;
END;
IF @Flag = 1
BEGIN
SELECT N'AlreadyRequested', N'An escalation was already requested.', @TicketRef;
RETURN;
END;
BEGIN TRY
BEGIN TRANSACTION;
UPDATE dbo.SupportTickets
SET EscalationFlag = 1,
UpdatedAtUTC = SYSUTCDATETIME()
WHERE TicketId = @TicketId AND EmployeeUPN = @EmployeeUPN;
INSERT INTO dbo.TicketAudit (TicketId, ActorUPN, ActionType, ActionDetails)
VALUES (@TicketId, @EmployeeUPN, N'EscalationRequested', LEFT(@Reason, 300));
COMMIT TRANSACTION;
SELECT N'Escalated', N'Escalation request recorded.',
N'PAW-' + CAST(@TicketId AS NVARCHAR(10));
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
SELECT N'Error', N'The escalation could not be recorded. Nothing was changed.',
CAST(NULL AS NVARCHAR(20));
END CATCH;
END;
Employee Confirmation Is Not Business Authorization
Procedure, Direct Action, or Agent Flow?
The connector offers several operations. Here is how we weigh each for PAW Labs, with a relative, illustrative bar for how much of the rule-keeping lives in the database rather than in the model. These bars are our own impression, not a benchmark.
Arbitrary SQL from the model (not recommended)
Direct Insert row (V2) or Update row (V2) on a table
Stored procedure
Agent flow that calls a stored procedure
Simple reads. Keep fields and filters narrow; Get row needs a primary key and your own ownership check.
Simple record changes. Validate fields and restrict columns. Microsoft's docs note inserts and updates do not work on tables with a server-side trigger.
Controlled, parameterized queries where supported. Not available for on-premises or gateway connections, and a wider boundary than named procedures.
Reusable logic and controlled writes. Our choice for all four PAW Labs operations. Needs good validation and EXECUTE-only permissions.
Multi-step processes across several systems. Use when more than one deterministic step must run together.
A stored procedure centralizes validation and business logic in SQL rather than trusting the model to remember every rule. That does not make it automatically secure; it still needs validation, correct permissions, and careful code.
When an Agent Flow Is the Better Choice
Escalating a critical VPN outage may need four things at once: validate the ticket, update the escalation field, write an audit row, and notify the IT support team. A direct tool fits one database operation; a flow fits a sequence. The agent decides the employee wants an escalation, the flow runs the fixed sequence, and SQL enforces the database rules.
Decides: this is an escalation request, and the employee confirmed
Run dbo.usp_EscalateVpnSupportTicket
If the status is Escalated, notify the IT support team
Respond to the agent with two separate results: ticket updated, notification sent or failed
To add a flow as a tool in the standard experience, verify Microsoft's current requirements first. The flow generally needs the trigger designed for agents and a response action that returns to the agent, it must be published, and it must respond within the documented limit. Asynchronous flows follow different rules, so do not mix the two modes.
Stage 5: Authentication and SQL Permissions
Connecting is easy. Deciding whose power the connection carries is the hard part. Authentication tells SQL who is connecting, and permissions decide what that identity may do. They are separate parts of the design, and both need care.
The practical risk: if the connection runs under a powerful maker account, an employee may invoke operations that account can reach even when they should not. Verify in Microsoft's current Copilot Studio authentication documentation how end-user authentication and maker-provided credentials behave for tools in your environment, and never assume the connection runs as the employee.
Verified sign-in supplies the identity value
Chooses the tool; guides but does not enforce
What the connection is allowed to be
EXECUTE on four named procedures
Validation and ownership filters
The last line of defense
Here is the permission design we used. Because the procedures and tables share a schema and owner, SQL's ownership chaining lets a procedure touch the tables for its caller, so the roles need only EXECUTE and no direct table rights.
-- Separate roles per capability CREATE ROLE paw_ticket_read; CREATE ROLE paw_ticket_create; CREATE ROLE paw_ticket_escalate; GRANT EXECUTE ON OBJECT::dbo.usp_GetMyOpenVpnTickets TO paw_ticket_read; GRANT EXECUTE ON OBJECT::dbo.usp_GetVpnTicketStatus TO paw_ticket_read; GRANT EXECUTE ON OBJECT::dbo.usp_CreateVpnSupportTicket TO paw_ticket_create; GRANT EXECUTE ON OBJECT::dbo.usp_EscalateVpnSupportTicket TO paw_ticket_escalate; -- Example for Azure SQL with a Microsoft Entra identity for the agent connection -- (replace the placeholder with your own identity name) CREATE USER [paw-helpdesk-agent] FROM EXTERNAL PROVIDER; ALTER ROLE paw_ticket_read ADD MEMBER [paw-helpdesk-agent]; ALTER ROLE paw_ticket_create ADD MEMBER [paw-helpdesk-agent]; -- Add paw_ticket_escalate only when your escalation process is ready.
If the connector's dropdowns fail to list databases because the identity only has database-level rights, enter the database name as a custom value, as Microsoft's connector documentation suggests. For the wider picture of permissions, monitoring, and governance once an agent goes live, see our guide to production AI agents, and our overview of AI security threats for why data exposure through connected tools deserves planning.
When SQL Fails, Returns Nothing, or Gets Retried
In every failure the agent's job is to report honestly. It must never say a record was created or updated unless the procedure returned a success status and a ticket ID.
Stage 7: Test the SQL-Enabled Agent End to End
This is a plan, not a results table. Every item below is a proposed test with expected behavior. Do not mark any test as passed without evidence. For each one, check these seven places:
One Complete Conversation: From Talk to Task
This is an illustrative sample interaction built from our sample beta project's data, not a recorded test.
And a shorter one for a new issue, with the same discipline:
That reference in the last line is the value the procedure returned. If the procedure returns an error or a validation failure, the agent says so and offers a retry or a handoff to IT.
Common Beginner Mistakes
Fix: EXECUTE on named procedures only
Fix: fixed procedures with typed parameters
Fix: dedicated minimal identity; review sharing
Fix: verified identity, ownership enforced in SQL
Fix: separate roles per capability
Fix: summary plus explicit yes before any write
Fix: validate, plus CHECK constraints
Fix: return only what the conversation needs
Fix: request key with a unique index
Fix: check the returned status and the real row
Fix: one authoritative store per deployment
Fix: audit row in the same transaction
Fix: run all three test groups
Security Checklist Before Connecting Production SQL
Limitations and the Practical Reality Check
This build works as a learning sample, but a working sample is not production readiness. Copilot Studio's SQL integration does not make a database automatically safe, and an AI agent does not replace SQL development or database administration skills.
Premium for Copilot Studio in Microsoft's documentation. Check current guidance and your connector policies.
On-premises connections lose query actions and some stored procedure outputs.
The connector page lists a 110-second limit for a query or procedure, plus throttling that varies by service. Verify current values for your setup.
Renaming a parameter or result column means revisiting the tool. Result sets need unique, non-empty column names.
The model can still pick the wrong tool. Keep descriptions distinct and test mixed requests.
If you cannot show a trustworthy identity mapping end to end, call your build a sandbox.
When not to use this pattern: if you cannot verify employee identity and permissions for sensitive data, or if every step needs a human decision, a simple handoff to the IT team is the better answer.
Rolling It Out in Stages
Lowest risk, because nothing can change
Prove the negative tests before adding writes
With confirmation and duplicate handling
With audit rows and a separate approval path
Add orchestration when several steps truly require it
Check errors, access, audit records, and feedback before expanding
Starting read-only reduces risk because a mistaken lookup shows information but alters nothing, so you learn how the agent behaves before it can change anything.
Frequently Asked Questions
Can Microsoft Copilot Studio connect to SQL Server?
Yes, through the SQL Server connector, which you add to an agent as a tool.
Can a Copilot Studio agent execute SQL queries?
The connector includes Execute a SQL query (V2), but we do not recommend giving an agent free-form query power. Named stored procedures are easier to test and secure, and the query action is not supported for on-premises or gateway connections.
Can an agent create and update SQL records?
Yes, through connector actions or stored procedures, provided the connection has permission. In this build only the create and escalation procedures write, and only after confirmation.
What is the SQL Server connector?
It is Microsoft's connector that exposes database operations such as getting rows, inserting, updating, and running procedures to Copilot Studio, Power Automate, Power Apps, and Logic Apps.
What is Execute stored procedure (V2)?
The connector operation that runs a named stored procedure with the inputs you supply. We use it for all four PAW Labs tools.
Is the SQL Server connector Premium?
Microsoft's connector documentation lists it as Premium for Copilot Studio. Check current licensing for your organization.
Can I use Azure SQL Database?
Yes, and it is our recommended path for a cloud walkthrough. Check firewall, authentication, and cost settings.
Can I connect an on-premises SQL Server?
Yes, through a data gateway, with limitations. Query actions are not supported there, and stored procedure results are restricted, so design for a single result set.
Should I allow an agent to execute arbitrary SQL?
No. Expose narrow, named operations instead.
Why are stored procedures useful?
They keep validation and business rules in the database, give the agent a fixed interface, and let you grant EXECUTE on only what the agent needs. They are not automatically safe; they still need careful code and the right permissions.
Can the agent retrieve only the signed-in employee's tickets?
Only if the identity passed to the procedure is trustworthy and the procedure filters on it. The SQL is simple; the identity mapping is the part to verify in your environment.
How do I prevent an employee from viewing another employee's tickets?
Enforce ownership in the database operation itself, as the lookup procedure does, and treat a prompt instruction as a supplement rather than a safeguard.
Does a SQL connection automatically inherit the employee's permissions?
Not by default. A shareable connection uses the credentials it was created with. Verify how your chosen authentication type and Copilot Studio authentication settings behave.
Should I use SQL or Dataverse for a Copilot Studio agent?
It depends on where your operational data lives and who maintains it. Choose one authoritative store per deployment rather than writing the same tickets to both.
When should I use an agent flow instead of a direct SQL tool?
Use a flow when several deterministic steps must run together, such as update, audit, and notify. Use a direct procedure tool for a single database operation.
Can I keep the existing Dataverse ticket workflow?
Yes. Keep it as it is, treat the SQL version as an alternative backend or a separate controlled extension, and switch the creation topic to one backend at a time.
Final Thoughts
The employee who asked about a disconnecting VPN should now get a real answer: the ticket's status, a confirmed new ticket, or an escalation request, without knowing a single SQL term. Copilot Studio understands the request. The knowledge guide supplies approved troubleshooting steps. SQL stores and retrieves the records, stored procedures enforce the operations, flows coordinate multi-step work, and permissions and confirmation protect the process.
The goal is not to give AI control of the database. The goal is to give the agent a small set of well-defined SQL capabilities that turn a business conversation into a controlled, verifiable task. If you are following the series, keep exploring our Copilot Studio articles, and test every sample in a sandbox before trusting it with real data.
Comments
Post a Comment