How is managed identity of web app working without assigning IAM role in SQL?

Question

Hi,

I am moving towards managed identities, in doing so I followed https://techcommunity.microsoft.com/t5/apps-on-azure-blog/connect-app-service-with-azure-sql-database-with-managed/ba-p/3288300 to connect web app with SQL DB.

I followed every step but I didn't add the app service's managed identity role in IAM section of SQL DB but still the managed identity is working. The above tutorial doesn't talk about adding IAM role but here this tutorial does: https://youtu.be/lnFvuanTkdc?si=TSLRP83c5HR87rll&t=461 (timestamped)

Can anyone please explain how is this happening? I have added the MI's user in the DB using SSMS but haven't tweaked with any IAM roles. Isn't IAM role assignment a necessary step to work with MIs?

Answer 1

It sounds like you have a bit of confusion around MIs and how they interact with Azure resources such as Azure SQL Database and Azure App Service. Managed Identities allow Azure resources to authenticate to other Azure services securely without needing to store credentials in the code.

When you're using a Managed Identity with Azure SQL Database, there are two primary aspects of security to consider:

1. RBAC: This is where IAM (Identity and Access Management) roles come into play. IAM roles are used to manage permissions at the Azure resource level, allowing you to control what actions a Managed Identity can perform on resources in Azure, such as Azure SQL servers or databases. However, IAM roles do not directly control access to the data or database objects within Azure SQL Database.
2. SQL Database-level Security: This is managed within the SQL Server itself, using SQL Server Authentication, including logins and database users. When you grant access to a Managed Identity in Azure SQL Database, you're typically creating a database user linked to that Managed Identity and then assigning the appropriate database roles or permissions to that user. This controls what the Managed Identity can do within the database, such as reading or writing data.

Why It Still Works Without IAM Role Assignment?

Based on your description, it sounds like you've successfully created a user within your SQL Database that's linked to the App Service's Managed Identity and granted it the necessary permissions at the database level. This is why your setup is working even without assigning an IAM role to the Managed Identity in the Azure SQL Database IAM settings.

IAM roles are not required for a Managed Identity to interact with Azure SQL Database at the data level. Instead, what matters is the database-level security setup specifically, the creation of a database user linked to the Managed Identity and the assignment of appropriate permissions or roles within the SQL database. This setup allows the Managed Identity to authenticate with Azure SQL Database and perform actions according to its database permissions.

It's a common misconception that IAM roles are required for database access. In reality, IAM roles manage access to Azure resources at the infrastructure level, while SQL permissions manage access to database content. Your Managed Identity needs to be configured at the SQL level (which you've done) to interact with the database contents, which is independent of the IAM roles.

This distinction is why the tutorial you followed did not require adding the App Service Managed Identity to the IAM section of the SQL Database. The crucial step is ensuring that the Managed Identity has been appropriately added and configured within the SQL Database itself, as you have done through SSMS.