Leveraging System-Assigned Identity in Azure and Assigning Resources to an Entra Group

TL;DR — In this article, we’ll demonstrate how to connect an Azure Web App to multiple resources, with a particular focus on establishing a secure connection to an Azure SQL Database. We’ll leverage an Entra Group and a System-Assigned Managed Identity to streamline and secure the process.

Introduction

In the rapidly evolving cloud environment, managing identity and access is a crucial aspect of maintaining security and efficiency. Microsoft Azure offers several features to streamline this process, one of which is the use of System-Assigned Managed Identities. This article will guide you through the concept of System-Assigned Managed Identities in Azure, how to use them, and how to assign resources to an Entra group, simplifying access management and enhancing security.

I found articles explaining in Azure documentation explaining user assigned identity but I though was helpful to have a full procedure explaining the System Assigned Identity using Groups and details about the connection with Azure Sql Database.

What is a System-Assigned Managed Identity?

• System-Assigned Managed Identity is a feature in Azure that allows Azure resources to automatically manage their own identities in Azure Active Directory (Azure AD).
• These identities can be used to authenticate to any service that supports Azure AD authentication, without the need to manage credentials manually.
• When enabled, Azure automatically creates an identity for the resource in Azure AD, handles the lifecycle of the identity, and removes it when the resource is deleted.

Benefits of Using System-Assigned Managed Identities

1. Credential Management: Eliminates the need to store credentials in your code, which reduces the risk of credential leaks.
2. No needs to save or rotate the access keys
3. Seamless Integration: Easily integrate with Azure services that support Azure AD authentication.
4. Improved Security: Reduce the attack surface by not having to manage and rotate secrets manually.
5. Simplified Access Control: Use Azure RBAC (Role-Based Access Control) to grant access to resources.

Enabling System-Assigned Managed Identity

Let’s walk through the process of enabling a System-Assigned Managed Identity for an Azure Virtual Machine (VM).

1. Navigate to the Azure Portal: Open the Azure portal and go to your VM.
2. Identity Blade: In the VM’s settings, select the “Identity” blade.
3. Enable System-Assigned Identity: Under the “System-assigned” tab, set the status to “On” and save your changes.

Azure will create an identity for the VM in Azure AD, which you can now use to authenticate to other Azure services.

Create a Entra Group

Instead of assigning identities to each resource individually, we can create a group for all our resources and assign identities to the group. This simplifies management but could potentially grant higher access to a resource than necessary, so be cautious.

1. Go to Azure AD in the Azure portal.
2. Select “Groups” and then “New group.”
3. Enter the group’s details and create it.

4. Assign Resources to the Group. The identity will be visualized as Service principal

Assigning Permissions Using RBAC

Once the System-Assigned Managed Identity is enabled, you can grant it permissions to access other Azure resources.

1. Go to the Target Resource: Navigate to the resource you want the VM to access.
2. Access Control (IAM): Select “Access Control (IAM)” from the resource’s menu.
3. Add Role Assignment: Click on “Add” and then “Add role assignment.”
4. Select Role: Choose the appropriate role (e.g., Reader, Contributor).
5. Assign Access to Managed Identity: In the “Assign access to” dropdown, select “Managed identity,” then choose the VM’s identity.

Access to Azure SQL Server

About Azure SQL server will requires a further step.

1. After the assignment throught RBAC, it needs to create a database user for the identity and assign the roles (as explained here Migrate a Python application to use passwordless connections with Azure SQL Database — Azure SQL Database | Microsoft Learn )

CREATE USER [user-assigned-identity-name] FROM EXTERNAL PROVIDER;

-- just assigned what it needs, maybe just read is enough
ALTER ROLE db_datareader ADD MEMBER [user-assigned-identity-name]; 
ALTER ROLE db_datawriter ADD MEMBER [user-assigned-identity-name]; 
ALTER ROLE db_ddladmin ADD MEMBER [user-assigned-identity-name];
GO

2. In [user-assigned-identity-name], you can use the Group Name created before

3. Don’t use AZURE_CLIENT_ID, AZURE_TENANT_ID or AZURE_CLIENT_SECRET as environment variables, otherwise it will try to use them to authenticate instead of managed identity

4. This is the python code you can use to create the connection:

def get_db_connection(connection_string):
    
    if os.getenv("on_azure") == "True":
        auth = "authentication=ActiveDirectoryMSI;"
        connection_string = connection_string + auth
        conn = pyodbc.connect(connection_string)
    else:
        # this local part depends what kind of access you want to use running locally. 
        # It is not related to the managed identity copy covered 
        credential = DefaultAzureCredential(exclude_interactive_browser_credential=False)
        token_bytes = credential.get_token("https://database.windows.net/.default").token.encode("UTF-16-LE")
        token_struct = struct.pack(f'<I{len(token_bytes)}s', len(token_bytes), token_bytes)
        SQL_COPT_SS_ACCESS_TOKEN = 1256  # This connection option is defined by microsoft in msodbcsql.h
        conn = pyodbc.connect(connection_string, attrs_before={SQL_COPT_SS_ACCESS_TOKEN: token_struct})
    return conn

The connection string is something like:

Driver={ODBC Driver 17 for SQL Server};Server=tcp:<database-server-name>.database.windows.net,1433;Database=<database-name>;Encrypt=yes;TrustServerCertificate=no; Connection Timeout=30

About the driver, the documentation says ODBC Driver 18, but I had to use ODBC Driver 17, you can use this command to check the list of installed ODBC drivers

import pyodbc
pyodbc.drivers()

ref: Migrate a Python application to use passwordless connections with Azure SQL Database — Azure SQL Database | Microsoft Learn

5. To check which kind of access works better for you locally, you can follow this documentation: Connect using Microsoft Entra authentication — JDBC Driver for SQL Server | Microsoft Learn

Troubleshooting

if you get any authentication error, you should check the error if it is covered in this document:
azure-sdk-for-python/sdk/identity/azure-identity/TROUBLESHOOTING.md at main · Azure/azure-sdk-for-python · GitHub

Conclusion

By using System-Assigned Managed Identities in Azure, you can significantly enhance the security and manageability of your cloud resources. Assigning these identities to Entra groups further simplifies access management, allowing for efficient and secure operations. As cloud environments grow more complex, these tools become indispensable in maintaining robust security postures and streamlined operations.

By following the steps outlined in this article, you can leverage Azure’s powerful identity and access management features to ensure your resources are both secure and easily manageable.