CODEWITHSUNDEEP
azureazure-web-app-serviceazure-sql-databaseazure-bicepazure-managed-identity
GeorgeF0
GeorgeF0

Reputation: 431

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!

Upvotes: 0

Views: 105

Answers (1)

Alex
Alex

Reputation: 8116

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.

Upvotes: 0

Related Questions