1#!/bin/bash
2# Grant Azure SQL data-plane access to the managed identity AND apply EF Core migrations.
3#
4# USAGE: Copy this file to scripts/grant-and-migrate.sh in your project root and add
5#        a postprovision hook in azure.yaml:
6#
7#   hooks:
8#     postprovision:
9#       posix:
10#         shell: sh
11#         run: ./scripts/grant-and-migrate.sh
12#       windows:
13#         shell: pwsh
14#         run: ./scripts/grant-and-migrate.ps1
15#
16# ENVIRONMENT VARIABLES (sourced from azd env):
17#   SQL_SERVER           - SQL server name (without .database.windows.net)
18#   SQL_DATABASE         - Database name
19#   AZURE_RESOURCE_GROUP - Resource group name
20#   SERVICE_WEB_NAME     - App Service name (used when set, takes priority)
21#   SERVICE_API_NAME     - API service name (fallback when SERVICE_WEB_NAME is not set)
22#
23# CONFIGURATION:
24#   Set APP_PROJECT_PATH below to the path of your application project (.csproj directory)
25 
26set -e
27 
28APP_PROJECT_PATH="src/api"  # Adjust to your project directory
29 
30# Safely load azd environment variables without eval
31while IFS= read -r line; do
32  [ -n "$line" ] || continue
33  key=${line%%=*}
34  value=${line#*=}
35  case "$value" in
36    \"*\") value=${value#\"}; value=${value%\"} ;;
37    \'*\') value=${value#\'}; value=${value%\'} ;;
38  esac
39  export "$key=$value"
40done < <(azd env get-values)
41 
42# Determine app identity name (App Service uses SERVICE_WEB_NAME, APIs use SERVICE_API_NAME)
43APP_NAME=${SERVICE_WEB_NAME:-$SERVICE_API_NAME}
44 
45if [ -z "$APP_NAME" ]; then
46  echo "ERROR: Neither SERVICE_WEB_NAME nor SERVICE_API_NAME is set in azd environment." >&2
47  exit 1
48fi
49 
50# ─── Step 1: Grant SQL data-plane access ────────────────────────────────────
51echo "Granting SQL data-plane access to managed identity: $APP_NAME"
52 
53# Ensure the rdbms-connect extension is installed (provides 'az sql db query')
54if ! az extension show --name rdbms-connect >/dev/null 2>&1; then
55  echo "Installing Azure CLI extension: rdbms-connect"
56  if ! az extension add --name rdbms-connect --yes; then
57    echo "ERROR: Failed to install required Azure CLI extension 'rdbms-connect'. Cannot continue with 'az sql db query'." >&2
58    exit 1
59  fi
60fi
61 
62az sql db query \
63  --server "$SQL_SERVER" \
64  --database "$SQL_DATABASE" \
65  --resource-group "$AZURE_RESOURCE_GROUP" \
66  --auth-mode ActiveDirectoryDefault \
67  --queries "
68    IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = '$APP_NAME')
69      CREATE USER [$APP_NAME] FROM EXTERNAL PROVIDER;
70 
71    IF NOT EXISTS (
72      SELECT 1 FROM sys.database_role_members drm
73      JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
74      JOIN sys.database_principals m ON drm.member_principal_id = m.principal_id
75      WHERE r.name = 'db_datareader' AND m.name = '$APP_NAME'
76    )
77      ALTER ROLE db_datareader ADD MEMBER [$APP_NAME];
78 
79    IF NOT EXISTS (
80      SELECT 1 FROM sys.database_role_members drm
81      JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
82      JOIN sys.database_principals m ON drm.member_principal_id = m.principal_id
83      WHERE r.name = 'db_datawriter' AND m.name = '$APP_NAME'
84    )
85      ALTER ROLE db_datawriter ADD MEMBER [$APP_NAME];
86 
87    IF NOT EXISTS (
88      SELECT 1 FROM sys.database_role_members drm
89      JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
90      JOIN sys.database_principals m ON drm.member_principal_id = m.principal_id
91      WHERE r.name = 'db_ddladmin' AND m.name = '$APP_NAME'
92    )
93      ALTER ROLE db_ddladmin ADD MEMBER [$APP_NAME];
94  "
95 
96echo "SQL access granted successfully."
97 
98# ─── Step 2: Apply EF Core migrations ───────────────────────────────────────
99# Install dotnet-ef only when it is not already installed (no-op when already present)
100if ! dotnet tool list --global 2>/dev/null | grep -q '^\s*dotnet-ef\s'; then
101  dotnet tool install --global dotnet-ef
102fi
103export PATH="$PATH:$HOME/.dotnet/tools"
104 
105CONNECTION_STRING="Server=tcp:${SQL_SERVER}.database.windows.net,1433;Database=${SQL_DATABASE};Authentication=Active Directory Default;Encrypt=True;"
106 
107echo "Applying EF Core migrations..."
108cd "$APP_PROJECT_PATH"
109dotnet ef database update --connection "$CONNECTION_STRING"
110echo "Migrations applied successfully."
111