Expose Azure SQL data to AI agents (Microsoft Foundry, VS Code Copilot, any MCP client) via the Model Context Protocol, with enterprise-grade OAuth and per-role/per-user RBAC end-to-end.
The demo deploys two MCP server patterns side-by-side so you can compare authentication models:
| Endpoint | Pattern | What SQL sees | Best for |
|---|---|---|---|
/mi-multidb/mcp |
One Data API Builder (DAB) container federates 5 entities across 4 databases on 2 SQL servers, using Managed Identities. | The container's identity | Service-to-service, no per-user SQL provisioning |
/obo/mcp |
DAB exchanges the caller's user token for a SQL token as the user (OAuth On-Behalf-Of). | The actual end user | Per-user audit, per-user SQL row-level RBAC |
Both are fronted by Azure API Management (type=mcp) with OAuth validation, then visible to Foundry agents through Azure API Center as a private tool catalog.
Demo deploys in ~25 min
┌─────────────────────────────────────┐
│ 5 scripts. Each idempotent. │
│ ./scripts/bootstrap-entra.sh │
│ ./scripts/deploy.sh │
│ ./scripts/seed-sql.sh │
│ ./scripts/deploy-containers.sh │
│ ./scripts/test-e2e.sh │
└─────────────────────────────────────┘
▼
┌─────────────────────────────────────┐
│ Two MCP endpoints, both OAuth │
│ https://<apim>.azure-api.net/ │
│ mi-multidb/mcp │
│ obo/mcp │
└─────────────────────────────────────┘
▼
┌─────────────────────────────────────┐
│ 5 entities · 4 databases · │
│ 2 SQL servers · 1 Sales App audi. │
│ 6 Entra groups + roles │
└─────────────────────────────────────┘
flowchart TB
subgraph Clients["🤖 MCP clients"]
VSC[VS Code Copilot Chat]
FDY[Microsoft Foundry agents]
end
subgraph Gateway["🌐 Public edge"]
APIM["Azure API Management<br/>type=mcp APIs<br/>/mi-multidb/mcp · /obo/mcp<br/><i>JWT validation + role-mapping policy</i>"]
APIC["Azure API Center<br/><i>private MCP tool catalog</i>"]
end
subgraph VNet["🔒 VNet (Limited to VNet — no public access)"]
DAB1["sqlmcp-dab-multi<br/>(2 UAMIs attached)<br/><i>Federation pattern</i>"]
DAB2["sqlmcp-dab-obo<br/>(OBO token swap)<br/><i>Per-user identity</i>"]
end
subgraph SrvA["💾 SQL Server A"]
SDB[("SalesDb")]
ODB[("OrdersDb")]
end
subgraph SrvB["💾 SQL Server B"]
GDB[("GLDb")]
ADB[("APDb")]
end
Entra["🔑 Microsoft Entra ID<br/>Sales App registration<br/>5 app roles · 5 groups"]
VSC -->|OAuth user JWT| APIM
FDY -->|OAuth user JWT<br/>or Agent MI| APIM
APIM -->|JWT validated<br/>X-MS-API-ROLE header| DAB1
APIM -->|JWT forwarded| DAB2
DAB1 -->|UAMI: sales| SDB
DAB1 -->|UAMI: sales| ODB
DAB1 -->|UAMI: finance| GDB
DAB1 -->|UAMI: finance| ADB
DAB2 -->|"OBO swap → SQL token <i>as the user</i>"| GDB
APIM -.->|auto-sync| APIC
APIC -.->|catalog discovery| FDY
APIM -.->|"validates JWT issued by"| Entra
Clients -.->|OAuth flow| Entra
sequenceDiagram
autonumber
participant U as User / Agent
participant E as Entra ID<br/>(Sales App)
participant A as APIM
participant D as DAB container
participant S as Azure SQL
Note over U,S: PATTERN 1 — Managed Identity (Federation)
U->>E: OAuth (user_impersonation)
E-->>U: JWT (aud=Sales App, roles=[sales-*])
U->>A: POST /mi-multidb/mcp + Bearer JWT
A->>A: validate-jwt (audience, issuer, roles)
A->>A: set X-MS-API-ROLE = highest role
A->>D: forward to mi-multidb container
D->>D: check dab-config permissions for role
D->>S: connect via UAMI (sales or finance)
Note over D,S: SQL sees the UAMI, not the user
S-->>D: rows (filtered by UAMI's grants)
D-->>A: MCP response
A-->>U: response
Note over U,S: PATTERN 2 — On-Behalf-Of (per-user)
U->>A: POST /obo/mcp + Bearer JWT
A->>D: forward to obo container
D->>E: OBO swap (client_id=Sales App + secret + user JWT)
E-->>D: SQL access token (aud=database.windows.net,<br/>oid=USER, upn=USER)
D->>S: connect with the SQL token AS the user
Note over D,S: SQL sees the actual user
S-->>D: rows (filtered by user's DB principal grants)
D-->>A: MCP response
A-->>U: response
sql-mcp/
├── README.md ← you are here
├── .gitignore ← protects secrets/local state
├── .vscode/mcp.json ← VS Code MCP client config
├── infra/ ← Azure Bicep
│ ├── main.bicep ← entry point
│ ├── main.parameters.json ← parameters (populated by bootstrap)
│ └── modules/ ← 9 modules: VNet, SQL, ACR, ACA, APIM, …
├── dab/ ← Data API Builder configs
│ ├── mi-multidb/ ← Pattern 1 (5 entities, 4 DBs, 2 servers)
│ ├── obo/ ← Pattern 2 (1 entity, OBO swap)
│ ├── README.md ← env-var ↔ config ↔ UAMI wiring deep-dive
│ └── Dockerfile ← inside each subfolder
├── apim/
│ └── mcp-policy.xml ← JWT-validation policy template
├── sql/ ← 12 files, one per (db, action)
│ ├── schema-{Sales,Orders,GL,AP}Db.sql
│ ├── seed-{Sales,Orders,GL,AP}Db.sql
│ └── grant-uami-{Sales,Orders,GL,AP}Db.sql
└── scripts/ ← 6 scripts, 5 deploy + 1 client
├── bootstrap-entra.sh ← 1. Entra apps + roles + groups + secret
├── deploy.sh ← 2. Bicep infra
├── seed-sql.sh ← 3. Schemas + seed + UAMI grants
├── deploy-containers.sh ← 4. DAB images + ACA + APIM type=mcp
├── test-e2e.sh ← 5. Full validation
└── mcp-client.py ← Interactive CLI for ad-hoc testing
| Tool | Install | Why |
|---|---|---|
| Azure CLI ≥ 2.55 | brew install azure-cli / docs |
All Azure operations |
jq |
brew install jq |
JSON parsing |
go-sqlcmd |
brew install sqlcmd |
Seeding SQL with Entra auth |
| Python 3.11+ | system or brew install python |
Test harness + interactive client |
| bash 5+ | system | Running scripts |
Azure-side requirements:
- An active Azure subscription in a region that supports APIM Standard v2 (e.g.,
swedencentral,eastus,westeurope) - Permission to create Entra app registrations in your tenant (typically the Application Administrator role, or Cloud Application Administrator)
- A budget of ~$5 for a one-day demo or ~$30/day if you leave it running (see Cost)
Verify your environment:
az --version && jq --version && sqlcmd --version && python3 --version
az account show # must show your target subscriptionImportant: run scripts from the repo root so relative paths resolve correctly. Each script is idempotent — safe to re-run if something fails partway.
git clone <this-repo> sql-mcp && cd sql-mcp
az login
az account set --subscription "<your-subscription-id>"
export RG=rg-sqlmcp-demo
export LOCATION=swedencentral # or eastus, westeurope, etc.Creates the Sales App registration with the right configuration and 6 security groups.
./scripts/bootstrap-entra.shWhat it creates:
sql-mcp-dab-salesapp reg with v2 tokens,AzureADandPersonalMicrosoftAccountsign-in, 5 app roles,user_impersonationscopeAzure SQL/user_impersonationdelegated permission + admin consent (required for OBO)- 6 Entra groups: 5 demo roles + 1 SQL admin group (you are added to all)
- Client secret (1-year), pre-auth list for Azure CLI + VS Code, redirect URIs
- Local
.entra-valuesfile (chmod 600) andinfra/main.parameters.jsonupdated
Validate: cat .entra-values should show SALES_APP_ID= and DAB_OBO_CLIENT_SECRET= populated.
Bicep deploys: RG → VNet → Key Vault → 2 SQL servers + 4 DBs → 2 UAMIs → Log Analytics + App Insights → ACR → ACA env → APIM Standard v2.
./scripts/deploy.shAPIM Standard v2 is the long pole (~12–20 min). Everything else completes in 3–5 min.
Validate: after the script finishes, run
jq '.apimGatewayUrl.value, .sqlServerA.value, .sqlServerB.value' .last-outputs.jsonYou should see your APIM gateway URL and two SQL server names.
Creates schemas, sample data, and the UAMI grants in all 4 databases.
./scripts/seed-sql.shValidate: the last line should print "✓ SQL seeded — 4 databases…".
Builds 2 DAB images in ACR, creates 2 container apps, then configures APIM with type=mcp APIs, the JWT policy, the OIDC provider, the custom PRM endpoint, and the backends.
./scripts/deploy-containers.shValidate: the script ends with its own smoke test:
✓ /mi-multidb/mcp returns 401 (expected — no token)
✓ /obo/mcp returns 401 (expected — no token)
✓ PRM /mi-multidb served (200)
✓ PRM /obo served (200)
Acquires a real OAuth token (client-credentials with the Sales App) and runs through initialize → tools/list → describe_entities → read_records on every entity.
./scripts/test-e2e.shYou should see:
✓ token issued, length 1288
✓ aud=<sales-app-guid> roles=['sales-reader'] ver=2.0
✓ /mi-multidb/mcp — 5 entities, real rows returned
✓ /obo/mcp — 1 entity (GLAccount)
ALL GREEN — full MCP pipeline working end-to-end.
🎉 The MCP server is now usable from any client.
In the Foundry portal → your project → Build → Tools → + Add tool → Custom → Model Context Protocol:
| Field | Value |
|---|---|
| Name | MIMultiDBMCP |
| Remote MCP Server endpoint | https://<your-apim>.azure-api.net/mi-multidb/mcp |
| Authentication | Microsoft Entra ID |
| Identity | Project managed identity (or Agent identity) |
| Audience | api://<your-sales-app-id>/user_impersonation |
Click Connect → copy the redirect URL → add it to the Sales App's Authentication → Web → Redirect URIs. Then create a prompt agent (gpt-4o-mini), attach the tool, and chat with it.
Open this repo in VS Code (the .vscode/mcp.json is pre-configured). Edit the URLs to point at your APIM. Switch Copilot Chat to Agent mode, sign in when prompted, and chat away.
python3 scripts/mcp-client.py mi-multidb
# Then type commands:
> describe_entities
> read_records Customer 5
> aggregate Order sum:TotalUsd
> read_records Vendor 3
> quitThese show DAB enforcing field- and entity-level RBAC based on the JWT roles claim:
| Prompt | What happens |
|---|---|
| "What data do you have access to?" | Lists 5 entities across 2 servers / 4 DBs |
| "Show 3 customers including their TaxId column" | Returns 3 rows but the TaxId field is missing — excluded for the sales-reader role in dab-config.json |
| "Show vendors with their bank routing numbers" | Returns rows but BankRouting/BankAccount/TaxId columns are missing |
| "How many orders are there and what's the total revenue?" | aggregate_records — real numbers from OrdersDb |
| "Add a new customer named Acme Co" | Succeeds for sales-writer / sales-admin. Fails 403 for sales-reader. |
| "Create a GL account 9999 named 'Test'" | Always refused (GL writes require finance-partner role — no sales-* role has it) |
| "Delete order 1" | Refused unless user has sales-admin |
The agent's behavior is driven entirely by the
dab-config*.jsonfiles. To change what roles can do, edit those, rebuild the image (Step 4), and the policy changes propagate without touching APIM, Bicep, or any code.
Approximate daily burn with the default sizes from this repo (1× APIM Standard v2 unit, S0 SQL, Basic ACR):
| Resource | Daily |
|---|---|
| APIM Standard v2 (1 unit) | ~$30 |
| 4× SQL Database S0 | ~$0.80 |
| ACA env Load Balancer | ~$0.10 |
| ACR Basic | ~$0.17 |
| Container Apps (idle, 2× 1 vCPU) | ~$0.10 |
| Foundry / API Center / VNet / UAMIs | $0 (idle / free) |
| Total | ~$31/day |
$200/month VS Enterprise credit covers 6–7 days of continuous demo. For longer-running dev, switch APIM to Developer SKU (~$1.70/day) — but recreating the APIM is a re-deploy (~15 min).
When you're done:
az group delete -n "$RG" --yes --no-wait # async, ~10 min
# Entra cleanup (free — keep if you want to redeploy without re-bootstrapping)
source .entra-values
az ad app delete --id "$SALES_APP_ID"
for g in "Sales Readers" "Sales Writers" "Sales Admins" \
"Finance Analysts" "Finance Partners" "sql-mcp-sql-admins"; do
GID=$(az ad group show -g "$g" --query id -o tsv 2>/dev/null)
[ -n "$GID" ] && az ad group delete -g "$GID"
done
rm -f .entra-values .last-outputs.jsonUser JWT roles: [sales-reader, sales-writer, sales-admin, …]
│
┌────────────────────────┼────────────────────────┐
│ │ │
▼ LAYER 1 ▼ LAYER 2 ▼ LAYER 3
APIM gate APIM role selector DAB dab-config
"required-claims" X-MS-API-ROLE header "permissions" array
(reject if no role) (highest of the user's) (entity + field-level)
│
▼
LAYER 4 (OBO only)
SQL DB principal
(db_datareader,
row-level security,
schema permissions)
| Concern | mi-multidb (Pattern 1) |
obo (Pattern 2) |
|---|---|---|
| SQL connection identity | UAMI of container | The user's OBO-swapped token |
| SQL audit log shows | UAMI client id (98ebfbf3-…) |
User UPN (alice@contoso.com) |
| Per-user provisioning needed in SQL | ❌ One UAMI for all | ✅ Each user as a SQL DB principal |
Row-level security keyed on USER_NAME() |
❌ (always the UAMI) | ✅ (the actual user) |
| Suitable for unattended/background agents | ✅ | ❌ (no user to act on behalf of) |
| Suitable for compliance demos | ✅ End-to-end audit |
Read
dab/README.mdfor the full env-var → config → UAMI wiring story.
This repo was deployed end-to-end into a fresh resource group (May 2026) and validated:
| Step | Time | Result |
|---|---|---|
bootstrap-entra.sh |
30 s | ✅ Sales App + 6 groups + secret + KV grants |
deploy.sh (Bicep) |
~18 min | ✅ All 10 sub-deployments green |
seed-sql.sh |
~30 s | ✅ 12 SQL files, 4 DBs seeded |
deploy-containers.sh |
~3 min | ✅ 2 images built, 2 ACA apps running, APIM configured |
test-e2e.sh |
~10 s | ✅ Both endpoints return 5 entities + 1 entity, real SQL rows |
| Total | ~25 min | All green |
If you want to re-validate yourself, follow Steps 0–5 above. The whole loop costs <$1 if you tear down within an hour.
| Symptom | Likely cause | Fix |
|---|---|---|
bootstrap-entra.sh fails on app creation |
Tenant blocks app reg for your account | Need Application Administrator role or higher |
deploy.sh complains about REPLACE_ME_VIA_BOOTSTRAP_ENTRA |
You skipped Step 1 | Run ./scripts/bootstrap-entra.sh first |
seed-sql.sh says "Login failed for user <token-identified principal>" |
You're not in the SQL admin group / token expired | Re-run az login. The Entra group you're in was set as the SQL Entra admin during deploy. |
deploy-containers.sh fails on az acr build |
ACR isn't fully provisioned yet (rare race) | Wait 30 s, re-run the script (idempotent) |
| MCP endpoint returns 401 | No token attached or token has no roles claim |
The JWT must contain aud=<sales-app-id> and roles=[…]. Use test-e2e.sh to verify token shape. |
| Foundry MCP tool stuck on consent screen | Sales App's redirect URI doesn't include Foundry's | Copy Foundry's redirect URL → add it to Sales App → Authentication → Web → Redirect URIs |
OBO endpoint returns Unable to acquire database access token |
Missing Azure SQL/user_impersonation consent or user not a SQL principal |
Re-run ./scripts/bootstrap-entra.sh (granted by it); manually add the user via CREATE USER [user] FROM EXTERNAL PROVIDER in GLDb |
dab/README.md— env-var → config → UAMI wiring across the multi-DB and OBO containersapim/mcp-policy.xml— the JWT validation +X-MS-API-ROLEinjection policy (read top-to-bottom; ~95 lines)- Data API Builder docs
- APIM
type=mcpoverview - Foundry private tool catalog
- Model Context Protocol spec
Sample code for demo and training purposes. No warranty. Use at your own risk.