AZ-400: Managed Identity for App Service → Azure SQL (no passwords)

[zkarachiwala]

Exam objective

Configure System-assigned Managed Identity on App Service and use it to authenticate to Azure SQL with no stored database password
(Design and implement a security and compliance plan — 10–15%)

Read first

• Managed Identity overview
• Tutorial: App Service to Azure SQL with Managed Identity
• Active Directory Default authentication in Microsoft.Data.SqlClient

What to build

End-to-end passwordless connection from App Service to Azure SQL. After completing this issue, the App Service has no database password in its configuration anywhere.

Your task

1. Enable System-assigned Managed Identity on the App Service: Settings → Identity → System assigned → Status: On → Save — note the Object (principal) ID that appears
2. In Azure SQL, connect as an administrator and create a contained database user for the managed identity:

   CREATE USER [timetracker-zak] FROM EXTERNAL PROVIDER;
   ALTER ROLE db_datareader ADD MEMBER [timetracker-zak];
   ALTER ROLE db_datawriter ADD MEMBER [timetracker-zak];

3. Update the connection string in App Service App Settings to use Entra authentication:

   Server=<server>.database.windows.net;Database=TimeTrackerDb;Authentication=Active Directory Default;Encrypt=True;
   

4. Remove DbUser and DbPassword from App Service App Settings
5. Restart the App Service — verify it starts and the app loads without credentials in the connection string

Explore

# Verify the managed identity exists:
az webapp identity show --name timetracker-zak --resource-group <rg> \
  --query "{type: type, principalId: principalId}" --output table

# Confirm the identity has been added to the SQL database:
# Connect to Azure SQL as admin and run:
SELECT name, type_desc FROM sys.database_principals WHERE authentication_type_desc = 'EXTERNAL'

# Test the connection string locally with az login (Active Directory Default falls back to CLI credentials):
# The connection string will work locally if you are logged in to az CLI with the right tenant
dotnet run  # from TimeTracker.Web — should connect using your az CLI credentials

Exam checkpoint

• What is the difference between a System-assigned and User-assigned Managed Identity?
• What is a contained database user in SQL Server and how does it differ from a server-level login?
• What Azure RBAC role grants an identity the ability to read/write data in Azure SQL?
• What happens to the Managed Identity when the App Service resource is deleted?
• What is the principle of least privilege and how does it apply to the SQL roles you granted?

[zkarachiwala]

Moved to study-plans — tracked in the Certifications project.