Forum Discussion

AK299792458's avatar
AK299792458
Copper Contributor
Mar 18, 2025

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.

Getting Started
  • BeckettFrost's avatar
    BeckettFrost
    Iron Contributor
    Mar 27, 2025

    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.

    • NormanComsoft's avatar
      NormanComsoft
      Copper Contributor
      Apr 14, 2025

      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

Resources