Connecting to Azure SQL using Managed Identity

Connecting your Azure App Service Apps to an Azure SQL database using managed identity makes your app more secure as it eliminates secrets from your app such as credentials in connection strings. This post describes how to set this up. It is mostly taken from the official docs but with some extra information and caveats which I found useful to understand the process, hopefully others will too.

1. Enable AAD authentication on the Azure SQL Database

To be able to use managed identity authentication you need to assign an AAD user (or group) as the admin of the server. This cannot be a Microsoft account that you used to sign up for your Azure subscription. It must be a user that was created in AAD. For testing locally, you can just create a new user in AAD, but remember to sign-in as them and change the default password to prevent any expired password errors.

To set AAD authentication via the Azure portal go to the SQL Server resource > Azure Active Directory (under Settings) > Set admin (select the AAD user or group) > Save.

2. Modify the code to request a token

Your code must be modified so that it requests a token for SQL Database access from AAD which it adds to the database connection. Use the Azure.Identity.DefaultAzureCredential class here as it is flexible enough to work locally on dev and in prod. When running in App Service, it uses the app's system-assigned managed identity. When running locally, it can get a token using the logged-in identity of Visual Studio, VS Code, Azure CLI or Azure PS.

Be careful during local development using DefaultAzureCredential as the user you are signed in as in Visual Studio or VS Code might not have access to the database, as it is likely you won't be signed in as the AAD server admin you set up in step 1. In this case you can simply repeat step 4 below for the identity you are signed in as.

When running locally using DefaultAzureCredential I also saw some errors to do with expired refresh tokens. Re-signing in inside Visual Studio or Azure CLI (az login) should fix this.

The code to request a token usually goes in your DbContext object if using Entity Framework. The code example below is for an ASP.NET Framework app using Entity Framework:

public MyDatabaseContext() : base("name=MyDbConnection")
{
    var conn = (System.Data.SqlClient.SqlConnection)Database.Connection;
    var credential = new Azure.Identity.DefaultAzureCredential();
    var token = credential.GetToken(new Azure.Core.TokenRequestContext(new[] { "https://database.windows.net/.default" }));
    conn.AccessToken = token.Token;
}

Note https://database.windows.net/.default is the token endpoint for Azure SQL Databases. This string will be different if using Azure Database for MySQL (https://ossrdbms-aad.database.windows.net), or Azure Database for PostgreSQL (https://ossrdbms-aad.database.windows.net).

3. Update the connection string

Update the connection string to use AAD managed identity authentication instead of a username/password. This is the whole point of using the managed identity mechanism!

In either your web.config, App Service app settings, or both depending on your setup, replace the connection string with:

server=tcp:<server-name>.database.windows.net;database=<db name>;

Note how you only need the server and database parts. The authentication method is inferred to be AAD managed identity. Also, you don't need to specify a user id (User ID or UID) if using system-assigned managed identities (if using a user-assigned managed identity then you would need to specify the user id). This is because AAD authentication for Azure SQL uses a contained user, which differs from the traditional SQL Server approach of having both a server admin login and a database user. More information on this available here: https://docs.microsoft.com/en-us/sql/relational-databases/security/contained-database-users-making-your-database-portable?view=sql-server-ver16

4. Configure App Service app to connect to SQL Database using managed identity

The final step is to grant permissions to the App Service's managed identity to access the SQL Database. We do this by adding the managed identity as a user in the database.

Note: Your App Service app must have a managed identity by this point. Create one if it doesn't already exist. E.g. az webapp identity assign --resource-group ... --name <app-name>

Run the following SQL commands to grant the minimum permissions your app needs, e.g. (note that the square brackets are required):

CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<identity-name>];
ALTER ROLE db_datawriter ADD MEMBER [<identity-name>];
GO

Where identity-name is the name of the managed identity in AAD. If it's a system-assigned identity, the name is always the same as the name of your App Service app. For an AAD group, use the group's display name.

For a detailed list of available roles see here: https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver16#fixed-database-roles

To run the SQL commands above you can sign in to the SQL Database via the Azure Cloud Shell using your AAD server admin's credentials created in step 1. E.g:
sqlcmd -S <server-name>.database.windows.net -d <db-name> -U <aad-user-name> -P "<aad-password>" -G -l 30

Finally, publish your app with the code changes made in step 2 to Azure and you should now be successfully connecting to the database via managed identity with no secrets in sight. I'll drink to that ☕.