Forum Discussion
AK299792458
Mar 18, 2025Copper Contributor
Having Copilot agent talk to on-prem SQL and answer questions
I am trying to see if it is possible to create a Copilot agent that can take a natural language question and pass it to on-prem SQL server as a query and then respond with an answer. For example, if I ask "What was the total sales last month?", I want Copilot to answer "$50,000" or something like that from the on-prem database and table(s) I specified beforehand.
I have a Microsoft Graph Connector (MS SQL Server) that is connected to an on-prem SQL server via a Graph Connector agent. The Graph Connector can access the SQL data and index it.
I have added the Graph Connector as knowledge source in Copilot agent. However, the agent does not seem to be able to answer questions using the data. Instead, it only suggests a generic SQL query that does not reference the database name or the table name. Is what I'm trying to do with Copilot possible?
Thank you.
- BeckettFrostIron Contributor
1. Use Microsoft Power Platform as a bridge
Power Automate + Data Gateway
After creating an automated process with Power Automate that triggers a Copilot request:
Connect to SQL Server using On-Premises Data Gateway.
Execute the SQL query and return the results.
Format the results and pass them to Copilot to generate an answer.
Power Virtual Agents + Custom Connector
Create a Custom Connector that encapsulates the SQL query logic for Copilot to call. 2.
2. Integration via Azure Services
Azure Logic Apps + SQL Connector
Deploy Logic Apps in Azure to query data via SQL Server Connector and expose to Copilot via API.
Azure API Management (APIM)
Encapsulate the local SQL database into a REST API, manage access through APIM, and Copilot gets the data through API calls.
3. Use Semantic Kernel or Custom Plugin
Developing Copilot Plug-ins
Develop custom plug-ins using Microsoft Semantic Kernel or Teams message extensions to handle SQL query logic directly.
LLM Prompt Engineering Optimization
Enable Copilot to generate accurate SQL queries through Prompt Engineering and then execute them through intermediate services.- NormanComsoftCopper Contributor
Hi, Can I ask you for details with Copilot or any official documentation ? I can't find the right connector for Onprem SQL Server with Copilot Studio