C#, .Net and Azure

Managed identity and SQL databases

2020/02/15

I have blogged about managed identity many times already and it has quickly become a central part of any application hosted in Azure as it allows connecting various services seamlessly via Azure AD secured communication.

With its built in secret rotation and its lifetime bound to the underlying service it is not only the most convenient way to perform service to service communication but also the most secure.

For many services the steps are straightforward:

Explicit keyvault example:

var tokenProvider = new AzureServiceTokenProvider();
var kvClient = new KeyVaultClient((authority, resource, scope) 
    => tokenProvider.KeyVaultTokenCallback(authority, resource, scope));
builder.AddAzureKeyVault($"https://{keyVaultName}.vault.azure.net", kvClient, 
    new DefaultKeyVaultSecretManager());

Which can also be written shorter as just:

builder.AddAzureKeyVault($"https://{keyVaultName}.vault.azure.net");

For SQL servers the steps are similar and there are already various tutorials (Use a Windows VM system-assigned managed identity to access Azure SQL, Secure Azure SQL Database connection from App Service using a managed identity, ..).

However the tutorials are written in a way to make you use various tools (az cli, Powershell & Cloudshell) and the actual steps to implement the code in .Net Framework (or .Net Core) are vague and incomplete. They especially never touch on using MSI when debugging from a local machine.

Therefore, I decided to create a sample project using .Net Core & Entity Framework Core.

The project is hosted on github. It’s a simple razor pages app (using a .Net Core 3.1 template with which stores user accounts in a database).

The essential steps are in the github readme as well but I’ll describe them in more detail in this post:

Managed identity from a web app to SQL server

To make MSI work you need to create users inside the SQL server for each service that should connect.

First make sure the service you want to use has MSI enabled, next connect to the database (e.g. by using the query editor in Azure).

For every service you then need to execute these statements (where the name is that of the managed identitiy, aka the service name):

CREATE USER [service name] FROM EXTERNAL PROVIDER
ALTER ROLE db_datareader ADD MEMBER [service name]
ALTER ROLE db_datawriter ADD MEMBER [service name]

(If you have a webapp my-azure-app.azurewebsites.net then my-azure-app would be the service name).

Note that you need to make yourself Sql Active Directory Admin before executing the commands, see the documentation on github for details.

The output of all commands above will be:

Query succeeded: Affected rows: 0.

If need be, you can also add more roles.

After executing these commands the web app needs to be updated:

Update webapp to use MSI

Specify the connection string without a password:

Server=tcp:<my sql server>.database.windows.net,1433;Database=<my database>;MultipleActiveResultSets=True

The only code change required is in your DbContext class (if you’re using entity framework) to fetch the MSI authentication token.

I’ve added a bit more boilerplate code to support MSI and local db at the same time:

public class ApplicationDbContext : IdentityDbContext
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {
        // adapted from https://docs.microsoft.com/en-us/azure/app-service/app-service-web-tutorial-connect-msi#modify-aspnet-core
        var con = Database.GetDbConnection();
        if (con is SqlConnection connection)
        {
            if (connection.ConnectionString.Contains("(localdb)", StringComparison.OrdinalIgnoreCase))
                return; // no MSI needed when using local db

            // force sync 
            connection.AccessToken = new AzureServiceTokenProvider()
                .GetAccessTokenAsync("https://database.windows.net/")
                .Result;
        }
    }
}

Note: new AzureServiceTokenProvider() will cache the MSI token (so not every request fetches a new one). Its unintuitive but it relies on an internal static shared cache. I don’t agree with this design decision and would rather manage the lifetime myself but that’s the way it currently is.

Managed identity from a web app slot to SQL server

For slots it works just like with the regular webapp so you can repeat all the same steps from above:

The identity name of the slot will be in the format: <webapp>/slots/<slotName>

You can always find the exact name of the slot by going into Azure AD -> enterprise applications and filtering to all applications.

Managed identity from a local user to SQL server

MSI has the added benefit of also working with local user accounts.

If you are running your app from Visual Studio it will try these alternative authentication methods:

Note: There is an important detail when testing this in your private Azure subscription.

SQL MSI does not work for the account that created the azure subscription. When I tested it I received an exception:

Microsoft.Data.SqlClient.SqlException: ‘Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’.’

As per this excerpt from the docs:

[..] It must be a user that you created, imported, synced, or invited into Azure AD

For work environments this detail is usually irrelevant (as someone else created the subscription and you’ve most likely been added as either a guest user or member).

However for private subscription your account is usually the first user in the directory which is treated a bit special (it technically should be a guest account since it’s an external email added to the AD but because it’s the first account is is treated like a member account).

I don’t know the exact reason why this initial account won’t work with SQL managed identity but I tripped over it while testing and found the documentation on the limitation.

After I created a new member account and granted it permissions everything worked flawlessly for the new account.

Alternatively, you can also invite yourself (with a different email) as a guest user and use that for MSI.

Note that you must log in with this account locally (Visual Studio/az cli) in order for local MSI to work. Luckily Visual Studio allows multiple accounts and you can select which one should be used as MSI authentication fallback in Tools -> Options -> Azure App Authentication.


That’s it! You can find the project along with a step by step guide on how to get MSI working with SQL on github.