1# Grant Azure SQL data-plane access to the App Service / Container App managed identity.
2#
3# USAGE: Copy this file to scripts/grant-sql-access.ps1 in your project root and add
4#        a postprovision hook in azure.yaml:
5#
6#   hooks:
7#     postprovision:
8#       posix:
9#         shell: sh
10#         run: ./scripts/grant-sql-access.sh
11#       windows:
12#         shell: pwsh
13#         run: ./scripts/grant-sql-access.ps1
14#
15# ENVIRONMENT VARIABLES (sourced from azd env):
16#   SQL_SERVER           - SQL server name (without .database.windows.net)
17#   SQL_DATABASE         - Database name
18#   AZURE_RESOURCE_GROUP - Resource group name
19#   SERVICE_WEB_NAME     - App Service name (used when set, takes priority)
20#   SERVICE_API_NAME     - API service name (fallback when SERVICE_WEB_NAME is not set)
21#   SQL_GRANT_DDLADMIN   - Set to "true" to also grant db_ddladmin (needed for EF migrations)
22 
23$ErrorActionPreference = 'Stop'
24 
25# Load azd environment variables
26azd env get-values | ForEach-Object {
27    $name, $value = $_.Split('=', 2)
28    Set-Item "env:$name" $value.Trim('"')
29}
30 
31# Determine app identity name (App Service uses SERVICE_WEB_NAME, APIs use SERVICE_API_NAME)
32$AppName = if ($env:SERVICE_WEB_NAME) { $env:SERVICE_WEB_NAME } else { $env:SERVICE_API_NAME }
33 
34if (-not $AppName) {
35    throw "ERROR: Neither SERVICE_WEB_NAME nor SERVICE_API_NAME is set in azd environment."
36}
37 
38Write-Host "Granting SQL data-plane access to managed identity: $AppName"
39 
40# Build idempotent SQL grant queries (reader + writer, required for all apps)
41$SqlQuery = @"
42IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = '$AppName')
43  CREATE USER [$AppName] FROM EXTERNAL PROVIDER;
44 
45IF NOT EXISTS (
46  SELECT 1 FROM sys.database_role_members drm
47  JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
48  JOIN sys.database_principals m ON drm.member_principal_id = m.principal_id
49  WHERE r.name = 'db_datareader' AND m.name = '$AppName'
50)
51  ALTER ROLE db_datareader ADD MEMBER [$AppName];
52 
53IF 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_datawriter' AND m.name = '$AppName'
58)
59  ALTER ROLE db_datawriter ADD MEMBER [$AppName];
60"@
61 
62# Optionally grant db_ddladmin (needed when EF Core migrations run at startup or via hook)
63$GrantDdlAdmin = $env:SQL_GRANT_DDLADMIN -eq 'true'
64if ($GrantDdlAdmin) {
65    $SqlQuery += @"
66 
67IF NOT EXISTS (
68  SELECT 1 FROM sys.database_role_members drm
69  JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
70  JOIN sys.database_principals m ON drm.member_principal_id = m.principal_id
71  WHERE r.name = 'db_ddladmin' AND m.name = '$AppName'
72)
73  ALTER ROLE db_ddladmin ADD MEMBER [$AppName];
74"@
75}
76 
77# Ensure the rdbms-connect extension is installed (provides 'az sql db query')
78az extension show --name rdbms-connect *> $null
79if ($LASTEXITCODE -ne 0) {
80    Write-Host "Azure CLI extension 'rdbms-connect' is not installed. Installing..."
81    az extension add --name rdbms-connect --yes
82    if ($LASTEXITCODE -ne 0) {
83        throw "ERROR: Failed to install Azure CLI extension 'rdbms-connect'. Cannot continue because 'az sql db query' requires it."
84    }
85}
86 
87az sql db query `
88  --server $env:SQL_SERVER `
89  --database $env:SQL_DATABASE `
90  --resource-group $env:AZURE_RESOURCE_GROUP `
91  --auth-mode ActiveDirectoryDefault `
92  --queries $SqlQuery
93 
94Write-Host "SQL access granted successfully."
95