top of page
  • Writer's pictureAndrew Kelleher

Enabling Managed Identity access to Azure SQL DB

Updated: Nov 15, 2023

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 -

  1. Use the Microsoft.Azure.Services.AppAuthentication and Microsoft.Data.SqlClient packages

  2. Set the connection string to use Authentication = Active Directory Managed Identity

  3. 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.

6,230 views0 comments

コメント


bottom of page