Skip to main content
Azure OpenAI

Connecting Azure Database to Azure OpenAI for Intelligent Business Insights

At On Point BI, we empower businesses to transform raw data into actionable intelligence. As organizations increasingly adopt AI to enhance decision-making, integrating Azure databases with Azure OpenAI offers a powerful way to unlock natural language querying and advanced analytics. This blog post explores how to connect an Azure SQL Database to Azure OpenAI, enabling your team to interact with data using conversational AI, streamline workflows, and deliver smarter business insights.

Why Integrate Azure SQL Database with Azure OpenAI?

Azure SQL Database is a robust, fully managed relational database service that supports structured data storage and querying. Azure OpenAI, on the other hand, provides access to advanced language models like GPT-4o, which excel at processing natural language and generating context-aware responses. By combining these services, you can:

  • Enable Natural Language Queries: Allow non-technical users to query databases using plain English, reducing the need for SQL expertise.
  • Enhance Business Intelligence: Generate summaries, reports, or simulations from structured data with AI-driven insights.
  • Streamline Operations: Automate tasks like data analysis, customer support, and report generation.
  • Leverage Azure’s Security: Benefit from Azure’s enterprise-grade security, including managed identities and role-based access control (RBAC).

This integration is particularly valuable for businesses looking to make data accessible to all team members, not just data scientists or developers. Let’s dive into how On Point BI can help you set this up.

Prerequisites

Before you begin, ensure you have:

  1. An Azure subscription with access to Azure SQL Database and Azure OpenAI.
  2. An Azure SQL Database populated with your data (e.g., a sales or customer dataset).
  3. An Azure OpenAI resource with a deployed model (e.g., GPT-4o or text-embedding-ada-002).
  4. Azure Data Studio or another SQL client for querying.
  5. A development environment with Python, Node.js, or another language to interact with Azure services.
  6. Necessary permissions to configure role assignments and managed identities.

Step-by-Step Guide to Connecting Azure SQL Database to Azure OpenAI

Here’s a streamlined process to integrate your Azure SQL Database with Azure OpenAI, leveraging the Retrieval Augmented Generation (RAG) pattern for enhanced responses.

Step 1: Set Up Your Azure SQL Database
  1. Create or Use an Existing Database: In the Azure Portal, navigate to “SQL Databases” and create a new database or use an existing one. For this example, assume you have a database named SalesDB with a table CustomerOrders.
  2. Gather Connection Details: Note the server name, database name, and credentials (username and password) from the Azure Portal under the “Connection strings” tab.
  3. Enable Managed Identity: For secure access, enable a system-assigned managed identity for your Azure SQL Database. This allows Azure OpenAI to authenticate without hard coding credentials.
Step 2: Create an Azure OpenAI Resource
  1. Deploy an Azure OpenAI Resource: In the Azure Portal, search for “Azure OpenAI” and create a new resource. Choose a region (e.g., East US) and a pricing tier (e.g., Standard S0).
  2. Deploy a Model: In Azure AI Studio, navigate to “Model Deployments” and deploy a model like gpt-4o. Note the deployment name and endpoint URL.
  3. Obtain API Credentials: Under “Keys and Endpoint,” copy the API key and endpoint URL. Alternatively, use a system-assigned managed identity for secure authentication.
Step 3: Configure Azure AI Search for Data Indexing

Azure OpenAI often uses Azure AI Search to index and retrieve data efficiently. This step is optional but recommended for large datasets.

  1. Create an Azure AI Search Resource: In the Azure Portal, create a search service and enable system-assigned managed identity.
  2. Index Your Data: Upload your data (e.g., from Azure Blob Storage or Azure SQL Database) to an Azure AI Search index. Use the data preparation script available on GitHub to convert structured data to text if needed.
  3. Connect to Azure OpenAI: In Azure AI Studio, under “Add Your Data,” select your Azure AI Search resource and configure the connection using the API key or managed identity.
Step 4: Enable Natural Language Querying

To allow users to query the database in natural language, you can use Azure OpenAI to convert user prompts into SQL queries. Here’s a sample Python script using the LangChain framework to achieve this:

import os
from langchain.sql_database import SQLDatabase
from langchain.llms import AzureOpenAI
from langchain.agents import create_sql_agent
from langchain.agents.agent_toolkits import SQLDatabaseToolkit

# Configure Azure SQL Database connection
db = SQLDatabase.from_uri(
    "mssql+pyodbc://<username>:<password>@<server>.database.windows.net/<database>?driver=ODBC+Driver+17+for+SQL+Server"
)

# Configure Azure OpenAI
os.environ["AZURE_OPENAI_API_KEY"] = "<your-api-key>"
os.environ["AZURE_OPENAI_ENDPOINT"] = "<your-endpoint>"
os.environ["AZURE_OPENAI_DEPLOYMENT_NAME"] = "<your-deployment-name>"
os.environ["AZURE_OPENAI_API_VERSION"] = "2024-05-01-preview"

llm = AzureOpenAI(
    deployment_name=os.environ["AZURE_OPENAI_DEPLOYMENT_NAME"],
    api_version=os.environ["AZURE_OPENAI_API_VERSION"]
)

# Create SQL agent
toolkit = SQLDatabaseToolkit(db=db, llm=llm)
agent = create_sql_agent(llm=llm, toolkit=toolkit, verbose=True)

# Query the database
query = "What are the top 5 customers by order value in 2024?"
result = agent.run(query)
print(result)

Instructions:

  1. Install required packages: pip install langchain pyodbc azure-openai.
  2. Replace <username>, <password>, <server>, <database>, <your-api-key>, <your-endpoint>, and <your-deployment-name> with your actual credentials.
  3. Run the script to execute natural language queries against your Azure SQL Database. The agent will convert the query (e.g., “What are the top 5 customers by order value in 2024?”) into a SQL query and return the results.
Step 5: Secure the Integration
  • Use Managed Identities: Avoid hardcoding API keys by using system-assigned managed identities for authentication between Azure OpenAI, Azure AI Search, and Azure SQL Database. Assign roles like “Contributor” or “Reader” as needed.

  • Enable CORS: If accessing data from a web app, enable Cross-Origin Resource Sharing (CORS) on your Azure Blob Storage or other data sources.Configure Private Endpoints: For enhanced security, set up private endpoint connections to restrict access to your Azure resources.

  • Configure Private Endpoints: For enhanced security, set up private endpoint connections to restrict access to your Azure resources.

Step 6: Test and Deploy
  1. Test in Azure AI Studio: Use the “Playground” in Azure AI Studio to test your model’s responses with sample queries.
  2. Deploy a Web App: Create a web app using Azure App Service to allow users to interact with the database via a front-end interface. Use the Azure OpenAI web app template for quick deployment.

  3. Monitor and Optimize: Monitor usage in the Azure Portal and optimize your Azure AI Search index for semantic or vector search to improve performance.

Best Practices

  • Optimize Data for AI: Convert structured data (e.g., tables) to unstructured text for better compatibility with Azure OpenAI. Use the data preparation script from GitHub if needed.

  • Manage Token Usage: Azure OpenAI processes text in tokens, so keep prompts concise to reduce latency and costs.

  • Regularly Update Schema: If your database schema changes, update the Azure AI Search index or backend service to reflect the new structure.

  • Leverage RAG: Use Retrieval Augmented Generation to ground AI responses in your data, ensuring accurate and context-aware outputs.

How On Point BI Can Help

At On Point BI, we specialize in building data-driven solutions that empower your business. Our team can:

  • Design and deploy Azure SQL Database and Azure OpenAI integrations tailored to your needs.
  • Develop custom web apps for natural language querying and reporting.
  • Optimize your AI workflows with advanced analytics and visualizations.
  • Ensure secure, scalable, and cost-effective deployments.

Ready to unlock the power of AI-driven business intelligence? Contact us at www.onpointbi.com to learn how we can transform your data into actionable insights.

Conclusion

Connecting Azure SQL Database to Azure OpenAI opens up new possibilities for interacting with your data. By enabling natural language queries, you can make data accessible to all team members, streamline operations, and drive smarter decision-making. Follow the steps above, leverage On Point BI’s expertise, and start building intelligent applications today.

For more resources, check out: