1# SQL Database Recipe — REFERENCE ONLY
2 
3Adds Azure SQL Database integration to an App Service base template.
4 
5## Overview
6 
7This recipe composes with a Web API or Web App base template to add Azure SQL Database connectivity. It provides the IaC delta (SQL Server, database, firewall, RBAC) and per-language source code using EF Core, Prisma, or SQLAlchemy.
8 
9## Integration Type
10 
11| Aspect | Value |
12|--------|-------|
13| **Database** | Azure SQL Database (Serverless or Provisioned) |
14| **Auth** | Managed identity (passwordless) |
15| **ORM** | EF Core (.NET), Prisma (Node.js), SQLAlchemy (Python) |
16| **Hosting** | App Service (from base template) |
17| **Local Auth** | Disabled in Azure (Entra ID only); local dev may use SQL auth |
18 
19## Composition Steps
20 
21Apply these steps AFTER `azd init -t <base-template>`:
22 
23| # | Step | Details |
24|---|------|---------|
25| 1 | **Add IaC module** | Add SQL Server + Database Bicep module to `infra/app/` |
26| 2 | **Wire into main** | Add module reference in `main.bicep` |
27| 3 | **Add app settings** | Add SQL connection string (managed identity) |
28| 4 | **Add source code** | Add ORM models, DbContext/client setup from `source/{lang}.md` |
29| 5 | **Add packages** | Add ORM and SQL client packages |
30| 6 | **Run migrations** | Add postprovision hook for DB schema setup |
31 
32## App Settings
33 
34| Setting | Value | Purpose |
35|---------|-------|---------|
36| `AZURE_SQL_CONNECTION_STRING` | `Server=tcp:{server}.database.windows.net;Database={db};Authentication=Active Directory Managed Identity;User Id={clientId};` | Passwordless SQL connection |
37 
38### Bicep App Settings Block
39 
40```bicep
41appSettings: [
42  {
43    name: 'AZURE_SQL_CONNECTION_STRING'
44    value: 'Server=tcp:${sqlServer.properties.fullyQualifiedDomainName},1433;Database=${sqlDatabase.name};Authentication=Active Directory Managed Identity;User Id=${managedIdentity.properties.clientId};Encrypt=True;TrustServerCertificate=False;'
45  }
46]
47```
48 
49> **Note:** The `Authentication=Active Directory Managed Identity` setting tells the SQL client to use the app's managed identity. No passwords are stored.
50 
51## RBAC Roles Required
52 
53| Role | GUID | Scope | Purpose |
54|------|------|-------|---------|
55| **SQL DB Contributor** | `9b7fa17d-e63e-47b0-bb0a-15c516ac86ec` | SQL Server | Manage database |
56| **Directory Readers** | `88d8e3e3-8f55-4a1e-953a-9b9898b8876b` | Entra ID | Read directory for MI auth |
57 
58> **Important:** Data plane access uses SQL-level roles (`db_datareader`, `db_datawriter`), assigned via a postprovision script that runs `ALTER ROLE` statements.
59 
60## Resources Created
61 
62| Resource | Type | Purpose |
63|----------|------|---------|
64| SQL Server | `Microsoft.Sql/servers` | Logical SQL server |
65| SQL Database | `Microsoft.Sql/servers/databases` | Application database |
66| Firewall Rule | `Microsoft.Sql/servers/firewallRules` | Allow Azure services |
67| Entra Admin | `Microsoft.Sql/servers/administrators` | Set MI as admin |
68 
69## Files
70 
71| Path | Description |
72|------|-------------|
73| [source/dotnet.md](source/dotnet.md) | C# EF Core integration |
74| [source/python.md](source/python.md) | Python SQLAlchemy integration |
75| [source/nodejs.md](source/nodejs.md) | Node.js Prisma integration |
76 
77## References
78 
79- [Azure SQL passwordless connections](https://learn.microsoft.com/en-us/azure/azure-sql/database/azure-sql-passwordless-migration)
80- [EF Core with Azure SQL](https://learn.microsoft.com/en-us/ef/core/providers/sql-server/)
81- [Tutorial: App Service with SQL](https://learn.microsoft.com/en-us/azure/app-service/tutorial-dotnetcore-sqldb-app)
82