1# SQL Database - Entra ID Authentication
2 
3## Entra ID Admin Configuration (User)
4 
5**Recommended for development** — Uses signed-in user as admin.
6 
7```bicep
8param principalId string
9param principalName string
10@allowed(['User', 'Group', 'Application'])
11param principalType string = 'User'
12 
13resource sqlServer 'Microsoft.Sql/servers@2022-05-01-preview' = {
14  name: '${resourcePrefix}-sql-${uniqueHash}'
15  location: location
16  properties: {
17    administrators: {
18      administratorType: 'ActiveDirectory'
19      principalType: principalType
20      login: principalName
21      sid: principalId
22      tenantId: subscription().tenantId
23      azureADOnlyAuthentication: true
24    }
25    minimalTlsVersion: '1.2'
26  }
27}
28```
29 
30> ⚠️ **Warning:** If deploying from CI/CD with a service principal, set `principalType` to `'Application'`. The default `'User'` only works for interactive (human) deployments.
31 
32**Get signed-in user info:**
33```bash
34az ad signed-in-user show --query "{id:id, name:displayName}" -o json
35```
36 
37**Set as azd environment variables:**
38```bash
39PRINCIPAL_INFO=$(az ad signed-in-user show --query "{id:id, name:displayName}" -o json)
40azd env set AZURE_PRINCIPAL_ID $(echo $PRINCIPAL_INFO | jq -r '.id')
41azd env set AZURE_PRINCIPAL_NAME $(echo $PRINCIPAL_INFO | jq -r '.name')
42```
43 
44> 💡 **Tip:** Set these immediately after `azd init` to avoid deployment failures.
45 
46## Entra ID Admin Configuration (Group)
47 
48**Recommended for production** — Uses Entra group for admin access.
49 
50```bicep
51resource sqlServer 'Microsoft.Sql/servers@2022-05-01-preview' = {
52  name: '${resourcePrefix}-sql-${uniqueHash}'
53  location: location
54  properties: {
55    administrators: {
56      administratorType: 'ActiveDirectory'
57      principalType: 'Group'
58      login: 'SQL Admins'
59      sid: entraGroupObjectId
60      tenantId: subscription().tenantId
61      azureADOnlyAuthentication: true
62    }
63    minimalTlsVersion: '1.2'
64  }
65}
66```
67 
68## Managed Identity Access
69 
70Grant app managed identity access via SQL:
71 
72```sql
73CREATE USER [my-container-app] FROM EXTERNAL PROVIDER;
74ALTER ROLE db_datareader ADD MEMBER [my-container-app];
75ALTER ROLE db_datawriter ADD MEMBER [my-container-app];
76```
77 
78## Common Database Roles
79 
80| Role | Permissions |
81|------|-------------|
82| `db_datareader` | Read all tables |
83| `db_datawriter` | Insert, update, delete |
84| `db_ddladmin` | Create/modify schema |
85| `db_owner` | Full access |
86 
87## Connection Strings
88 
89### Entra ID Authentication (Recommended)
90 
91> ⚠️ **MANDATORY**: When generating SQL connection strings for apps with Entra-only auth, ALWAYS include an `Authentication` parameter. Use `Authentication=Active Directory Default` for general scenarios (App Service, local dev) or `Authentication=Active Directory Managed Identity` when a user-assigned managed identity with a specific `User Id` is required (e.g., Azure Functions SQL bindings). Do NOT use manual `AccessToken` assignment, `DefaultAzureCredential().GetToken()` patterns, or bare connection strings without an authentication parameter. The `Authentication` parameter is required in both the Bicep infrastructure output (e.g., App Service `connectionStrings` property) and in any application configuration files (e.g., `appsettings.json`).
92 
93```
94Server=tcp:{server}.database.windows.net,1433;Database={database};Authentication=Active Directory Default;Encrypt=True;TrustServerCertificate=False;
95```
96 
97**Required for .NET applications:**
98- `Microsoft.Data.SqlClient` (v5.1.0+)
99- `Azure.Identity` (for local development)
100 
101### Legacy SQL Authentication (⛔ DO NOT USE - EVER)
102 
103> ❌ **PROHIBITED — NEVER generate connection strings with `User ID` and `Password` parameters, and NEVER generate `administratorLogin` or `administratorLoginPassword` Bicep properties.**
104> This applies unconditionally — do not include them in any conditional, ternary, or optional branch.
105> SQL admin authentication is disabled in all subscriptions with Entra-only policies and violates Azure security best practices.
106> Always use an Entra ID `Authentication=Active Directory Default` or `Authentication=Active Directory Managed Identity` connection string above.
107