Integrate Azure SQL with Copilot Studio for Dynamic Knowledge Retrieval

Copilot Studio agents can tap into structured data stored in Azure SQL databases, turning everyday queries into actionable insights. Instead of writing complex queries, you let the agent figure out which columns to use based on the user’s natural language. In this guide, you’ll connect an Azure SQL database holding task and assignee information, configure a service principal for authentication, and teach the agent how to interpret column values with synonyms and a glossary. The result is a secure, shareable knowledge source that works for everyone on your team.

Scenario: A Task-Focused Project Agent

Your team uses a Copilot agent to answer questions about ongoing work: “What tasks are overdue on Project Alpha?”, “Show me high priority items assigned to Jane.”, “How many hours are estimated for the CI/CD pipeline?”. The underlying data lives in two SQL tables: Tasks and Assignees. You’ll build the agent to retrieve this information without exposing the database directly to end users.

Step 1 – Provision the Azure SQL Environment

Start by creating an Azure SQL database and a logical server that will host it.

Go to the Azure portal and navigate to Azure SQL.
Select Create → SQL Database and choose your subscription, resource group, and database details (e.g., name: ProjectTasksDB).
Under Server, create a new SQL Server (logical server). Choose a server name, location, and authentication method. For this tutorial, we’ll use SQL authentication temporarily, but the final agent will rely on a service principal.
Review and create the resources.

Once the deployment finishes, go to the SQL database overview page and write down the server name (e.g., tasks-server.database.windows.net) and the database name.

Step 2 – Configure the Firewall and Load Sample Tables

Allow Azure Services

Open the SQL server resource in the portal, go to Networking → Public access, and set Public network access to Selected networks. Then check Allow Azure services and resources to access this server. This is mandatory for Copilot Studio (which runs inside Azure) to connect to your database.

Add Your Local IP

While in the firewall rules, add your current IP address so you can connect with a SQL client tool like SSMS or Azure Data Studio to create tables.

Create Tables and Seed Data

Connect to the server using SQL Server authentication (or Microsoft Entra MFA if you prefer). Run the following script to create the Assignees and Tasks tables and insert a few rows.

sqlCreate Tables and Insert Sample Data

CREATE TABLE Assignees (
  assignee_id INT IDENTITY(1,1) PRIMARY KEY,
  full_name NVARCHAR(100) NOT NULL,
  email NVARCHAR(200)
);

CREATE TABLE Tasks (
  task_id INT IDENTITY(1,1) PRIMARY KEY,
  title NVARCHAR(200) NOT NULL,
  project_name NVARCHAR(100),
  assigned_assignee_id INT FOREIGN KEY REFERENCES Assignees(assignee_id),
  priority NVARCHAR(10) CHECK (priority IN ('Low','Medium','High','Critical')),
  status NVARCHAR(20) CHECK (status IN ('Not Started','In Progress','Completed','On Hold')),
  due_date DATE,
  estimated_hours DECIMAL(5,1)
);

INSERT INTO Assignees (full_name, email) VALUES
('Jane Smith','jane@contoso.com'),
('Bob Lee','bob@contoso.com');

INSERT INTO Tasks (title, project_name, assigned_assignee_id, priority, status, due_date, estimated_hours) VALUES
('Design login page','Project Alpha',1,'High','In Progress','2026-06-15',20.0),
('Setup CI/CD pipeline','Project Alpha',2,'Medium','Not Started','2026-07-01',15.0),
('Write API documentation','Project Beta',1,'Low','Completed','2026-06-01',8.0);

Step 3 – Register an Azure AD Application for the Service Principal

Copilot Studio can authenticate to Azure SQL using a service principal (an app registration in Microsoft Entra ID). This creates a single, shareable credential that doesn’t require each user to log in individually.

In the Azure portal, go to App registrations → New registration.
Give the app a name (e.g., Copilot-SQL-Agent), leave the defaults, and register.
On the overview page, copy the Application (client) ID and Directory (tenant) ID.
Navigate to Certificates & secrets → New client secret. Add a description and expiration, then copy the secret value immediately. Store it in a secure location (you won’t be able to retrieve it later).

Step 4 – Create a Database User for the Service Principal

The SQL database needs a user mapped to the Azure AD application. Connect to your database with a SQL client (using an Entra admin account) and run the following commands:

sqlCreate Service Principal User and Grant Read Access

CREATE USER [Copilot-SQL-Agent] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [Copilot-SQL-Agent];

The db_datareader role grants read-only access, which is all the agent needs for knowledge retrieval.

Step 5 – Add Azure SQL Knowledge in Copilot Studio

Now return to Copilot Studio and create or open an existing agent.

Go to the Knowledge tab and click Add knowledge.
Choose Azure SQL.
Enter the Server name (e.g., tasks-server.database.windows.net) and Database name (ProjectTasksDB).
Under Authentication, select Service principal (Microsoft Entra ID application) and provide the tenant ID, client ID, and client secret you saved.
After connecting, select the Tasks and Assignees tables. Copilot Studio will pull their schemas automatically.
Click Add to agent.

The tables now appear in your agent’s knowledge list.

Step 6 – Fine-Tune Column Interpretation with Synonyms and Descriptions

To help the agent understand what each column means, you can add synonyms (alternate names) and descriptions. This improves accuracy when users ask questions in natural language.

In the Knowledge tab, click the three dots next to the Azure SQL source and choose Edit.
Expand a table’s column list. For each column that the agent will frequently query, provide:

Synonyms – other terms users might say.
Description – a short clarification of the column’s purpose.

For example, for task_id add synonyms: Task ID, Ticket ID, Work Item. For priority, add Urgency, Importance. For assigned_assignee_id, add Assignee, Assigned To, Owner.

Add descriptions like “The date the task is expected to be completed” for due_date and “The person responsible for completing the task” for assigned_assignee_id.

Step 7 – Build a Data Glossary for Consistent Value Interpretation

Values stored in the database (e.g., In Progress) might differ from how users express them (e.g., “active” or “in progress”). The data glossary allows you to define these mappings.

Open the Edit screen of the knowledge source and scroll to Data glossary. Add entries for columns that have coded or fixed values. For the status column, you could map:

Not Started → new, not started
In Progress → active, in progress
Completed → done, finished
On Hold → paused, on hold

Similarly, for priority, map Low, Medium, High, Critical to common synonyms.

Now a user asking “Show me active high-priority tasks” will be correctly matched to tasks with status = ‘In Progress’ and priority = ‘High’.

Security and Performance Considerations

Always use a service principal for production agents. It avoids embedding user credentials in skills and lets you revoke access centrally.
Grant the least privilege needed. db_datareader is sufficient for knowledge; avoid db_owner or write permissions.
Index your columns. Azure SQL will benefit from indexes on columns often used in WHERE clauses (e.g., project_name, assigned_assignee_id, status).
Respect SQL resource limits. Copilot Studio queries the database in real time. If your tables are very large, consider adding views that limit rows or pre-aggregate data.
Enable Azure Defender or Auditing if your data is sensitive.

Common Mistakes and How to Fix Them

Issue	Likely Cause	Solution
Agent returns “I couldn’t find anything”	The service principal user lacks `db_datareader` or the SQL server firewall blocks Azure services	Verify the user exists and has read role; check that “Allow Azure services” is enabled
Connection test fails during setup	Incorrect server name, database name, or service principal secret	Confirm the server FQDN, database name, and re‑create the client secret if expired
Agent misinterprets column values (e.g., “In Progress” matched to “Not Started”)	Missing glossary entries for value synonyms	Add glossary mappings for each coded value
Slow responses or timeouts	Unindexed columns or very large tables	Add indexes on queried columns; consider a filtered view

Final Recommendations

Connecting an Azure SQL database as a knowledge source in Copilot Studio is a powerful way to make structured data accessible through natural language. The service principal approach keeps authentication clean and shareable. Invest time in synonyms, descriptions, and the data glossary — they turn a basic connection into a truly intelligent agent that understands your users’ terminology. Start with a small, well‑indexed table, test thoroughly, and expand as you refine the mappings.

References

Original tutorial by Matthew Devaney: Copilot Studio: Connect An Azure SQL Database As Knowledge
Microsoft Learn: SQL Server Power Platform connector – authentication options
Microsoft Learn: Manage Azure SQL Database firewall rules
Microsoft Learn: Copilot Studio knowledge sources overview