Skip to main content

Notifications

Community site session details
Copilot Studio - General
Answered

How to add local on-prem SQL Server as Copilot Studio knowledge skuce?

Like (1) ShareShare
ReportReport
Posted on 26 Dec 2024 12:56:18 by 16
I have lots of structured data in an onprem local MS SQL Server. How do i add it as a ”realtime updated” knowledge data source to copilot studio? I.e to list orders in september 2024 with status= unpaid, or details of a specific orderid.
 
The use case of local SQL server appears sufficiently common but i havent found a standard feature to do it.
 
1. is there a simple standard nocode way to do it?
2. or do i need to use prompt flow and ai search? How?
3. Or should i write a rest api over the sql db backend and just make http requests from a topic?
 
any ideas most appreciated!
 
 
 
 
  • Verified answer
    Artur Stepniak Profile Picture
    1,521 Super User 2025 Season 1 on 27 Dec 2024 at 08:48:38
    How to add local on-prem SQL Server as Copilot Studio knowledge skuce?
    Hello,
     
    Vinoth proposed a good approach overall, just keep in mind that it's going to be quite slow. In general, on-premises data gateway integrations are slower than calling the cloud-hosted database. I'd suggest you, if possible, to switch the datasource to Dataverse or Azure SQL. Otherwise, if you'll have big workloads, it could hang.
     
    In case of any other questions, let me know. If the answer helped you, mark it, so that others can benefit from it.
     
    Best regards,
     
    Artur Stepniak
    Interested in GenAI? Visit my site!
  • Suggested answer
    Vinoth Selvam Profile Picture
    1,527 Super User 2025 Season 1 on 27 Dec 2024 at 08:41:35
    How to add local on-prem SQL Server as Copilot Studio knowledge skuce?
     
    You can try the following ways.
     
    1. Install and configure an On-Premises Data Gateway to connect your local SQL Server with Power Platform. In Copilot Studio (or Power Virtual Agents), create topics that query Dataverse or call a flow in Power Automate. Build a flow that queries your SQL Server for the required data (e.g., orders with status = unpaid). Return the result to the chatbot as part of the flow response.
     
    2. If you have the resources, you can write a REST API for querying your SQL Server and call it directly from a topic in Copilot Studio. Deploy the API on-premises or in the cloud. Use HTTP action in copilot studio or Power automate to make requests to the API and display the results.
     
    Thanks.
  • SaiRT14 Profile Picture
    1,966 Super User 2025 Season 1 on 26 Dec 2024 at 16:00:59
    How to add local on-prem SQL Server as Copilot Studio knowledge skuce?
     
    No code approach:
    Yes - Azure Data Gateway + Power Platform:  Set up an Azure Data Gateway: Install the gateway on your on-premises network to securely connect your local SQL Server with cloud services like Power Automate, Power Apps, or Power BI. Expose Data via Dataverse: Use Power Platform's Dataverse to create tables that sync
    with your SQL Server data. Define a virtual table or import the data on-demand.
     
    let me know if you need more details.
     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard > Copilot Studio - General

#1
Ekta Gupta Profile Picture

Ekta Gupta 4

#2
ricardodesouza Profile Picture

ricardodesouza 2

#2
L-1234567-0 Profile Picture

L-1234567-0 2

Overall leaderboard