Integrating Azure AI Studio with structured data from Azure Synpase Analytics/SQL Serverless

Question

Hello,

we would like to create a copilot in Azure AI Studio that can answer questions from a user provided in natural language and ground this copilot with structured data stored in the storage account of our synapse analytics and corresponding views in SQL serverless. Is it possible to convert the user questions to sql queries using OpenAI, execute these queries on the SQL serverless views, return the results from the serverless pool and convert them back to natural language using OpenAI to provide the answer? We have already succesfully tested this approach with an open-source solution outside of Azure AI studio and using a demo postgres database, but would like to test this with our existing enterprise infrastructure in Synpase. The Azure AI Studio demos use indexing to ground the co-pilot, which has some drawbacks for our usecase as it is not as flexible with aggregations and joins as an sql query.

Best,

Hiob

Answer 1

@Hiob Gebisso - I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer .

Ask: Integrating Azure AI Studio with structured data from Azure Synpase Analytics/SQL Serverless

Solution: We managed to solve this using an azure function for connecting to the DB , and granting the managed identity sql reader rights on an Azure SQL Database. For some reason, it was not possible to give the azure function access to the serverless (or even a dedicated sql pool) , unless the managed identity was set as Microsoft Entra admin for the synapse workspace, which of course is not desirable.

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Answer 2

@Hiob Gebisso - Thanks for the question and using MS Q&A platform.

Yes, it is possible to integrate Azure AI Studio with structured data from Azure Synapse Analytics/SQL Serverless to create a copilot that can answer questions from a user provided in natural language.

To achieve this, you can use OpenAI to convert the user questions to SQL queries, execute these queries on the SQL Serverless views, and return the results from the serverless pool. You can then use OpenAI to convert the results back to natural language to provide the answer to the user.

However, it's important to note that this approach may require some custom development work to integrate with your existing enterprise infrastructure in Synapse. You may need to create custom connectors or APIs to connect Azure AI Studio with your Synapse Analytics and SQL Serverless data sources.

Additionally, you mentioned that the Azure AI Studio demos use indexing to ground the co-pilot, which may not be as flexible with aggregations and joins as an SQL query. In this case, you may need to customize the indexing approach to better suit your use case.

Overall, it is possible to achieve your desired outcome with Azure AI Studio and Synapse Analytics/SQL Serverless, but it may require some custom development work to integrate the two systems and customize the approach to suit your specific needs.

Hope this helps. Do let us know if you any further queries.