Answered

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

(1) Share

Report

Posted on by MU-24120506-0

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!

Categories:

General topics

I have the same question (0)

All responses (3)

Answers (1)

SaiRT14 1,990 Super User 2025 Season 2 on at

Like (0)

Report

Hi MU-24120506-0,

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.

Was this reply helpful? Yes No
Suggested answer

Vinoth Selvam 1,590 Super User 2025 Season 2 on at

Like (1)

Report

Hi MU-24120506-0,

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.

Was this reply helpful? Yes No
Verified answer

Artur Stepniak 1,539 Super User 2025 Season 2 on at

Like (1)

Report

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!

Was this reply helpful? Yes No