Directly connect SQL data source to Copilot Studio

Question

Hello Microsoft Team,

I am developing a Copilot Studio agent that uses an Azure SQL database containing a Lessons Learned view with more than 16,000 records.

I would like to connect the SQL data source directly to Copilot Studio and allow the agent to answer dynamic user questions based on the SQL data.

Users may ask questions about different fields, such as:

• Customer
• Project
• Supplier
• Location
• Status
• Date
• Lesson ID
• Product
• Equipment

The questions are not predefined, so the agent needs to understand the user's question and retrieve the relevant information from the SQL data.

Currently, I am facing issues when trying to use SQL as a direct data source, and the agent does not always return the expected SQL results.

My questions are:

1. Is it possible to directly connect an Azure SQL table/view to Copilot Studio?
2. What is the recommended configuration for this?
3. Can Copilot Studio dynamically query the SQL data based on unpredictable natural-language questions?
4. How can I make sure the agent returns accurate SQL records, counts, and filtered results?

I would appreciate guidance on the recommended approach for connecting and querying the SQL data directly from Copilot Studio.Hello Microsoft Team,

I am developing a Copilot Studio agent that uses an Azure SQL database containing a Lessons Learned view with more than 19,000 records.

I would like to connect the SQL data source directly to Copilot Studio and allow the agent to answer dynamic user questions based on the SQL data.

Users may ask questions about different fields, such as:

• Customer
• Project
• Supplier
• Location
• Status
• Date
• Lesson ID
• Product
• Equipment

The questions are not predefined, so the agent needs to understand the user's question and retrieve the relevant information from the SQL data.

Currently, I am facing issues when trying to use SQL as a direct data source, and the agent does not always return the expected SQL results.

My questions are:

1. Is it possible to directly connect an Azure SQL table/view to Copilot Studio?
2. What is the recommended configuration for this?
3. Can Copilot Studio dynamically query the SQL data based on unpredictable natural-language questions?
4. How can I make sure the agent returns accurate SQL records, counts, and filtered results?

I would appreciate guidance on the recommended approach for connecting and querying the SQL data directly from Copilot Studio.

Answer 1

Yes, Azure SQL can be connected to Copilot Studio using Power Platform connectors and used as a real-time data source. However, for a large dataset (16,000+ records), the recommended approach is to use SQL through a Power Automate flow, custom connector, or real-time knowledge connector rather than relying solely on generative knowledge retrieval.

Ref: https://learn.microsoft.com/en-us/microsoft-copilot-studio/knowledge-graph-vs-power-platform-connectors

Copilot Studio can understand natural-language questions and extract filters such as Customer, Project, Supplier, Location, Status, Date, Product, or Equipment, then invoke SQL queries at runtime to retrieve the relevant records.

To ensure accurate counts, filtered results, and record retrieval, keep SQL as the system of record and return query results directly from the database instead of relying on the LLM to infer answers.

For more advanced semantic search and large-scale Q&A scenarios, consider indexing the SQL data in Azure AI Search and using it as the retrieval layer for Copilot Studio. This typically provides more reliable results for unpredictable user questions.

Ref: https://learn.microsoft.com/en-us/microsoft-copilot-studio/nlu-generative-answers-azure-openai