I recently worked with a customer where we needed to authenticate against an Azure SQL Database using an Azure Managed Identity.
In this blog post, I'll introduce managed identities and the configuration required to access the database.
The example topology below shows the services that we'll deploy and how they interact with each other -
In our scenario, the "client" was an Azure Container App. The same approach applies to those other Azure services that can be assigned a managed identity, i.e. Azure virtual machines, Azure Kubernetes Services (AKS), Azure App Services, etc.
Managed Identities Overview
Managed identities are a feature of Azure Active Directory (now Entra) that allows you to authenticate and authorize your applications and Azure services without storing credentials in your code or configuration files.
They are assigned to Azure resources, such as Container Apps, and then granted access to other Azure services that support Azure AD authentication.
The benefits of using managed identities are:
You don't have to manage any secrets or keys for your applications
You can use role-based access control (RBAC) to grant permissions to your managed identities
You can rotate or revoke the identities at any time without affecting your applications.
There are two types of Managed Identities -
System Assigned - is created and deleted with the application and can only be used by that application
User Assigned - is a separate resource that can be assigned to multiple applications and has its own lifecycle
In this scenario, I’m using a User Assigned identity, but a System Assigned identity works in exactly the same way
Prerequisites
If you wish to recreate these steps, you'll need to start with the following Azure services deployed -
Azure Container App Environment (the consumption tier is sufficient for testing)
Azure Container App (this can be the default sample image)
Azure SQL Database
In the following steps, we’ll go through -
Creating the Managed Identity
Assigning the Managed Identity to the Container App
Granting permissions to the identity within the SQL database
Create a User Assigned Managed Identity
1. From the Azure Marketplace, search for User Assigned Managed Identity and click Create
2. Give it a name, say midemo-ca-id and click Review + Create, then Create
That's it; we now have an identity for our app.
Assign the Managed Identity to the Container App
1. Open the Container App, select the Identity pane and then User Assigned. Click Add
2. Select our newly created identity, midemo-ca-id and click Add
Our identity is now assigned to the container app -
Grant the Managed Identity access to the Azure SQL Database
Having created the Managed Identity, we now need to grant it the required permissions within the database.
Where possible, we’d normally use the built-in Azure RBAC roles to grant permissions to the MI, i.e. the ability to read secrets from Key Vault or pull images from a Container Registry.
Azure SQL is a little different. We must create the MI user as a “contained user” within the database and assign them the required SQL permissions.
Beware: the built-in SQL RBAC roles (SQL DB Contributor, SQL Server Contributor etc) aren’t quite what they appear. These roles provide Azure management access to the database and server. They don’t provide data plane access i.e. access to the database itself as a client or application.
The steps to create the contained user are as follows -
1. Open the SQL Database and browse to the Query editor pane. Login using credentials you created when deploying the SQL Server (or your Azure AD creds if using native Azure Authentication)
2. Enter and run the following SQL script to create the database user -
CREATE USER [<yourid>] FROM EXTERNAL PROVIDER;
GRANT CREATE SCHEMA TO [<yourid>];
GRANT CREATE TABLE TO [<yourid>];Replace the managed identity name with the name of your MI. In this example, we're assigning CREATE SCHEMA and CREATE TABLE permissions. You can customize these as needed.
All being well, you'll receive a Query succeeded: Affected rows: 0 message.
3. Next, we'll run an additional SQL script to confirm that the MI has been successfully added to the database.
select name as username,
create_date,
modify_date,
type_desc as type,
authentication_type_desc as authentication_type
from sys.database_principals
where type not in ('A', 'G', 'R', 'X')
and sid is not null
and name != 'guest'
order by username;The results from running the script confirm that midemo-ca-id has been added to the database -
Connect to the Azure SQL database using the Managed Identity
If our container is running a .NET application here are a few pointers on authenticating against the SQL DB using the container’s managed identity -
Use the Microsoft.Azure.Services.AppAuthentication and Microsoft.Data.SqlClient packages
Set the connection string to use Authentication = Active Directory Managed Identity
Get an access token using the managed identity and use it to query the Azure SQL database. You can do this by using the AzureServiceTokenProvider class from the Microsoft.Azure.Services.AppAuthentication package, and passing the token to the SqlConnection class from the Microsoft.Data.SqlClient package
A good Microsoft tutorial here also walks through the steps required (albeit with the client as a Windows Azure VM).
If you’re using other languages, such as Python, Java etc, you can refer to the following links:
Conclusion
In this blog post, I explored using Azure Managed Identities to access an Azure SQL database from Azure container apps.
This approach makes your app more secure by eliminating secrets from your code and configuration files.
You can also use managed identities to access other Azure services that support Azure AD authentication, such as Azure Key Vault and Azure Storage. For more information, see Managed Identities in Azure Container Apps.
As always, I hope you found this useful and comments etc. welcome.