1#!/bin/bash
2# Grant Azure SQL data-plane access to the App Service / Container App managed identity.
3#
4# USAGE: Copy this file to scripts/grant-sql-access.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-sql-access.sh
12#       windows:
13#         shell: pwsh
14#         run: ./scripts/grant-sql-access.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#   SQL_GRANT_DDLADMIN   - Set to "true" to also grant db_ddladmin (needed for EF migrations)
23 
24set -e
25 
26# Safely load azd environment variables without eval
27while IFS= read -r line; do
28  [ -n "$line" ] || continue
29  key=${line%%=*}
30  value=${line#*=}
31  case "$value" in
32    \"*\") value=${value#\"}; value=${value%\"} ;;
33    \'*\') value=${value#\'}; value=${value%\'} ;;
34  esac
35  export "$key=$value"
36done < <(azd env get-values)
37 
38# Determine app identity name (App Service uses SERVICE_WEB_NAME, APIs use SERVICE_API_NAME)
39APP_NAME=${SERVICE_WEB_NAME:-$SERVICE_API_NAME}
40 
41if [ -z "$APP_NAME" ]; then
42  echo "ERROR: Neither SERVICE_WEB_NAME nor SERVICE_API_NAME is set in azd environment." >&2
43  exit 1
44fi
45 
46echo "Granting SQL data-plane access to managed identity: $APP_NAME"
47 
48# Build idempotent SQL grant queries (reader + writer, required for all apps)
49SQL_QUERIES="
50  IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = '$APP_NAME')
51    CREATE USER [$APP_NAME] FROM EXTERNAL PROVIDER;
52 
53  IF NOT EXISTS (
54    SELECT 1 FROM sys.database_role_members drm
55    JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
56    JOIN sys.database_principals m ON drm.member_principal_id = m.principal_id
57    WHERE r.name = 'db_datareader' AND m.name = '$APP_NAME'
58  )
59    ALTER ROLE db_datareader ADD MEMBER [$APP_NAME];
60 
61  IF NOT EXISTS (
62    SELECT 1 FROM sys.database_role_members drm
63    JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
64    JOIN sys.database_principals m ON drm.member_principal_id = m.principal_id
65    WHERE r.name = 'db_datawriter' AND m.name = '$APP_NAME'
66  )
67    ALTER ROLE db_datawriter ADD MEMBER [$APP_NAME];
68"
69 
70# Optionally grant db_ddladmin (needed when EF Core migrations run at startup or via hook)
71SQL_GRANT_DDLADMIN="${SQL_GRANT_DDLADMIN:-false}"
72if [ "$SQL_GRANT_DDLADMIN" = "true" ]; then
73  SQL_QUERIES="$SQL_QUERIES
74  IF NOT EXISTS (
75    SELECT 1 FROM sys.database_role_members drm
76    JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
77    JOIN sys.database_principals m ON drm.member_principal_id = m.principal_id
78    WHERE r.name = 'db_ddladmin' AND m.name = '$APP_NAME'
79  )
80    ALTER ROLE db_ddladmin ADD MEMBER [$APP_NAME];
81"
82fi
83 
84# Ensure the rdbms-connect extension is installed (provides 'az sql db query')
85if ! az extension show --name rdbms-connect >/dev/null 2>&1; then
86  echo "Azure CLI extension 'rdbms-connect' is not installed. Installing..."
87  if ! az extension add --name rdbms-connect --yes; then
88    echo "ERROR: Failed to install Azure CLI extension 'rdbms-connect'. Ensure Azure CLI has network access and retry." >&2
89    exit 1
90  fi
91fi
92 
93az sql db query \
94  --server "$SQL_SERVER" \
95  --database "$SQL_DATABASE" \
96  --resource-group "$AZURE_RESOURCE_GROUP" \
97  --auth-mode ActiveDirectoryDefault \
98  --queries "$SQL_QUERIES"
99 
100echo "SQL access granted successfully."
101