1# SQL Database Entra Authentication
2 
3Quick reference for Azure SQL Database Entra authentication in post-deployment scenarios.
4 
5## Prerequisites
6 
7Azure SQL Server must be configured with Entra-only authentication during provisioning. The signed-in user must be set as Entra admin:
8 
9```bicep
10@allowed(['User', 'Group', 'Application'])
11param principalType string = 'User'
12 
13properties: {
14  administrators: {
15    administratorType: 'ActiveDirectory'
16    principalType: principalType  // 'User' for interactive, 'Application' for CI/CD
17    login: principalName
18    sid: principalId
19    tenantId: subscription().tenantId
20    azureADOnlyAuthentication: true
21  }
22}
23```
24 
25> ⚠️ **Warning:** Hardcoding `principalType: 'User'` causes `UnmatchedPrincipalType` errors when deploying from CI/CD with a service principal. Use a parameter instead.
26 
27## Connection Patterns
28 
29### Azure CLI (Recommended for Scripts)
30 
31> ⚠️ **Warning:** `az sql db query` requires the `rdbms-connect` extension. Install it first: `az extension add --name rdbms-connect --yes`
32 
33```bash
34az sql db query \
35  --server "$SQL_SERVER" \
36  --database "$SQL_DATABASE" \
37  --resource-group "$AZURE_RESOURCE_GROUP" \
38  --auth-mode ActiveDirectoryDefault \
39  --queries "SELECT 1"
40```
41 
42### Connection Strings
43 
44**For .NET applications with managed identity:**
45 
46```
47Server=tcp:{server}.database.windows.net,1433;Database={database};Authentication=Active Directory Default;Encrypt=True;
48```
49 
50**Required packages:**
51- `Microsoft.Data.SqlClient` (v5.1.0+)
52- `Azure.Identity` (for local development)
53 
54## Database Roles
55 
56| Role | Permissions | Use For |
57|------|------------|---------|
58| `db_datareader` | SELECT | Read operations |
59| `db_datawriter` | INSERT, UPDATE, DELETE | Write operations |
60| `db_ddladmin` | CREATE, ALTER, DROP schema | EF migrations |
61| `db_owner` | Full control | Admin (use sparingly) |
62 
63## Grant Managed Identity Access
64 
65```sql
66-- Create user from managed identity
67CREATE USER [app-name] FROM EXTERNAL PROVIDER;
68 
69-- Grant standard application permissions
70ALTER ROLE db_datareader ADD MEMBER [app-name];
71ALTER ROLE db_datawriter ADD MEMBER [app-name];
72ALTER ROLE db_ddladmin ADD MEMBER [app-name];
73```
74 
75> 💡 **Tip:** The managed identity name matches the App Service or Container App name.
76 
77## Verify Current Admin
78 
79```bash
80az sql server ad-admin list \
81  --server "$SQL_SERVER" \
82  --resource-group "$AZURE_RESOURCE_GROUP"
83```
84 
85## References
86 
87- [SQL Managed Identity Access](sql-managed-identity.md)
88- [EF Core Migrations](ef-migrations.md)
89- [Post-Deployment Guide](post-deployment.md)
90