1# Grant Azure SQL data-plane access to the managed identity AND apply EF Core migrations.
2#
3# USAGE: Copy this file to scripts/grant-and-migrate.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-and-migrate.sh
11#       windows:
12#         shell: pwsh
13#         run: ./scripts/grant-and-migrate.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#
22# CONFIGURATION:
23#   Set $AppProjectPath below to the path of your application project (.csproj directory)
24 
25$ErrorActionPreference = 'Stop'
26 
27$AppProjectPath = "src/api"  # Adjust to your project directory
28 
29# Load azd environment variables
30azd env get-values | ForEach-Object {
31    $name, $value = $_.Split('=', 2)
32    Set-Item "env:$name" $value.Trim('"')
33}
34 
35# Determine app identity name (App Service uses SERVICE_WEB_NAME, APIs use SERVICE_API_NAME)
36$AppName = if ($env:SERVICE_WEB_NAME) { $env:SERVICE_WEB_NAME } else { $env:SERVICE_API_NAME }
37 
38if (-not $AppName) {
39    throw "ERROR: Neither SERVICE_WEB_NAME nor SERVICE_API_NAME is set in azd environment."
40}
41 
42# ─── Step 1: Grant SQL data-plane access ────────────────────────────────────
43Write-Host "Granting SQL data-plane access to managed identity: $AppName"
44 
45$SqlQuery = @"
46IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = '$AppName')
47  CREATE USER [$AppName] FROM EXTERNAL PROVIDER;
48 
49IF NOT EXISTS (
50  SELECT 1 FROM sys.database_role_members drm
51  JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
52  JOIN sys.database_principals m ON drm.member_principal_id = m.principal_id
53  WHERE r.name = 'db_datareader' AND m.name = '$AppName'
54)
55  ALTER ROLE db_datareader ADD MEMBER [$AppName];
56 
57IF NOT EXISTS (
58  SELECT 1 FROM sys.database_role_members drm
59  JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
60  JOIN sys.database_principals m ON drm.member_principal_id = m.principal_id
61  WHERE r.name = 'db_datawriter' AND m.name = '$AppName'
62)
63  ALTER ROLE db_datawriter ADD MEMBER [$AppName];
64 
65IF NOT EXISTS (
66  SELECT 1 FROM sys.database_role_members drm
67  JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
68  JOIN sys.database_principals m ON drm.member_principal_id = m.principal_id
69  WHERE r.name = 'db_ddladmin' AND m.name = '$AppName'
70)
71  ALTER ROLE db_ddladmin ADD MEMBER [$AppName];
72"@
73 
74# Ensure the rdbms-connect extension is installed (provides 'az sql db query')
75az extension show --name rdbms-connect 1>$null 2>$null
76if ($LASTEXITCODE -ne 0) {
77    az extension add --name rdbms-connect --yes
78    if ($LASTEXITCODE -ne 0) {
79        throw "Failed to install Azure CLI extension 'rdbms-connect', which is required for 'az sql db query'."
80    }
81}
82 
83az sql db query `
84  --server $env:SQL_SERVER `
85  --database $env:SQL_DATABASE `
86  --resource-group $env:AZURE_RESOURCE_GROUP `
87  --auth-mode ActiveDirectoryDefault `
88  --queries $SqlQuery
89 
90Write-Host "SQL access granted successfully."
91 
92# ─── Step 2: Apply EF Core migrations ───────────────────────────────────────
93# Install dotnet-ef only when it is not already installed
94$globalTools = dotnet tool list --global 2>$null
95if ($LASTEXITCODE -ne 0) {
96    throw "Failed to list globally installed .NET tools."
97}
98if (-not ($globalTools | Select-String -Pattern '^\s*dotnet-ef\s')) {
99    dotnet tool install --global dotnet-ef
100    if ($LASTEXITCODE -ne 0) {
101        throw "Failed to install dotnet-ef."
102    }
103}
104$env:PATH += ";$env:USERPROFILE\.dotnet\tools"
105 
106$ConnectionString = "Server=tcp:$($env:SQL_SERVER).database.windows.net,1433;Database=$($env:SQL_DATABASE);Authentication=Active Directory Default;Encrypt=True;"
107 
108Write-Host "Applying EF Core migrations..."
109Set-Location $AppProjectPath
110dotnet ef database update --connection $ConnectionString
111Write-Host "Migrations applied successfully."
112