# SQL MCP Server — Foundry Demo — Resume Point

Project: Deploy Microsoft's SQL MCP Server (Data API builder) on Azure Container
Apps, backed by an Azure SQL `Products` table, so it can be wired into a
Microsoft Foundry agent (Claude, via Microsoft Agent Framework or the Foundry
portal) as a Custom MCP Tool.

Working folder on this machine: `~/MCP-Azure` (contains `dab-config.json` and
`Dockerfile`). This is a **separate folder** from the `agent.py` / Cursor
project that consumes this MCP server later — don't merge them.

---

## Current status

| Step | Status |
|---|---|
| 1. Azure SQL Database + Products table (10 sample rows) | ✅ Done — verified via Azure Portal Query Editor (`SELECT COUNT(*) FROM dbo.Products` → 10) |
| 2. `dab-config.json` (SQL MCP Server config) | ✅ Done — entity `Products` fully configured, `anonymous:read`, field descriptions set |
| 3. Dockerfile | ✅ Created |
| 3. Azure Container Registry (ACR) resource | ✅ Created (`acrsqlmcp15272`) |
| 3. Build image via `az acr build` (cloud build) | ❌ **Blocked** — Free Trial subscriptions can't use ACR Tasks (`TasksOperationsNotAllowed`). Switched to local Docker build + push instead. |
| 3. Build image locally with Docker, tag, push to ACR | 🔄 **In progress — resume here** |
| 3. Container Apps environment + deploy | ⬜ Not started |
| 3. Get `/mcp` URL, test `/health` | ⬜ Not started |
| 4. Wire into Foundry (portal or `agent.py`) | ⬜ Not started (guide + `agent.py` already exist from earlier session) |

---

## Known values (already created — don't regenerate)

```bash
RESOURCE_GROUP="rg-mcp-demo"
LOCATION="westus3"
SQL_SERVER="sql-mcp-1000"              # NOT the FQDN — just the server name
SQL_DATABASE="ProductsDb"
SQL_ADMIN="sqladmin"
SQL_PASSWORD="<not stored here — re-enter each session>"

CONNECTION_STRING="Server=tcp:${SQL_SERVER}.database.windows.net,1433;Database=${SQL_DATABASE};User ID=${SQL_ADMIN};Password=${SQL_PASSWORD};Encrypt=true;TrustServerCertificate=false;Connection Timeout=30;"

ACR_NAME="acrsqlmcp15272"
ACR_LOGIN_SERVER="acrsqlmcp15272.azurecr.io"

CONTAINERAPP_ENV="sql-mcp-env"         # not yet created
CONTAINERAPP_NAME="sql-mcp-server"     # not yet created
```

Re-paste this whole block into a fresh terminal at the start of each session —
none of these persist across terminal tabs/restarts.

---

## Remaining steps (resume here)

### 1. Confirm Docker Desktop is running

```bash
docker --version
```

If not installed: `brew install --cask docker`, then open Docker Desktop from
Applications and wait for the whale icon to go steady before continuing.

### 2. Build the image locally, targeting the right architecture

This Mac is Apple Silicon (arm64), but Azure Container Apps expects
`linux/amd64` by default — build explicitly for that platform:

```bash
cd ~/MCP-Azure
docker build --platform linux/amd64 -t sql-mcp-server:1 .
```

### 3. Tag and push to ACR

```bash
az acr login --name "$ACR_NAME"

docker tag sql-mcp-server:1 "$ACR_LOGIN_SERVER/sql-mcp-server:1"
docker push "$ACR_LOGIN_SERVER/sql-mcp-server:1"
```

Confirm it landed:
```bash
az acr repository list --name "$ACR_NAME" --output table
```
Expect to see `sql-mcp-server` listed.

### 4. Create the Container Apps environment and deploy

```bash
az containerapp env create --name "$CONTAINERAPP_ENV" --resource-group "$RESOURCE_GROUP" --location "$LOCATION"

ACR_USERNAME=$(az acr credential show --name "$ACR_NAME" --query username --output tsv)
ACR_PASSWORD=$(az acr credential show --name "$ACR_NAME" --query "passwords[0].value" --output tsv)

az containerapp create \
  --name "$CONTAINERAPP_NAME" --resource-group "$RESOURCE_GROUP" --environment "$CONTAINERAPP_ENV" \
  --image "$ACR_LOGIN_SERVER/sql-mcp-server:1" \
  --registry-server "$ACR_LOGIN_SERVER" --registry-username "$ACR_USERNAME" --registry-password "$ACR_PASSWORD" \
  --target-port 5000 --ingress external --min-replicas 1 --max-replicas 3 \
  --secrets "mssql-connection-string=$CONNECTION_STRING" \
  --env-vars "MSSQL_CONNECTION_STRING=secretref:mssql-connection-string" \
  --cpu 0.5 --memory 1.0Gi
```

### 5. Get the MCP URL and test it

```bash
MCP_URL=$(az containerapp show --name "$CONTAINERAPP_NAME" --resource-group "$RESOURCE_GROUP" \
  --query "properties.configuration.ingress.fqdn" --output tsv)

echo "MCP Server URL: https://$MCP_URL/mcp"
curl "https://$MCP_URL/health"
```

A healthy response here means the SQL MCP Server is fully live. That URL is
what goes into `PRODUCTS_MCP_URL` in the separate `agent.py` / Cursor project,
or into the "Remote MCP Server endpoint" field when wiring this into Microsoft
Foundry's portal (Custom MCP Tool, Authentication: Unauthenticated, Require
approval: never).

---

## Gotchas already hit and fixed (so you don't re-debug these)

1. **SQL Server region capacity restriction** (Free Trial subscriptions can't
   create SQL servers in some regions, e.g. East US) — fixed by using
   `westus3`.
2. **`sqlcmd` (Go/Homebrew version) panics** on certain flag combinations
   (`panic: runtime error: index out of range`) — worked around by verifying
   data via the **Azure Portal Query Editor** instead of the CLI.
3. **`dab init --connection-string "@env(...)"` fails** with `Response file
   'env(...)' does not exist` — the DAB CLI's arg parser treats a leading `@`
   as a "response file" reference. Fixed by using `--connection-string=@env(...)`
   (no space, `=` sign) instead of a space-separated value.
4. **`.NET 8.0.0 not found` running `dab`** — DAB `2.0.9` targets .NET 8, but
   only .NET 9 SDK was installed. Fixed with
   `export DOTNET_ROLL_FORWARD=LatestMajor` (run once per terminal session,
   or prefix each `dotnet tool run dab ...` command with it).
5. **`dab update Products --fields.description "Category: Electronics,
   Furniture, ..."` truncates at the first comma** — the CLI parser splits on
   commas. Fixed by hand-editing the `Category` field's `description` value
   directly in `dab-config.json` after generation.
6. **`Microsoft.ContainerRegistry` provider not registered** — first-time use
   of ACR on this subscription. Fixed with:
   ```bash
   az provider register --namespace Microsoft.ContainerRegistry
   az provider register --namespace Microsoft.App
   az provider register --namespace Microsoft.OperationalInsights
   ```
7. **`az acr build` fails with `TasksOperationsNotAllowed`** — ACR Tasks
   (cloud-based builds) are blocked entirely for Azure Free Trial
   subscriptions; only a Pay-As-You-Go upgrade lifts this. Workaround: build
   the image **locally with Docker** (`docker build --platform linux/amd64`)
   and push directly with `docker tag` / `docker push` instead of
   `az acr build`. This is where we are now (Step 3 above).

---

## Related files from earlier work (not in this folder)

- `foundry-mcp-azure-sql-demo.md` — full original step-by-step guide (Steps
  1–6, including the no-code Foundry portal wiring and the Python SDK path).
- `agent.py`, `.env.example`, `requirements.txt` — a Microsoft Agent
  Framework + Claude-in-Foundry agent (separate Cursor project) that consumes
  this same MCP server via `PRODUCTS_MCP_URL`.