# Azure SQL MCP Server — Complete Guide

A Model Context Protocol (MCP) server that connects Microsoft Copilot Studio to your Azure SQL Database. Supports 12 tools for querying, CRUD operations, schema inspection, search, and chart visualization.

---

## Table of Contents

1. [Prerequisites](#1-prerequisites)
2. [Installation](#2-installation)
3. [Configuration](#3-configuration)
4. [Code Fixes (Important)](#4-code-fixes)
5. [Running the Server](#5-running-the-server)
6. [Exposing to the Internet](#6-exposing-to-the-internet)
7. [Copilot Studio Setup](#7-copilot-studio-setup)
8. [All 12 Tools Reference](#8-all-12-tools-reference)
9. [CRUD Operations](#9-crud-operations)
10. [Chart Visualization](#10-chart-visualization)
11. [Example Queries & Use Cases](#11-example-queries--use-cases)
12. [Production Deployment](#12-production-deployment)
13. [API Key Authentication](#13-api-key-authentication)
14. [Troubleshooting](#14-troubleshooting)
15. [Security Checklist](#15-security-checklist)

---


## Architecture

![alt text](architecture.png)

## 1. Prerequisites

- **Python 3.8+**
- **ODBC Driver 18 for SQL Server** — [Download here](https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server)
- **Azure SQL Database** with server hostname, database name, username, and password
- **Cloudflare Tunnel** (for local testing) — `winget install Cloudflare.cloudflared`

### Install ODBC Driver

**Windows:** Download and install from the link above.

**macOS:**
```bash
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
brew install msodbcsql18 mssql-tools18
```

**Linux (Ubuntu/Debian):**
```bash
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18
```

---

## 2. Installation

```bash
git clone <your-repo-url>
cd azure-sql-mcp-server
python -m venv venv
venv\Scripts\activate          # Windows
# source venv/bin/activate     # macOS/Linux
pip install -r requirements.txt
```

---

## 3. Configuration

Create a `.env` file in the project root:

```env
AZURE_SQL_SERVER=your-server.database.windows.net
AZURE_SQL_DATABASE=your-database-name
AZURE_SQL_USERNAME=your-username
AZURE_SQL_PASSWORD=your-password
AZURE_SQL_DRIVER=ODBC Driver 18 for SQL Server
```

> **Never commit `.env` to version control.** Add it to `.gitignore`.

---

## 4. Code Fixes

The MCP SDK requires specific configuration. Apply these 3 fixes to `azure_sql_mcp.py`:

### Fix 1: Lifespan function signature

```python
# ❌ BEFORE
@asynccontextmanager
async def app_lifespan():

# ✅ AFTER — FastMCP passes the server instance
@asynccontextmanager
async def app_lifespan(server: FastMCP):
```

### Fix 2: Host and port on the constructor

```python
# ❌ BEFORE
mcp = FastMCP("azure_sql_mcp", lifespan=app_lifespan)

# ✅ AFTER — host/port go on the constructor, NOT on run()
mcp = FastMCP("azure_sql_mcp", host="0.0.0.0", port=8000, lifespan=app_lifespan)
```

### Fix 3: HTTP transport

```python
# ❌ BEFORE
if __name__ == "__main__":
    mcp.run()

# ✅ AFTER — streamable-http (with hyphen) for Copilot Studio
if __name__ == "__main__":
    mcp.run(transport="streamable-http")
```

---

## 5. Running the Server

```bash
cd azure-sql-mcp-server
.\venv\Scripts\Activate.ps1       # Windows
python azure_sql_mcp.py
```

You should see:

```
INFO: Initializing Azure SQL MCP server...
INFO: Database connection established
INFO: Database connection verified
INFO: Uvicorn running on http://0.0.0.0:8000 (Press CTRL+C to quit)
```

MCP endpoint: `http://localhost:8000/mcp`

---

## 6. Exposing to the Internet

Copilot Studio needs a public HTTPS URL. **Use Cloudflare Tunnel** (free, no signup).

> **Why not ngrok?** ngrok's free tier shows a browser warning page (`ERR_NGROK_6024`) that blocks API clients like Copilot Studio.

### Steps

1. Install (one-time):
   ```bash
   winget install Cloudflare.cloudflared
   ```
   Close and reopen your terminal after install.

2. In a **new terminal** (keep the server running in the first one):
   ```bash
   cloudflared tunnel --url http://localhost:8000
   ```

3. Copy the URL from the output:
   ```
   https://electronic-annie-jose-spoken.trycloudflare.com
   ```

4. Your MCP server URL for Copilot Studio:
   ```
   https://electronic-annie-jose-spoken.trycloudflare.com/mcp
   ```

> URLs change on restart. For a permanent URL, deploy to Azure App Service (see [Production Deployment](#12-production-deployment)).

---

## 7. Copilot Studio Setup

### Step 1: Add the MCP Server

1. Go to [Copilot Studio](https://copilotstudio.microsoft.com)
2. Open your agent → **Tools** → **Add a tool** → **New tool** → **Model Context Protocol**
3. Fill in:

| Field | Value |
|-------|-------|
| **Server name** | `azure-sql-mcp` |
| **Server description** | `Azure SQL Database for querying tables, retrieving data, inspecting schema, and visualizing data with charts` |
| **Server URL** | `https://YOUR-CLOUDFLARE-URL.trycloudflare.com/mcp` |
| **Authentication** | None (local testing) or API key (production) |

4. Click **Create** → **Next** → **Create new connection** → **Add and configure**

### Step 2: Configure the Agent (Overview tab)

Both fields are **required** — the agent won't work without them.

**Description:**
```
Azure SQL Database Assistant that queries tables, retrieves data, inspects schema, manages records, and visualizes data with charts.
```

**Instructions (click Edit):**
```
You are an Azure SQL Database assistant. You help users interact with their database using natural language.

Your capabilities:
- List tables and describe their schema
- Execute SQL queries (SELECT, INSERT, UPDATE, DELETE)
- Search for data across table columns
- Create and drop tables
- Visualize data as charts (bar, pie, line, doughnut)
- Provide database information and statistics

Rules:
- Always use the MCP tools to answer database questions - never guess table names or data
- Before querying, list tables first if you don't know the schema
- Use parameterized queries when possible
- Ask for confirmation before UPDATE, DELETE, or DROP operations
- Format results clearly for the user
- When asked for charts, pick the most appropriate chart type based on the data
```

### Step 3: Publish and Test

Click **Publish**, wait a minute, then test with prompts like:

- *"Show me all tables in the database"*
- *"What's the schema of the customers table?"*
- *"Get the first 10 rows from orders"*
- *"How many records are in each table?"*
- *"Show me sales by region as a bar chart"*

---

## 8. All 12 Tools Reference

| # | Tool | What it does | Read-Only |
|---|------|-------------|-----------|
| 1 | `azure_sql_execute_query` | Run any SQL query | No |
| 2 | `azure_sql_list_tables` | List all tables with row counts | Yes |
| 3 | `azure_sql_get_table_schema` | Get column details for a table | Yes |
| 4 | `azure_sql_get_table_data` | Fetch paginated table data | Yes |
| 5 | `azure_sql_get_database_info` | Database metadata & stats | Yes |
| 6 | `azure_sql_create_record` | INSERT a new row | No |
| 7 | `azure_sql_update_record` | UPDATE existing rows (WHERE required) | No |
| 8 | `azure_sql_delete_record` | DELETE rows (WHERE required) | No |
| 9 | `azure_sql_search` | Search text across columns | Yes |
| 10 | `azure_sql_create_table` | Create a new table | No |
| 11 | `azure_sql_drop_table` | Drop a table | No |
| 12 | `azure_sql_visualize_data` | Generate charts (Adaptive Card) | Yes |

All tools support both `markdown` and `json` response formats.

---

## 9. CRUD Operations

### CREATE — `azure_sql_create_record`

```json
{
  "table_name": "customers",
  "data": {
    "name": "John Doe",
    "email": "john@example.com",
    "city": "Seattle"
  }
}
```

Natural language: *"Add a new customer named John Doe with email john@example.com"*

---

### READ — `azure_sql_execute_query`

```json
{
  "query": "SELECT * FROM customers WHERE city = ?",
  "params": ["Seattle"],
  "response_format": "markdown"
}
```

Natural language: *"Show me all customers from Seattle"*

---

### UPDATE — `azure_sql_update_record`

```json
{
  "table_name": "customers",
  "data": { "email": "newemail@example.com" },
  "where": { "id": 123 }
}
```

**Safety:** WHERE clause is **required** — prevents accidental mass updates.

Natural language: *"Update customer 123's email to newemail@example.com"*

---

### DELETE — `azure_sql_delete_record`

```json
{
  "table_name": "customers",
  "where": { "id": 999 }
}
```

**Safety:** WHERE clause is **required** — prevents accidental mass deletion.

Natural language: *"Delete customer with ID 999"*

---

### SEARCH — `azure_sql_search`

```json
{
  "table_name": "customers",
  "search_term": "john",
  "columns": ["name", "email"],
  "limit": 50
}
```

If `columns` is omitted, all text columns are searched automatically.

Natural language: *"Search for 'john' in customers table"*

---

### CREATE TABLE — `azure_sql_create_table`

```json
{
  "table_name": "employees",
  "columns": [
    { "name": "id", "type": "INT", "primary_key": true, "identity": true },
    { "name": "name", "type": "NVARCHAR(100)", "nullable": false },
    { "name": "email", "type": "NVARCHAR(255)" },
    { "name": "hire_date", "type": "DATE" },
    { "name": "salary", "type": "DECIMAL(10,2)" }
  ],
  "if_not_exists": true
}
```

Column properties: `name`, `type`, `primary_key`, `identity`, `nullable`, `default`.

Natural language: *"Create a customers table with id, name, email, and phone columns"*

---

### DROP TABLE — `azure_sql_drop_table`

```json
"old_backup_table"
```

Uses `DROP TABLE IF EXISTS` — won't error if table doesn't exist.

Natural language: *"Drop the old_backup_table"*

---

## 10. Chart Visualization

### Tool: `azure_sql_visualize_data`

Generates charts via QuickChart API and returns Adaptive Cards that render directly in Copilot Studio.

### Parameters

| Parameter | Required | Default | Description |
|-----------|----------|---------|-------------|
| `query` | Yes | — | SQL query to get chart data |
| `chart_type` | No | `bar` | bar, pie, line, doughnut, radar, polarArea |
| `title` | Yes | — | Chart title |
| `label_column` | Yes | — | Column for labels (X-axis / slices) |
| `value_column` | Yes | — | Column for values (Y-axis / data) |
| `width` | No | 800 | 400–1200 pixels |
| `height` | No | 500 | 300–800 pixels |

### Chart Types

| Type | Best For |
|------|----------|
| `bar` | Comparing categories (sales by region) |
| `pie` | Proportions (market share) |
| `line` | Trends over time (monthly revenue) |
| `doughnut` | Modern proportions (budget breakdown) |
| `radar` | Multi-dimensional data (performance metrics) |
| `polarArea` | Cyclical data (seasonal patterns) |

### Example: Bar Chart

```json
{
  "query": "SELECT region, SUM(sales) as total FROM orders GROUP BY region ORDER BY total DESC",
  "chart_type": "bar",
  "title": "Sales by Region",
  "label_column": "region",
  "value_column": "total"
}
```

### Example: Pie Chart

```json
{
  "query": "SELECT category, COUNT(*) as count FROM products GROUP BY category",
  "chart_type": "pie",
  "title": "Products by Category",
  "label_column": "category",
  "value_column": "count"
}
```

### Example: Line Chart (Trends)

```json
{
  "query": "SELECT FORMAT(order_date, 'yyyy-MM') as month, SUM(total) as revenue FROM orders WHERE order_date >= DATEADD(month, -6, GETDATE()) GROUP BY FORMAT(order_date, 'yyyy-MM') ORDER BY month",
  "chart_type": "line",
  "title": "Revenue Trend (Last 6 Months)",
  "label_column": "month",
  "value_column": "revenue"
}
```

### What Copilot Displays

Every chart returns an Adaptive Card with the chart image, plus automatic statistics: Total, Average, Highest (with label), Lowest (with label), and Data Points count.

### Best Practices for Charts

- **Bar charts:** 3–15 categories, sorted by value DESC
- **Pie charts:** 3–8 slices, sorted by value DESC
- **Line charts:** 5–50 points, sorted by date/time ASC
- Use clear column aliases: `SUM(sales) as total_sales` not `SUM(s)`
- Test your query with `azure_sql_execute_query` first, then visualize

---

## 11. Example Queries & Use Cases

### Data Exploration

```
"Show me all tables in the database"
"What columns does the orders table have?"
"Show me 10 sample products"
```

### Data Analysis

```
"How many orders were placed last month?"
"Which customer has the highest order total?"
"What's the average product price by category?"
"Show me sales trends for the last 6 months"
```

### Data Quality

```
"Are there any customers with missing email addresses?"
"Find duplicate customer records"
"Show me orders with invalid status values"
```

### Parameterized Queries (SQL injection safe)

```json
{
  "query": "SELECT * FROM customers WHERE city = ? AND status = ?",
  "params": ["Seattle", "active"],
  "response_format": "json"
}
```

### Aggregation

```json
{
  "query": "SELECT category, COUNT(*) as product_count, AVG(price) as avg_price FROM products GROUP BY category ORDER BY product_count DESC"
}
```

### Joins

```json
{
  "query": "SELECT c.customer_name, COUNT(o.order_id) as order_count FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_name ORDER BY order_count DESC"
}
```

### Time-Based Queries

```sql
-- Daily (last 30 days)
SELECT CAST(order_date AS DATE) as day, SUM(total) as revenue
FROM orders WHERE order_date >= DATEADD(day, -30, GETDATE())
GROUP BY CAST(order_date AS DATE) ORDER BY day

-- Monthly
SELECT FORMAT(order_date, 'yyyy-MM') as month, SUM(total) as revenue
FROM orders WHERE YEAR(order_date) = YEAR(GETDATE())
GROUP BY FORMAT(order_date, 'yyyy-MM') ORDER BY month

-- Quarterly
SELECT 'Q' + CAST(DATEPART(quarter, order_date) AS VARCHAR) as quarter, SUM(total) as revenue
FROM orders WHERE YEAR(order_date) = YEAR(GETDATE())
GROUP BY DATEPART(quarter, order_date) ORDER BY DATEPART(quarter, order_date)
```

### Complete CRUD Workflow

1. **Create table** → `azure_sql_create_table`
2. **Insert data** → `azure_sql_create_record`
3. **Search** → `azure_sql_search`
4. **Update** → `azure_sql_update_record`
5. **Visualize** → `azure_sql_visualize_data`
6. **Clean up** → `azure_sql_delete_record` or `azure_sql_drop_table`

---

## 12. Production Deployment

### Azure App Service

1. Create deployment files:

   `runtime.txt`:
   ```
   python-3.11
   ```

   `Procfile`:
   ```
   web: python azure_sql_mcp.py
   ```

2. Deploy:

   ```bash
   az login
   az group create --name mcp-servers --location eastus
   az appservice plan create --name mcp-plan --resource-group mcp-servers --sku B1 --is-linux
   az webapp create --name azure-sql-mcp --resource-group mcp-servers --plan mcp-plan --runtime "PYTHON:3.11"

   az webapp config appsettings set --name azure-sql-mcp --resource-group mcp-servers --settings \
       AZURE_SQL_SERVER="your-server.database.windows.net" \
       AZURE_SQL_DATABASE="your-database" \
       AZURE_SQL_USERNAME="your-username" \
       AZURE_SQL_PASSWORD="your-password" \
       AZURE_SQL_DRIVER="ODBC Driver 18 for SQL Server" \
       PORT="8000"

   az webapp up --name azure-sql-mcp --resource-group mcp-servers
   ```

3. Permanent URL: `https://azure-sql-mcp.azurewebsites.net/mcp`

### Docker

```dockerfile
FROM python:3.11-slim

RUN apt-get update && apt-get install -y curl apt-transport-https gnupg2 \
    && curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - \
    && curl https://packages.microsoft.com/config/debian/11/prod.list > /etc/apt/sources.list.d/mssql-release.list \
    && apt-get update \
    && ACCEPT_EULA=Y apt-get install -y msodbcsql18 \
    && apt-get clean && rm -rf /var/lib/apt/lists/*

WORKDIR /app
COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt
COPY azure_sql_mcp.py .

EXPOSE 8000
CMD ["python", "azure_sql_mcp.py"]
```

```bash
docker build -t azure-sql-mcp .
docker run -p 8000:8000 --env-file .env azure-sql-mcp
```

---

## 13. API Key Authentication

For production, add API key middleware to protect your server.

### Step 1: Add middleware to `azure_sql_mcp.py`

Add this above the Pydantic Models section:

```python
from starlette.middleware.base import BaseHTTPMiddleware
from starlette.requests import Request
from starlette.responses import JSONResponse

MCP_API_KEY = os.getenv("MCP_API_KEY", "")

class APIKeyMiddleware(BaseHTTPMiddleware):
    async def dispatch(self, request: Request, call_next):
        if not MCP_API_KEY:
            return await call_next(request)
        api_key = request.headers.get("X-API-Key", "")
        if api_key != MCP_API_KEY:
            return JSONResponse(status_code=401, content={"error": "Invalid API key"})
        return await call_next(request)
```

### Step 2: Update the entry point

```python
if __name__ == "__main__":
    app = mcp.streamable_http_app()
    app.add_middleware(APIKeyMiddleware)

    import uvicorn
    port = int(os.getenv("PORT", 8000))
    uvicorn.run(app, host="0.0.0.0", port=port)
```

### Step 3: Add to `.env`

```env
MCP_API_KEY=your-secret-api-key-here
```

Generate a strong key: `python -c "import secrets; print(secrets.token_urlsafe(32))"`

### Step 4: Configure in Copilot Studio

| Field | Value |
|-------|-------|
| **Authentication type** | API key |
| **Type** | Header |
| **Header name** | `X-API-Key` |

Enter the same key value when creating the connection.

---

## 14. Troubleshooting

| Problem | Fix |
|---------|-----|
| `app_lifespan() takes 0 positional arguments` | Add `server: FastMCP` parameter to `app_lifespan()` |
| `FastMCP.run() got unexpected keyword argument 'port'` | Set `host`/`port` on `FastMCP()` constructor, not `run()` |
| Server starts but no HTTP output | Set transport to `streamable-http` in `run()` |
| ngrok warning page blocks Copilot Studio | Use **Cloudflare Tunnel** instead |
| `cloudflared` not recognized after install | Close and reopen your terminal |
| Copilot says "Server URL is not valid" | URL must be HTTPS and end with `/mcp` |
| Copilot `SystemError` | Check both terminals running (server + tunnel), URL ends with `/mcp` |
| "This feature isn't available until your agent has finished setting up" | Fill in agent **Description** and **Instructions** on Overview tab, then **Publish** |
| Copilot "Connector request failed: Not Found" | URL needs `/mcp` at the end |
| Copilot says "Authentication failed" | Verify API key matches between Copilot Studio and `MCP_API_KEY` |
| Tools not appearing in Copilot Studio | Check server logs for errors, verify server is running |
| Connection to Azure SQL fails | Check `.env` credentials and Azure SQL firewall rules |
| ODBC driver not found | Install [ODBC Driver 18](https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server) |
| Query timeout | Optimize query, add indexes, use `TOP` to limit rows |
| Permission denied | Grant necessary permissions to database user |
| Chart shows "Column Not Found" | Match `label_column`/`value_column` exactly to query output columns |
| Chart shows "No Data Found" | Check your WHERE clause and date ranges |

### Check installed ODBC drivers

```bash
# Windows (PowerShell)
Get-OdbcDriver

# macOS/Linux
odbcinst -q -d
```

---

## 15. Security Checklist

- [ ] `.env` file is in `.gitignore` (never commit credentials)
- [ ] Server URL uses HTTPS
- [ ] API key authentication enabled for production
- [ ] Parameterized queries used for user input
- [ ] Azure SQL firewall restricts access to known IPs
- [ ] Database user has least-privilege permissions
- [ ] Azure SQL auditing enabled
- [ ] API key and passwords rotated regularly
- [ ] No multiple SQL statements allowed (built-in)
- [ ] WHERE clause required for UPDATE/DELETE (built-in)

---

## Project Structure

```
azure-sql-mcp-server/
├── azure_sql_mcp.py           # Main MCP server (all 12 tools)
├── requirements.txt           # Python dependencies
├── .env.example               # Environment variables template
├── .env                       # Your config (not in git)
├── AZURE_SQL_MCP_GUIDE.md     # This file
└── .gitignore
```

---

## Adding Custom Tools

```python
class CustomInput(BaseModel):
    param1: str = Field(..., description="Parameter description")
    response_format: ResponseFormat = Field(default=ResponseFormat.MARKDOWN)

@mcp.tool(
    name="azure_sql_custom_tool",
    annotations={
        "title": "Custom Tool",
        "readOnlyHint": True,
        "destructiveHint": False,
        "idempotentHint": True,
        "openWorldHint": False
    }
)
async def custom_tool(params: CustomInput) -> str:
    """Tool description."""
    try:
        results = execute_query("SELECT ...")
        if params.response_format == ResponseFormat.JSON:
            return json.dumps(results, indent=2, default=str)
        return "**Results**\n..."
    except Exception as e:
        return _handle_db_error(e)
```

---

## Changelog

### v2.0.0 — Chart Visualization
- Added `azure_sql_visualize_data` tool (6 chart types)
- Adaptive Card output for Copilot Studio
- Auto-statistics (total, average, min, max)
- QuickChart API integration (no API key required)

### v1.0.0 — Initial Release
- 5 core tools: execute_query, list_tables, get_table_schema, get_table_data, get_database_info
- Pydantic validation, parameterized queries, dual output formats

### v2.1.0 — Full CRUD + Deployment
- Added 7 new tools: create_record, update_record, delete_record, search, create_table, drop_table, visualize_data
- Cloudflare Tunnel support (replaces ngrok)
- API key authentication middleware
- Copilot Studio agent configuration (description + instructions)