azure-sql-mcp
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
```
This server cannot be deployed
Maintenance
ActivityMaintained
ResponsivenessOutdated data