1# SQL Database - Bicep Patterns
2 
3> ⛔ **CRITICAL — SQL Server Bicep MUST use Entra-only authentication. NEVER include `administratorLogin` or `administratorLoginPassword` anywhere in a Bicep file — not even inside a conditional (`condition ? { ... } : { administratorLoginPassword: ... }`) branch. If either property name appears anywhere in the file, the deployment will be rejected. Always use the pattern below.**
4 
5## Basic Setup (Entra-Only Authentication)
6 
7**Required approach** — Uses Microsoft Entra ID authentication only. Required for subscriptions with Entra-only policies; SQL admin authentication is disabled by policy in those environments.
8 
9```bicep
10param principalId string
11param principalName string
12@allowed(['User', 'Group', 'Application'])
13param principalType string = 'User'
14 
15resource sqlServer 'Microsoft.Sql/servers@2022-05-01-preview' = {
16  name: '${resourcePrefix}-sql-${uniqueHash}'
17  location: location
18  properties: {
19    administrators: {
20      administratorType: 'ActiveDirectory'
21      principalType: principalType
22      login: principalName
23      sid: principalId
24      tenantId: subscription().tenantId
25      azureADOnlyAuthentication: true
26    }
27    minimalTlsVersion: '1.2'
28  }
29}
30 
31resource sqlDatabase 'Microsoft.Sql/servers/databases@2022-05-01-preview' = {
32  parent: sqlServer
33  name: 'appdb'
34  location: location
35  sku: {
36    name: 'Basic'
37    tier: 'Basic'
38  }
39  properties: {
40    collation: 'SQL_Latin1_General_CP1_CI_AS'
41    maxSizeBytes: 2147483648  // 2 GB
42  }
43}
44 
45resource sqlFirewallAzure 'Microsoft.Sql/servers/firewallRules@2022-05-01-preview' = {
46  parent: sqlServer
47  name: 'AllowAzureServices'
48  properties: {
49    startIpAddress: '0.0.0.0'
50    endIpAddress: '0.0.0.0'
51  }
52}
53```
54 
55**Set Entra admin parameters:**
56 
571. Get current user info:
58```bash
59az ad signed-in-user show --query "{id:id, name:displayName}" -o json
60```
61 
62> ⚠️ **Warning:** If deploying from CI/CD with a service principal, set `principalType` to `'Application'`. The default `'User'` only works for interactive (human) deployments. Mismatched `principalType` causes `UnmatchedPrincipalType` errors during provisioning.
63 
642. Set as azd environment variables:
65```bash
66PRINCIPAL_INFO=$(az ad signed-in-user show --query "{id:id, name:displayName}" -o json)
67azd env set AZURE_PRINCIPAL_ID $(echo $PRINCIPAL_INFO | jq -r '.id')
68azd env set AZURE_PRINCIPAL_NAME $(echo $PRINCIPAL_INFO | jq -r '.name')
69```
70 
71> 💡 **Tip:** Set these variables immediately after `azd init` to avoid deployment failures. The Bicep `principalId` and `principalName` parameters will automatically use these environment variables.
72 
73## ⚠️ MANDATORY: Connection String with Entra Auth Parameter
74 
75> **CRITICAL:** When outputting SQL connection strings in Bicep (e.g., in App Service `connectionStrings` or as outputs), ALWAYS include an `Authentication` parameter. Use `Authentication=Active Directory Default` for general scenarios or `Authentication=Active Directory Managed Identity` when a user-assigned managed identity with a specific `User Id` is required. A bare `Server=tcp:...;Database=...;` connection string without this parameter will not authenticate via Entra ID and the app will fail to connect.
76 
77**Correct pattern in Bicep (Active Directory Default):**
78 
79```bicep
80connectionStrings: [
81  {
82    name: 'DefaultConnection'
83    connectionString: 'Server=tcp:${sqlServer.properties.fullyQualifiedDomainName},1433;Database=${sqlDatabase.name};Authentication=Active Directory Default;Encrypt=True;TrustServerCertificate=False;'
84    type: 'SQLAzure'
85  }
86]
87```
88 
89**With user-assigned managed identity:**
90 
91```bicep
92connectionStrings: [
93  {
94    name: 'DefaultConnection'
95    connectionString: 'Server=tcp:${sqlServer.properties.fullyQualifiedDomainName},1433;Database=${sqlDatabase.name};Authentication=Active Directory Managed Identity;User Id=${uamiClientId};Encrypt=True;TrustServerCertificate=False;'
96    type: 'SQLAzure'
97  }
98]
99```
100 
101> ❌ **DO NOT** generate connection strings without an `Authentication=Active Directory Default` or `Authentication=Active Directory Managed Identity` parameter.
102> ❌ **DO NOT** use manual `AccessToken` assignment or `DefaultAzureCredential().GetToken()` in application code as an alternative to the connection string parameter.
103 
104## Serverless Configuration
105 
106```bicep
107resource sqlDatabase 'Microsoft.Sql/servers/databases@2022-05-01-preview' = {
108  parent: sqlServer
109  name: 'appdb'
110  location: location
111  sku: {
112    name: 'GP_S_Gen5'
113    tier: 'GeneralPurpose'
114    family: 'Gen5'
115    capacity: 2
116  }
117  properties: {
118    autoPauseDelay: 60  // minutes
119    minCapacity: json('0.5')
120  }
121}
122```
123 
124## Private Endpoint
125 
126```bicep
127resource sqlPrivateEndpoint 'Microsoft.Network/privateEndpoints@2023-05-01' = {
128  name: '${sqlServer.name}-pe'
129  location: location
130  properties: {
131    subnet: {
132      id: subnet.id
133    }
134    privateLinkServiceConnections: [
135      {
136        name: '${sqlServer.name}-connection'
137        properties: {
138          privateLinkServiceId: sqlServer.id
139          groupIds: ['sqlServer']
140        }
141      }
142    ]
143  }
144}
145```
146 
147## ⛔ MANDATORY: SQL Data-Plane Access via postprovision Hook
148 
149> **CRITICAL:** ARM/Bicep role assignments (`SQL DB Contributor`) only grant **control-plane** access. They do **not** grant the app **data-plane** access to the database. Without the T-SQL grant below, apps using `Authentication=Active Directory Default` will crash on startup with a login failure.
150>
151> **When you generate SQL + Managed Identity infrastructure you MUST also:**
152> 1. Add a `postprovision` hook to `azure.yaml` that runs the SQL grant script
153> 2. Copy the pre-built scripts from [scripts/grant-sql-access.sh](scripts/grant-sql-access.sh) and [scripts/grant-sql-access.ps1](scripts/grant-sql-access.ps1) to `scripts/` in the user's project root
154 
155**azure.yaml hooks section (add or merge):**
156 
157```yaml
158hooks:
159  postprovision:
160    posix:
161      shell: sh
162      run: ./scripts/grant-sql-access.sh
163    windows:
164      shell: pwsh
165      run: ./scripts/grant-sql-access.ps1
166```
167 
168**Copy the pre-built scripts** — Read [scripts/grant-sql-access.sh](scripts/grant-sql-access.sh) and [scripts/grant-sql-access.ps1](scripts/grant-sql-access.ps1) and write them verbatim to the project's `scripts/` folder. Do not regenerate them from scratch.
169 
170Key behaviours of the scripts:
171- Installs the `rdbms-connect` Azure CLI extension if not already present (required for `az sql db query`)
172- Loads `azd env get-values` safely (no `eval`)
173- Grants `db_datareader` + `db_datawriter` by default (idempotent)
174- Set `SQL_GRANT_DDLADMIN=true` in the azd env to also grant `db_ddladmin` (needed for EF Core migrations)
175- `SERVICE_WEB_NAME` takes priority over `SERVICE_API_NAME` when resolving the app identity
176 
177> 💡 Make executable: `chmod +x scripts/*.sh`
178 
179> 💡 If the app also uses EF Core migrations, combine this with the migration hook — see the `azure-deploy` skill's `references/recipes/azd/sql-managed-identity.md`.
180 
181## Legacy SQL Authentication (⛔ DO NOT USE - EVER)
182 
183> ❌ **PROHIBITED — NEVER generate `administratorLogin` or `administratorLoginPassword` properties.**
184> This applies unconditionally: do NOT include them in direct assignments, conditional expressions, ternary branches, parameter defaults, or any other construct. The **text** of these property names must not appear anywhere in a generated `.bicep` file.
185> SQL admin authentication is disabled in all subscriptions with Entra-only policies and violates Azure security best practices.
186> Always use the Entra-only authentication pattern at the top of this file.
187>
188> ❌ **DO NOT generate conditional patterns like:**
189> ```bicep
190> properties: useEntraAuth ? {
191>   administrators: {
192>     azureADOnlyAuthentication: true
193>     // ...
194>   }
195> } : {
196>   administratorLogin: sqlAdminLogin          // ❌ PROHIBITED
197>   administratorLoginPassword: sqlAdminPassword  // ❌ PROHIBITED
198> }
199> ```
200> Use only the unconditional Entra-only pattern. No fallback. No toggle. No conditional.
201