Azure SQL Knowledge Source with an On-Prem SQL Server

(1) Share

Report

Posted on by jamesalthoff

I am looking to connect some On-Prem SQL tables to an agent I am building. When I select the Azure SQL knowledge source, my connections created in Power Automate came up. I can see data in the preview of the knowledge source, but when I ask questions to the agent, it cannot find the data I am looking for. I then ask the agent to just tell me anything it knows about the table I connected and it fails. Any ideas on how to get my On-Prem connection to work with the agent?

Categories:

Bot extensibility

I have the same question (0)

All responses (4)

Answers (0)

Suggested answer

Michael E. Gernaey 53,960 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi @jamesalthoff

For onpremise you need the OnPremise Data Gateway so you can connect to the source.

Download On-premises data gateway from Official Microsoft Download Center

As for it not giving you answers this becomes a little more difficult because, it's not a table scanner so it's not just going to spam read your table, you need it to be able to Index your data unto itself so it can answer.

if it fails to be able to index anything, even if the connector is able to connect, you will see zippo.

Are you using Gen AI etc and Agent Orchestration (all of which require some variation supporting RAG (ugg I hate that name)

You may have to look into configuring your Topics and or Instructions to support fall back into a Tool that uses a Flow (Agent Flow or otherwise) that essentially runs a query to the data directly to answer.

But just connecting isn't enough.

For SQL Azure, it should (over time not instantly) index the data so you should be able to get answers, however it can depend on the number or rows, indexes, views you use I mean different things. You could also replicate your OnPremise to SQL Azure and it would work OOB (in theory it would)

If these suggestions help resolve your issue, Please consider Marking the answer as such and also maybe a like.

Thank you!
Sincerely, Michael Gernaey

1 people found this reply helpful.

Was this reply helpful? Yes No
jamesalthoff 10 on at

Like (0)

Report
Copy link

Link copied!

Hi @Michael E. Gernaey,

Thank you for your quick response!

We do already have a gateway set up as I have been using this connection in Power Automate flows.

Yep, we are using Gen AI for responses in this agent.

Is there a way I can check if it is able to index the data? I feel like it has to be indexing at least some of the data since I can see rows in the preview of the knowledge source.

Right now, I don't have any topics configured as I want to try to get the agent to respond to natural language questions about the data (Similarly to Fabric Data Agents).

Thank you for your help!

James

Was this reply helpful? Yes No
AR-01041521-0 4 on at

Like (0)

Report
Copy link

Link copied!

I have a similar issue with executing an agent flow via MS Copilot and running a stored procedure from an on-prem SQL Express server. I have a working data gateway and can run power automate cloud/desktop flows from a Power BI button, but I am unable to run the agent flow (I get a 'bad gateway' error). This is the response (see attached screenshot) I am getting from MS Copilot when I ask about the error and provide screenshots, but with all the changes to the Copilot Studio UI, I'm not sure if its correct / up-to-date

Was this reply helpful? Yes No

AR-01041521-0

4 on at

Like (0)

Report

Copy link

Link copied!

Sorry here is the screenshot and explanation from MS Copilot: What the “BadGateway” means in your specific scenario
When you run the Agent Flow and it calls:
SQL Server → Execute stored procedure (V2)
through an on‑premises data gateway, the request is executed under the Copilot Studio (MCS) runtime, not the standard Power Automate runtime.
Critical distinction

Runtime	On‑prem SQL via gateway
Power Automate cloud flow	✅ Supported
Power BI	✅ Supported
Fabric	✅ Supported
Copilot Studio Agent Flow (MCS)	❌ Not supported

When Agent Flows attempt to retrieve data from SQL Server via gateway, the gateway rejects the call before SQL returns results, and the Copilot runtime surfaces this as:
BadGateway (502)
This happens even when: