Managed Identity with SQL Server & Entity Framework

It's a good idea if possible to move away from credentials in strings where possible, luckily using system assigned managed identity with Azure makes this pretty easy to do with just a small list of things to sort. I'm going to assume you have an existing dotnet application which is connecting to a SQL Server instance hosted within Azure and you've already setup a directory Admin for that server. You shouldn't need any RBAC(role based access control) setup added. We need to work through the following list to get us setup with a system assigned managed identity connection to our sql server instance.

• Marking our resource to use system assigned identity in our bicep
• Adapting the connection string to use 'Azure Managed Identity'
• Creating a DB user for our app
• Small bit of setup within our program.cs

I like Bicep for defining resources within Azure, it's repeatable, trusted and frankly far more readable than ARM templates (which is what Bicep gets compiled into before running anyway) I find it's more similar to a scripting language than reams of JSON, it even allows for creating reusable modules so I'm going to give my example for deploying an API resource as a bicep snippet.

resource api 'Microsoft.Web/sites@2022-03-01' = {
  name: apiName
  location: appHostingProfile.location
  identity: {
    type: 'SystemAssigned'
  }
  properties: {
    serverFarmId: serverFarmId
    httpsOnly: true
    siteConfig: {
      alwaysOn: true
      ftpsState: 'FtpsOnly'
      minTlsVersion: '1.2'
      connectionStrings: [
        {
          name: 'MyConnection'
          connectionString: 'Server=tcp:myserver.database.windows.net,1433;Initial Catalog=mydatabase;Encrypt=True;Authentication="Active Directory Managed Identity";'
          type: 'SQLAzure'
        }
      ]
    }
  }
}

The section

  identity: {
    type: 'SystemAssigned'
  }

Is essential here as it tells Azure that we want a system assigned identity, it lives with the azure resource and gets deleted with it. If you search for microsoft entra identities within your azure subscription, you'll find one with the same name as your api or web resource. You can alternatively create a user defined managed identity, which exists seperately from the resource and can be managed entirely seperately.

Within the connection string, you'll also notice this

Authentication="Active Directory Managed Identity"

It explictly tells the authentication classes which method to attempt to connect to the DB with. I prefer to have it defined like this on your azure resource, it's more explicit so you can be sure that you're connecting with your managed identity. You can alternatively use this line

Authentication="Active Directory Default"

It'll attempt to use credentials in the order defined at this Azure docs page. I'd recommend using this locally, in some appsettings.development.json which is designed to override your config running on your server when running your application locally.

Within your database, you'll need a user for your system managed identity, please make sure you create your user within the database you intend to use and not the system wide master db. Something like this should sort you out...

CREATE USER [$apiName] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [$apiName];
ALTER ROLE db_datawriter ADD MEMBER [$apiName];

Next we'll have a look at the small code snippet you'll to get the managed identity running with entity framework,

services.AddDbContext<MyDbContextClass>(options =>
{
    var sqlConnection = new SqlConnection(builder.Configuration.GetConnectionString("MyConnection"));

    options.UseSqlServer(sqlConnection);
});

It should be pretty simple as above, assuming you have already created a context class when using entity framework. That should be you all need to setup and get running with a C# app with Entity framework which is connecting to your Sql server running on Azure using a system managed identity!