How to connect a Web App to a SQL database on Azure using Managed Identities?

TL;DR: what am I missing from my bicep set-up for my web app to connect to SQL?

I'm new to Azure and I've been trying to set up a bicep "stack" for a project I'm working on. For some reason, my web app cannot connect to the SQL database. I'm not sure what my bicep recipe is missing.

param location string = resourceGroup().location

var environment = 'myveryownproject'
var sqlDbContributorRole = subscriptionResourceId('Microsoft.Authorization/roleDefinitions', '9b7fa17d-e63e-47b0-bb0a-15c516ac86ec')

func name(abbreviation string, environment string) string => 
  '${abbreviation}-${environment}'

func uname(abbreviation string, environment string, unique string) string => 
  '${abbreviation}-${environment}-${unique}'

resource applicationsSubnet 'Microsoft.Network/virtualNetworks/subnets@2024-01-01' = {
  name: name('snet', environment)
  parent: network
  properties: {
    serviceEndpoints: [
      {
        service: 'Microsoft.Sql'
      }
    ]
    addressPrefix: '10.0.0.0/24'
    delegations: [
      {
        name: name('snetd', environment)
        properties: {
          serviceName: webAppPlan.type
        }
      }
    ]
  }
}

resource network 'Microsoft.Network/virtualNetworks@2024-01-01' = {
  name: name('vnet', environment)
  location: location
  properties: {
    addressSpace: {
      addressPrefixes: [
        '10.0.0.0/16'
      ]
    }
  }
}

resource webAppPlan 'Microsoft.Web/serverfarms@2023-12-01' = {
  name: name('asp', environment)
  location: location
  kind: 'linux'
  properties: {
    reserved: true
  }
  sku: {
    name: 'B1'
  }
}

resource webApp 'Microsoft.Web/sites@2023-12-01' = {
  name: name('app', environment)
  location: location
  identity: {
    type: 'SystemAssigned'
  }
  properties: {
    serverFarmId: webAppPlan.id
    siteConfig: {
      linuxFxVersion: 'DOTNETCORE|8.0'
    }
    httpsOnly: true
    virtualNetworkSubnetId: applicationsSubnet.id
  }
}

resource databaseServer 'Microsoft.Sql/servers@2021-11-01' = {
  name: name('sql', environment)
  location: location
  properties: {
    minimalTlsVersion: '1.2'
    administrators: {
      administratorType: 'ActiveDirectory'
      sid: '<<<MY SID>>>'
      login: '<<<MY LOGIN>>>'
      azureADOnlyAuthentication: true
      principalType: 'User'
    }
  }
  identity: {
    type: 'SystemAssigned'
  }
}

resource subnetRuleForDatabaseServer 'Microsoft.Sql/servers/virtualNetworkRules@2023-08-01-preview' = {
  name: name('sqlnet', environment)
  parent: databaseServer
  properties: {
    virtualNetworkSubnetId: applicationsSubnet.id
  }
}

resource database 'Microsoft.Sql/servers/databases@2023-08-01-preview' = {
  name: name('sqldb', environment)
  location: location
  properties: {
    zoneRedundant: false
  }
  sku: {
    name: 'GP_S_Gen5'
    tier: 'GeneralPurpose'
    family: 'Gen5'
    capacity: 1
  }
  parent: databaseServer
}

resource webAppDatabaseAccessRole 'Microsoft.Authorization/roleAssignments@2022-04-01' = {
  name: guid(name('role', environment))
  properties: {
    principalId: webApp.identity.principalId
    roleDefinitionId: sqlDbContributorRole
  }
}

My web app is a simple ASP.NET Core app that uses Entity Framework Core

var builder = WebApplication.CreateBuilder(args);
// ...
builder.Services.AddDbContext<DataContext>((sp, options) =>
{
    var configuration = sp.GetRequiredService<IConfiguration>();
    var connectionString = configuration.GetConnectionString("MyDbConnectionString");
    options.UseSqlServer(connectionString);
});
// ...
var app = builder.Build();
// ...
var conf = sp.GetRequiredService<IConfiguration>();
var connection = conf.GetConnectionString("MyDbConnectionString");
app.Logger.LogInformation($"The connection is: {connection}");
sp.GetRequiredService<DataContext>().Database.EnsureCreated();

My application throws an error Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user '<token-identified principal>'. when running on Azure. If I add my own IP to the firewall and connect using the same code locally there's no issue. I'm not sure what I'm missing. Thanks!

The web app has the permission SQL DB Contributor (https://learn.microsoft.com/en-us/azure/role-based-access-control/built-in-roles/databases#sql-db-contributor) which does not allow data acccess to the database.

Either grant the managed identity the Sql Server Admin role (Which is against the principle of least privilege) or create a db contained user (https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-overview?view=azuresql#logins-server-principals) and grant it the required permissions, like db_datareader and db_datawriter.