Answered

Integration with Azure SQL - Ideas please

(1) Share

Report

Posted on by SS-01071009-0

Hi,

I am working on integrating my custom copilot with Azure SQL data as a knowledge source. Copilot studio seem to have built in integration for that but the problem is that using this integration prompts the user to authenticate every time - not ideal for the end user.

We tried setting up a service principal for this but that didn't affected the authentication prompt the user is getting when the agent tries to query the SQL knowledge source.

Another option I tried is to use Azure AI Search as a knowledge source by indexing the data and using RAG upon it, but this doesn't work in for the purpose as it ranks the results and picks the three most relevant ones to base the response with.

The goal is to add SQL data, as a knowledge source without having the user to authenticate every time.

Any suggestions please?

Many thanks,

Sasho

Categories:

Bot extensibility

I have the same question (0)

All responses (3)

Answers (0)

Sort by

Prasad-MSFT Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

When Azure SQL is added as a knowledge source, queries are executed in the end user’s context, so users are prompted to authenticate when the agent tries to access that data. Even if a service principal is configured, it does not override runtime behavior for knowledge sources, so the prompt still appears.

At the moment, there is no supported way to fully avoid end-user authentication for Azure SQL when it is used as a native knowledge source in Copilot Studio. If your goal is a seamless experience without repeated sign-in prompts, the recommended approach is to move SQL access behind a Tool or middle-tier API such as a Power Automate flow or Azure Function that uses shared/service credentials. That way, the user interacts only with the agent, while the backend handles authentication.

Was this reply helpful? Yes No
Suggested answer

11manish 3,333 on at

Like
a
(0)

Report
Copy link

Link copied!
If your goal is:

"Allow Copilot to answer from Azure SQL data without prompting every user to authenticate."

then I would avoid using Azure SQL as a Knowledge Source and instead expose the SQL data through:

Copilot Action → Power Automate → Azure SQL (simplest), or

Copilot Action → Azure Function/API → Azure SQL (best for enterprise scale).

This approach uses a service account/service principal behind the scenes and provides deterministic SQL retrieval without requiring end users to authenticate to the database each time.

Was this reply helpful? Yes No
Suggested answer

Valantis 6,735 on at

Like
a
(2)

Report
Copy link

Link copied!

Hi @SS-01071009-0,

Azure SQL as a native knowledge source always prompts the end user because queries run in the user's context. Service principal configuration doesn't override this for knowledge sources.

The confirmed working pattern:

1. Remove Azure SQL as a knowledge source entirely

2. Create a Power Automate flow that queries Azure SQL using a SQL Server connector with a service account or managed identity connection (not the user's credentials)

3. Add that flow as a Tool in your Copilot Studio agent

4. In the agent instructions, instruct the agent to call the SQL tool when users ask about data that lives in SQL

The flow connection uses your service principal credentials transparently. Users never see an auth prompt.

For your concern about AI Search ranking only the top 3 results: the Power Automate approach also solves this. Your flow can execute exact SQL queries (SELECT * WHERE condition) and return the full result set, not a ranked subset. The agent then reasons over the complete data the flow returns.

For enterprise scale where SQL queries need to be dynamic (user asks natural language, needs to translate to SQL): add an AI Builder prompt action inside the flow that converts the user's question to a SQL query, executes it against Azure SQL, and returns structured results. This gives you the RAG-like experience without the ranking limitation.

Best regards,

Valantis

✅ If this helped solve your issue, please Accept as Solution so others can find it quickly.

❤️ If it didn’t fully solve it but was still useful, please click “Yes” on “Was this reply helpful?” or leave a Like :).

🏷️ For follow-ups @Valantis.

📝 https://valantisond365.com/

💼 LinkedIn

▶️ YouTube

Was this reply helpful? Yes No