Connecting to Azure SQL using Managed Identity

Peter O'Connor — Sun, 11 Sep 2022 12:43:06 +0000

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

Streaming Videos from Azure Blob Storage

Peter O'Connor — Fri, 22 Apr 2022 12:32:45 +0000

I recently had a project where I needed to stream video content from an Azure Blob Storage account. Should be easy enough right? Just plonk a <video> tag in and point it to the Blob URL (the blobs are publicly accessible). Well, it wasn't quite so straightforward. Here are some gotchas that might help you if you need to do something similar.

Why are we waiting?

For my first naïve attempt, a <video> tag pointing to the Blob URL, it turned out it was trying to download the complete video file first before playing, and the videos I wanted to stream were too large for that to be acceptable.

Full video download took 43 seconds, not great...

For streaming, the request needs to specify a range of data at a time instead of the entire video. This is supported via the Accept-Ranges HTTP header which returns a HTTP 206 Partial Content response. Support for this didn't come in until version '2011-08-18' of the Azure Storage service which is why the entire file was being downloaded first. You can specify the version when using an SDK or the REST API (via the x-ms-version header), but for public anonymous access where I wanted to point a <video> tag at a URL, this wasn't an option.

Set a global default version

Instead, you can set the default version globally at the account level using either one of the SDKs or the 'Set Blob Service Properties' REST API method. This is the part that probably isn't explained fully in the docs, particularly for public anonymous access, although it is hinted at here. Make a REST/SDK call setting the version to 2011-08-18 or later and your storage account will now support range requests and streaming will work as expected.

blobServiceClient.setProperties({
    defaultServiceVersion: "2021-04-10"
})
.then(res => console.log("Set Properties response", res))
.catch(err => console.error("Set Properties error", err));

How to set the default service version via the JavaScript SDK.

With the correct default storage version set, the video can start playing immediately (note the response status code for the video).

Note: To set the default version you will probably need a PUT CORS rule on your storage account and a role assignment for the identity making the call. Storage Account Contributor should do the trick.

Bonus: The partial content response will also let your users scrub the video.

Content Type

Another gotcha to be aware of is to make sure your blobs' content types are set correctly. This is usually set appropriately during upload but ensure it is a video/... type and hasn't defaulted to something like application/octet-stream.

Further Info

General Azure Storage versioning best practices

Forem: Peter O'Connor