1# SQL Managed Identity Access
2 
3Grant Azure managed identities database permissions on Azure SQL with Entra authentication.
4 
5## Prerequisites
6 
7- Azure SQL Server with Entra ID admin configured
8- App Service/Container App with system-assigned managed identity
9- Your account is Entra ID admin on SQL Server
10- Azure CLI: `az login`
11- Azure CLI `rdbms-connect` extension: `az extension add --name rdbms-connect --yes`
12 
13## Quick Grant
14 
15```bash
16eval $(azd env get-values)
17APP_NAME=$(echo "$SERVICE_API_NAME")  # or SERVICE_WEB_NAME
18 
19az sql db query \
20  --server "$SQL_SERVER" \
21  --database "$SQL_DATABASE" \
22  --resource-group "$AZURE_RESOURCE_GROUP" \
23  --auth-mode ActiveDirectoryDefault \
24  --queries "
25    CREATE USER [$APP_NAME] FROM EXTERNAL PROVIDER;
26    ALTER ROLE db_datareader ADD MEMBER [$APP_NAME];
27    ALTER ROLE db_datawriter ADD MEMBER [$APP_NAME];
28    ALTER ROLE db_ddladmin ADD MEMBER [$APP_NAME];
29  "
30```
31 
32**PowerShell:**
33```powershell
34azd env get-values | ForEach-Object {
35    $name, $value = $_.Split('=', 2)
36    Set-Item "env:$name" $value
37}
38$AppName = $env:SERVICE_API_NAME  # or SERVICE_WEB_NAME
39 
40$SqlQuery = @"
41CREATE USER [$AppName] FROM EXTERNAL PROVIDER;
42ALTER ROLE db_datareader ADD MEMBER [$AppName];
43ALTER ROLE db_datawriter ADD MEMBER [$AppName];
44ALTER ROLE db_ddladmin ADD MEMBER [$AppName];
45"@
46 
47az sql db query `
48  --server $env:SQL_SERVER `
49  --database $env:SQL_DATABASE `
50  --resource-group $env:AZURE_RESOURCE_GROUP `
51  --auth-mode ActiveDirectoryDefault `
52  --queries $SqlQuery
53```
54 
55## Database Roles
56 
57| Role | Permissions | Use For |
58|------|------------|---------|
59| `db_datareader` | SELECT | Read-only queries |
60| `db_datawriter` | INSERT, UPDATE, DELETE | CRUD operations |
61| `db_ddladmin` | CREATE, ALTER, DROP schema | EF migrations |
62| `db_owner` | Full control | Admin (use sparingly) |
63 
64**Standard app (read/write/migrations):** All three roles above.  
65**Read-only app:** Only `db_datareader`.
66 
67## Automate with azd Hook
68 
69Add `postprovision` hook to `azure.yaml`:
70 
71```yaml
72hooks:
73  postprovision:
74    shell: sh
75    run: ./scripts/grant-sql-access.sh
76```
77 
78**scripts/grant-sql-access.sh:**
79 
80```bash
81#!/bin/bash
82set -e
83eval $(azd env get-values)
84 
85az sql db query \
86  --server "$SQL_SERVER" \
87  --database "$SQL_DATABASE" \
88  --resource-group "$AZURE_RESOURCE_GROUP" \
89  --auth-mode ActiveDirectoryDefault \
90  --queries "
91    IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = '$SERVICE_API_NAME')
92      CREATE USER [$SERVICE_API_NAME] FROM EXTERNAL PROVIDER;
93    
94    IF NOT EXISTS (
95      SELECT 1 FROM sys.database_role_members drm
96      JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
97      JOIN sys.database_principals m ON drm.member_principal_id = m.principal_id
98      WHERE r.name = 'db_datareader' AND m.name = '$SERVICE_API_NAME'
99    )
100      ALTER ROLE db_datareader ADD MEMBER [$SERVICE_API_NAME];
101    
102    IF NOT EXISTS (
103      SELECT 1 FROM sys.database_role_members drm
104      JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
105      JOIN sys.database_principals m ON drm.member_principal_id = m.principal_id
106      WHERE r.name = 'db_datawriter' AND m.name = '$SERVICE_API_NAME'
107    )
108      ALTER ROLE db_datawriter ADD MEMBER [$SERVICE_API_NAME];
109    
110    IF NOT EXISTS (
111      SELECT 1 FROM sys.database_role_members drm
112      JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
113      JOIN sys.database_principals m ON drm.member_principal_id = m.principal_id
114      WHERE r.name = 'db_ddladmin' AND m.name = '$SERVICE_API_NAME'
115    )
116      ALTER ROLE db_ddladmin ADD MEMBER [$SERVICE_API_NAME];
117  "
118```
119 
120**scripts/grant-sql-access.ps1:**
121 
122```powershell
123$ErrorActionPreference = 'Stop'
124azd env get-values | ForEach-Object {
125    $name, $value = $_.Split('=', 2)
126    Set-Item "env:$name" $value
127}
128 
129$SqlQuery = @"
130IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = '$($env:SERVICE_API_NAME)')
131  CREATE USER [$($env:SERVICE_API_NAME)] FROM EXTERNAL PROVIDER;
132 
133IF NOT EXISTS (
134  SELECT 1 FROM sys.database_role_members drm
135  JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
136  JOIN sys.database_principals m ON drm.member_principal_id = m.principal_id
137  WHERE r.name = 'db_datareader' AND m.name = '$($env:SERVICE_API_NAME)'
138)
139  ALTER ROLE db_datareader ADD MEMBER [$($env:SERVICE_API_NAME)];
140 
141IF NOT EXISTS (
142  SELECT 1 FROM sys.database_role_members drm
143  JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
144  JOIN sys.database_principals m ON drm.member_principal_id = m.principal_id
145  WHERE r.name = 'db_datawriter' AND m.name = '$($env:SERVICE_API_NAME)'
146)
147  ALTER ROLE db_datawriter ADD MEMBER [$($env:SERVICE_API_NAME)];
148 
149IF NOT EXISTS (
150  SELECT 1 FROM sys.database_role_members drm
151  JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
152  JOIN sys.database_principals m ON drm.member_principal_id = m.principal_id
153  WHERE r.name = 'db_ddladmin' AND m.name = '$($env:SERVICE_API_NAME)'
154)
155  ALTER ROLE db_ddladmin ADD MEMBER [$($env:SERVICE_API_NAME)];
156"@
157 
158az sql db query `
159  --server $env:SQL_SERVER `
160  --database $env:SQL_DATABASE `
161  --resource-group $env:AZURE_RESOURCE_GROUP `
162  --auth-mode ActiveDirectoryDefault `
163  --queries $SqlQuery
164```
165 
166> 💡 Make executable: `chmod +x scripts/*.sh`.
167 
168## Verification
169 
170```bash
171eval $(azd env get-values)
172APP_NAME=$SERVICE_API_NAME  # or SERVICE_WEB_NAME
173 
174az sql db query --server "$SQL_SERVER" --database "$SQL_DATABASE" \
175  --auth-mode ActiveDirectoryDefault --queries "
176    SELECT dp.name AS UserName, dr.name AS RoleName
177    FROM sys.database_principals dp
178    JOIN sys.database_role_members drm ON dp.principal_id = drm.member_principal_id
179    JOIN sys.database_principals dr ON drm.role_principal_id = dr.principal_id
180    WHERE dp.name = '$APP_NAME'
181  "
182```
183 
184**PowerShell:**
185```powershell
186azd env get-values | ForEach-Object {
187    $name, $value = $_.Split('=', 2)
188    Set-Item "env:$name" $value
189}
190$AppName = $env:SERVICE_API_NAME  # or SERVICE_WEB_NAME
191 
192$SqlQuery = @"
193SELECT dp.name AS UserName, dr.name AS RoleName
194FROM sys.database_principals dp
195JOIN sys.database_role_members drm ON dp.principal_id = drm.member_principal_id
196JOIN sys.database_principals dr ON drm.role_principal_id = dr.principal_id
197WHERE dp.name = '$AppName'
198"@
199 
200az sql db query --server $env:SQL_SERVER --database $env:SQL_DATABASE `
201  --auth-mode ActiveDirectoryDefault --queries $SqlQuery
202```
203 
204Expected: UserName matches `$APP_NAME`, RoleName includes `db_datareader`, `db_datawriter`, `db_ddladmin`.
205 
206## Troubleshooting
207 
208| Error | Solution |
209|-------|----------|
210| "Cannot find the user" | Verify identity exists: `az webapp identity show` or `az containerapp identity show` |
211| "Principal does not have permission" | Check you're Entra admin: `az sql server ad-admin list` |
212| "Login failed for user" | Run CREATE USER commands from this guide |
213 
214**Idempotent Script Pattern:**
215 
216```sql
217-- Check if user exists before creating
218IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'my-app')
219  CREATE USER [my-app] FROM EXTERNAL PROVIDER;
220 
221-- Check role membership before adding
222IF NOT EXISTS (
223  SELECT 1 FROM sys.database_role_members drm
224  JOIN sys.database_principals r ON drm.role_principal_id = r.principal_id
225  JOIN sys.database_principals m ON drm.member_principal_id = m.principal_id
226  WHERE r.name = 'db_datareader' AND m.name = 'my-app'
227)
228  ALTER ROLE db_datareader ADD MEMBER [my-app];
229```
230 
231## References
232 
233- [SQL Entra Authentication](sql-entra-auth.md)
234- [EF Core Migrations](ef-migrations.md)
235- [Post-Deployment Guide](post-deployment.md)
236