Configure System-Assigned Managed Identity for Azure SQL Database Access
You have an Azure App Service web app that uses a system-assigned managed identity. The web app needs to authenticate to an Azure SQL Database to read and write data. You want to use the managed identity to avoid storing credentials in connection strings. Which steps are required to configure this access?
Quick Answer
The correct approach is to create a contained database user in Azure SQL Database mapped to the system-assigned managed identity, grant the necessary database roles, and then authenticate from the app using a Microsoft Entra ID access token. This works because managed identities provide an automatically managed service principal in Microsoft Entra ID, allowing the app to obtain a token without ever storing credentials in connection strings. The token is acquired from the managed identity endpoint and passed to SQL Database, which validates it against the contained user you created. On the AZ-204 exam, this scenario tests your understanding of how to bridge Azure resources with PaaS databases using identity-based authentication, a common pattern for secure, credential-free access. A frequent trap is forgetting that the SQL database user must be a contained user specifically mapped to the managed identity’s object ID, not a regular SQL login. Memory tip: think “Map, Grant, Token” — map the identity to a contained user, grant roles, then acquire and use the token.
⚠ Common exam trap
Watch out — candidates often confuse Azure RBAC roles (which manage control-plane access) with SQL database-level permissions (which manage data-plane access), leading them to incorrectly select Option A or C instead of understanding that a contained database user and token-based authentication are required.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Create a contained database user in the SQL database mapped to the managed identity, grant required database roles, and use Microsoft Entra ID token-based authentication from the app.
To use a system-assigned managed identity with Azure SQL Database, you must create a contained database user mapped to the managed identity in the SQL database, grant it the necessary database roles (e.g., db_datareader, db_datawriter), and then acquire an access token for Microsoft Entra ID (formerly Azure AD) from the managed identity endpoint to authenticate. This token-based approach avoids storing credentials and leverages the managed identity's automatic credential rotation.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Assign the managed identity the 'SQL DB Contributor' RBAC role on the database, then use SQL authentication with the identity's client ID.
Why it's wrong here
RBAC roles for Azure SQL Database are for management plane operations only (e.g., creating databases). Data plane access requires creating a contained database user. Also, SQL authentication does not use managed identities.
- ✓
Create a contained database user in the SQL database mapped to the managed identity, grant required database roles, and use Microsoft Entra ID token-based authentication from the app.
Why this is correct
This is the correct procedure. The managed identity (an Microsoft Entra ID principal) must be added as a database user. The app then acquires an access token for Azure SQL Database using the managed identity and uses it to connect.
- ✗
Enable Microsoft Entra ID authentication on the SQL server, add the managed identity as an Microsoft Entra ID admin, and use integrated security in the connection string.
Why it's wrong here
Adding the managed identity as an Microsoft Entra ID admin grants server-level permissions, which is over-privileged. Also, integrated security (Windows authentication) is not applicable to managed identities.
- ✗
Configure the connection string with the managed identity's principal ID as the user ID and leave the password empty.
Why it's wrong here
Connection strings for Microsoft Entra ID authentication require parameters like 'Authentication=Active Directory Managed Identity' and do not use a user ID/password in the traditional sense.
Go deeper
Related to this question
Learn chapter
Azure Functions Development
Key term
Managed identity
A managed identity is an automatically managed service principal in Azure that allows your code to authenticate to any service that supports Azure AD authentication without storing credentials.
Key term
Key Vault Secrets
Key Vault Secrets are secure containers in Microsoft Azure that store sensitive information like passwords, connection strings, and API keys, keeping them encrypted and accessible only to authorized applications and users.
About these practice questions
Courseiva writes every AZ-204 question from scratch — 881 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
Same concept, more angles
1 more way this is tested on AZ-204
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. Which TWO of the following are valid ways to authenticate an Azure function to an Azure SQL database using managed identity?
medium- A.Create a service principal and assign it to the function app.
- B.Use the function app's default connection string with a username and password.
- ✓ C.Create a user-assigned managed identity, assign it to the function app, and use its client ID in the connection string.
- D.Upload a client certificate to the function app and use it to authenticate.
- ✓ E.Enable system-assigned managed identity on the function app and set the SQL connection string with 'Authentication=Active Directory Managed Identity'.
Why C: A user-assigned managed identity can be created, assigned to the function app, and then used in the SQL connection string by specifying the client ID (e.g., 'User ID=<client_id>;Authentication=Active Directory Managed Identity;'). This allows the function to authenticate to Azure SQL without storing credentials. Option E is also correct because enabling a system-assigned managed identity and setting the connection string with 'Authentication=Active Directory Managed Identity' lets the function app authenticate using its own identity, which is automatically managed by Azure.
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This AZ-204 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the AZ-204 exam.