openai-sql-agent by bhushang19 - MCP Server

SQL MCP Integration with Azure OpenAI

Overview

This project demonstrates how to integrate Azure SQL Database with Azure OpenAI using the Model Context Protocol (MCP) server architecture. The solution enables natural language querying of SQL databases through AI agents, providing an intelligent interface for database operations.

Architecture

The project follows a client-server architecture using the Model Context Protocol (MCP):

┌─────────────────┐    ┌──────────────────┐    ┌─────────────────┐
│   Azure OpenAI  │    │   Python Agent   │    │   SQL MCP       │
│   (GPT-4/GPT-3.5│◄──►│   (Client)       │◄──►│   Server        │
│   -turbo)       │    │                  │    │   (Node.js)     │
└─────────────────┘    └──────────────────┘    └─────────────────┘
                                                       │
                                                       ▼
                                              ┌─────────────────┐
                                              │   Azure SQL     │
                                              │   Database      │
                                              └─────────────────┘

Components

Azure OpenAI Service: Provides the AI model (GPT-4 or GPT-3.5-turbo) for natural language processing
Python Agent: Acts as the MCP client, orchestrating communication between AI and SQL server
SQL MCP Server: Node.js-based MCP server that handles SQL database operations
Azure SQL Database: The target database for queries and operations

Features

Natural Language Queries: Convert plain English to SQL queries
Intelligent Database Operations: Execute complex database operations through AI
Secure Connection: Support for SQL authentication with Azure SQL
Read-only Mode: Optional read-only access for safety
Connection Management: Configurable timeouts and certificate handling

Prerequisites

Python 3.8+
Node.js 16+
Azure OpenAI Service account
Azure SQL Database
Required Python packages (see requirements.txt)

⚠️ IMPORTANT: Updated MCP Server Setup

This repository includes an updated version of the SQL MCP server with support for multiple authentication methods. The original Azure SQL-AI-samples repository only supports Azure AD authentication, but this enhanced version adds support for:

SQL Server Authentication (username/password)
Windows Authentication (NTLM)
Azure AD Authentication (original method)

Setup Instructions:

Clone the repository:

git clone <repository-url>
cd sql-mcp-integration

Install Python dependencies:
```
pip install -r requirements.txt
```

Set up the enhanced MCP server:

# Clone the original SQL-AI-samples repository
git clone https://github.com/Azure-Samples/SQL-AI-samples.git repo/SQL-AI-samples

# Navigate to the Node.js MCP server directory
cd repo/SQL-AI-samples/MssqlMcp/Node

# Install dependencies
npm install

After the build is successful, locate the index.js file within the newly created dist folder. Copy and save its fully qualified path. you will need in an upcoming step.


# ⚠️ CRITICAL: Replace the generated index.js with our updated version
# Copy the enhanced index.ts from this repository to replace the original
cp ../../../index.ts src/index.ts

Verify the enhanced features: The enhanced index.ts file includes:
- Multi-authentication support (SQL, Windows, Azure AD)
- Better error handling and debugging
- Configurable connection timeouts
- Enhanced logging for troubleshooting

Configure environment variables: Create a .env file in the root directory:

# Azure OpenAI Configuration
AZURE_OPENAI_API_KEY=your_azure_openai_api_key
AZURE_OPENAI_ENDPOINT=your_azure_openai_endpoint
AZURE_OPENAI_CHAT_DEPLOYMENT=your_deployment_name
AZURE_OPENAI_CHAT_DEPLOYMENT_MODEL=gpt-4
AZURE_OPENAI_API_VERSION=2024-02-15-preview

# SQL Database Configuration
SERVER_NAME=your_sql_server.database.windows.net
DATABASE_NAME=your_database_name
AUTH_METHOD=sql
SQL_USERNAME=your_username
SQL_PASSWORD=your_password
READONLY=false
CONNECTION_TIMEOUT=50
TRUST_SERVER_CERTIFICATE=true

Usage

Basic Usage

Run the main program:

python program.py

The program will:

Connect to the SQL MCP server
Initialize the AI agent with SQL capabilities
Execute the default query: "Show me the first 5 rows from the Customer table"

Custom Queries

To run custom queries, modify the user_input variable in program.py:

user_input = "Find all customers who made purchases in the last 30 days"

System Instructions

The AI agent uses system instructions defined in sql_system_instructions.txt. This file contains guidelines for:

How to interpret natural language queries
SQL best practices
Error handling
Security considerations

Project Structure

sql-mcp-integration/
├── program.py                      # Main application entry point
├── sql_system_instructions.txt     # AI agent system instructions
├── requirements.txt                # Python dependencies
├── index.ts                        # Enhanced MCP server with multi-auth support
├── .env                           # Environment variables (create this)
├── readme.md                      # This documentation
└── repo/
    └── SQL-AI-samples/            # Original Azure SQL-AI-samples repository
        └── MssqlMcp/
            └── Node/
                ├── dist/
                │   └── index.js   # Compiled SQL MCP server (replace with enhanced version)
                ├── src/           # Source code
                ├── package.json   # Node.js dependencies
                └── tsconfig.json  # TypeScript configuration

Configuration Options

Azure OpenAI Settings

AZURE_OPENAI_API_KEY: Your Azure OpenAI API key
AZURE_OPENAI_ENDPOINT: Azure OpenAI service endpoint
AZURE_OPENAI_CHAT_DEPLOYMENT: Deployment name for chat completions
AZURE_OPENAI_CHAT_DEPLOYMENT_MODEL: Model name (e.g., gpt-4, gpt-35-turbo)
AZURE_OPENAI_API_VERSION: API version to use

SQL Database Settings

SERVER_NAME: Azure SQL server name
DATABASE_NAME: Target database name
AUTH_METHOD: Authentication method (sql, azure-ad, etc.)
SQL_USERNAME: Database username
SQL_PASSWORD: Database password
READONLY: Set to "true" for read-only access
CONNECTION_TIMEOUT: Connection timeout in seconds
TRUST_SERVER_CERTIFICATE: Whether to trust server certificate

Security Considerations

Environment Variables: Never commit .env files to version control
Read-only Mode: Use read-only mode for production queries when possible
Connection Security: Ensure proper SSL/TLS configuration
API Key Management: Use Azure Key Vault for API key storage in production

Troubleshooting

Common Issues

Node.js not found: Ensure Node.js is installed and in PATH
Connection timeout: Check network connectivity and firewall settings
Authentication errors: Verify SQL credentials and permissions
MCP server errors: Check the Node.js MCP server logs

License

This project is licensed under the MIT License - see the LICENSE file for details.

bhushang19/openai-sql-agent