Skip to main content
Glama
README.md
# azure-sql-mcp

A guarded, read-only [Model Context Protocol](https://modelcontextprotocol.io/) server for Azure SQL Database. It gives MCP clients accurate schema metadata, bounded query results, estimated execution plans, and a reusable slow-query diagnosis workflow.

> **Security note:** the server is read-only by default, but a remote HTTP deployment must still be protected by Microsoft Entra authentication, private networking, access restrictions, or an API gateway. The application does not implement HTTP authentication itself.

## Features

- `sql://schema/summary` Resource with tables, columns, primary keys, and foreign keys.
- `run_readonly_query` Tool with single-statement validation and sensitive-column removal.
- Configurable result cap from 1 to 500 rows; defaults to 50.
- Azure SQL TLS enforcement: `Encrypt=yes` and `TrustServerCertificate=no`.
- Per-session 3-second lock timeout and read-intent connection setting.
- `get_execution_plan` Tool using `SHOWPLAN_XML`, so diagnostics do not execute the query.
- `diagnose-slow-tsql` Prompt for evidence-led query/index recommendations.
- Local stdio and Streamable HTTP transports from the same codebase.

## Architecture

```text
MCP client (ChatGPT Desktop / Codex / Claude Code)
        │
        ├─ stdio: local child process
        └─ HTTP:  /mcp endpoint on Azure App Service
        │
        ▼
FastMCP server
        │
        ├─ schema Resource
        ├─ query and plan Tools
        └─ performance Prompt
        │
        ▼
Guardrails → Azure SQL read-only login → Azure SQL Database
```

## Prerequisites

- Python 3.12+
- [uv](https://docs.astral.sh/uv/)
- An Azure SQL login with read-only database permissions
- Azure SQL firewall or VNet access from the machine running the server

## Quick start

### uv (recommended)

```powershell
Copy-Item .env.example .env
uv sync
```

Edit `.env` with your Azure SQL values. Never commit this file.

```env
AZURE_SQL_SERVER=<server>.database.windows.net
AZURE_SQL_DATABASE=<database>
AZURE_SQL_USER=<read-only-login>
AZURE_SQL_PASSWORD=<password>
AZURE_SQL_ALLOWED_STATEMENT_TYPES=SELECT
AZURE_SQL_MAX_ROWS=50
AZURE_SQL_MCP_TRANSPORT=stdio
```

### pip alternative

`requirements.txt` is provided for pip-based environments. Create and activate a virtual
environment, install the dependencies, then install this project so the `azure-sql-mcp`
command is available:

```powershell
py -m venv .venv
.\.venv\Scripts\Activate.ps1
python -m pip install -r requirements.txt
python -m pip install -e .
```

Changing `.env` requires restarting the local MCP process. This is intentional: long-lived processes do not automatically reload secrets or connection settings from disk.

## MCP capabilities

| Type | Name | Purpose |
|---|---|---|
| Resource | `sql://schema/summary` | Discover real schema, table, column, and key names before querying. |
| Tool | `run_readonly_query` | Run one guarded query and receive JSON-safe, redacted rows. |
| Tool | `get_execution_plan` | Generate an estimated `SHOWPLAN_XML` without executing the query. |
| Prompt | `diagnose-slow-tsql` | Guide query-plan and index analysis without making production changes. |

## Local clients

### ChatGPT Desktop app

Open **Settings → MCP servers → Add server**.

For the simplest local setup, choose **STDIO** and configure:

| Field | Value |
|---|---|
| Command to launch | Full path to `uv.exe` (find it with `Get-Command uv \| Select-Object -Expand Source`) |
| Arguments | `run`, then `azure-sql-mcp` as two separate arguments |
| Working directory | This repository's absolute path |

Do not put `uv run azure-sql-mcp` into the command field as one value. The app launches the command and arguments separately. Restart ChatGPT Desktop after changing the MCP configuration, then open a local Codex chat and ask:

```text
Use azure-sql-mcp to read sql://schema/summary.
```

### Codex CLI

From the repository root:

```powershell
codex mcp add azure-sql-mcp -- uv run azure-sql-mcp
codex mcp list
```

### Claude Code

From the repository root:

```powershell
claude mcp add --scope project azure-sql-mcp -- uv run azure-sql-mcp
claude mcp list
```

## Local Streamable HTTP

Use HTTP when testing the same network transport used by Azure App Service. Keep this terminal open while a client is connected:

```powershell
$env:AZURE_SQL_MCP_TRANSPORT = "streamable-http"
$env:PORT = "8000"
uv run azure-sql-mcp
```

The endpoint is `http://127.0.0.1:8000/mcp`.

Connect from Codex CLI:

```powershell
codex mcp add azure-sql-mcp-http --url http://127.0.0.1:8000/mcp
```

Or add it in ChatGPT Desktop through **Settings → MCP servers → Add server → Streamable HTTP**. Browser ChatGPT cannot access `127.0.0.1` on your computer; use a secured remote endpoint instead.

## Query safety model

Before SQL reaches Azure SQL, the server:

1. Requires exactly one permitted statement; the secure default is `SELECT`.
2. Rejects stacked statements and `SELECT INTO`.
3. Adds `TOP (AZURE_SQL_MAX_ROWS)` when the query has no existing bounded limit.
4. Uses `SET ROWCOUNT` as a database-side backstop, including for paged queries.
5. Removes fields whose names indicate passwords, tokens, secrets, API keys, or SSNs.

Use deterministic paging for larger result sets:

```sql
SELECT customer_id, email
FROM dbo.Customers
ORDER BY customer_id
OFFSET 500 ROWS FETCH NEXT 500 ROWS ONLY;
```

## Azure App Service deployment

1. Build the application with its locked dependencies:

   ```text
   uv sync --frozen --no-dev
   ```

2. Configure App Service application settings for every `AZURE_SQL_*` variable above and:

   ```text
   AZURE_SQL_MCP_TRANSPORT=streamable-http
   ```

   App Service provides `PORT` automatically. Store the password in App Service settings or Key Vault—not in a deployed `.env` file.

3. Use this startup command:

   ```text
   uv run azure-sql-mcp
   ```

4. The MCP endpoint will be:

   ```text
   https://<app-name>.azurewebsites.net/mcp
   ```

5. Before registering clients, restrict access with Microsoft Entra authentication, private endpoints, App Service access restrictions, or a token-validating gateway. Keep the Azure SQL principal read-only and allow the App Service outbound network path through Azure SQL networking.

Register the secured remote server with clients:

```powershell
codex mcp add azure-sql-mcp --url https://<app-name>.azurewebsites.net/mcp
claude mcp add --scope user --transport http azure-sql-mcp https://<app-name>.azurewebsites.net/mcp
```

## Verification

Run these checks after code changes:

```powershell
uv lock --check
uv run python -m compileall -q src
uv run fastmcp list --command "uv run azure-sql-mcp" --resources --prompts
```

The last command starts the server as a temporary stdio child process and lists its MCP catalog; it does not query Azure SQL.

## Repository layout

```text
src/azure_sql_mcp/
├── db.py               # Azure SQL connection and session guardrails
├── query_guardrails.py # Statement validation and row limiting
├── query_execution.py  # Guarded query execution and JSON output
├── redaction.py        # Sensitive-column removal
├── plan_analysis.py    # Non-executing estimated execution plans
└── server.py           # FastMCP Resources, Tools, Prompt, and transports
```