How to automatically deploy a passwordless Azure SQL Server

  Microsoft.EntityFrameworkCore.SqlServer
  Microsoft.EntityFrameworkCore.Tools

// AzureSqlDbContext.cs

public class AzureSqlDbContext(DbContextOptions<AzureSqlDbContext> options) : DbContext(options)
{
    public DbSet<Todo> Todos { get; set; }
}

public class Todo
{
    public int Id { get; set; }
    public required string Name { get; set; }
}

builder.Services.AddDbContext<AzureSqlDbContext>(options => options.UseSqlServer(builder.Configuration.GetConnectionString("AzureSql")));

dotnet ef migrations add InitialCreate -o ./Migrations

// main.bicep

param applicationDatabaseAdminsGroupName string
param applicationDatabaseAdminsObjectId string
param deployEnvironment string

var appName = 'azuresql'
#disable-next-line no-hardcoded-location
var location = 'North Central US'

// Already created & must have ability to read Entra. `User.Read.All`, `GroupMember.Read.All`, and `Application.Read.All` permissions.
resource dbIdentity 'Microsoft.ManagedIdentity/userAssignedIdentities@2023-07-31-preview' existing = {
  name: '${deployEnvironment}-${appName}-dbumi-01'
}

// Create the sql server.
resource sqlServer 'Microsoft.Sql/servers@2023-05-01-preview' = {
  name: '${deployEnvironment}-${appName}-dbs-01'
  location: location
  identity: {
    type: 'UserAssigned'
    userAssignedIdentities: {
      '${dbIdentity.id}': {}
    }
  }
  properties: {
    administrators: {
      // Only allow Azure AD connections (passwordless)
      azureADOnlyAuthentication: true
      // The server administrator - we are passing the Entra Object Id which correlates to an Entra Group called `APP_SQLDbAdmins
      sid: applicationDatabaseAdminsObjectId
      // The login name of the server administrator group.
      login: '${deployEnvironment}-${applicationDatabaseAdminsGroupName}'
    }
    // This identity will be used when determining what in Azure the identity can see - which is why we need User.Read.All, etc. in order to CREATE EXTERNAL USER's from Entra.
    primaryUserAssignedIdentityId: dbIdentity.id
  }

  // Create the sql server database.
  resource sqlServerDatabase 'databases' = {
    name: '${deployEnvironment}-${appName}-db-01'
    location: location
    sku: {
      name: 'Basic'
      tier: 'Basic'
    }
  }
}
var sqlServerName = '${sqlServer.name}${environment().suffixes.sqlServerHostname}'
output sqlServerName string = sqlServerName
output sqlServerDatabaseName string = sqlServer::sqlServerDatabase.name

// Create the application service plan and the web application.
resource appServicePlan 'Microsoft.Web/serverfarms@2022-03-01' = {
  name: '${deployEnvironment}-${appName}'
  location: location
  sku: {
    name: 'B1'
  }
}

// This application identity is permissioned into the database. You could use a system-assigned identity from the web app as long as you don't have additional things this identity needs.
resource applicationIdentity 'Microsoft.ManagedIdentity/userAssignedIdentities@2023-07-31-preview' = {
  name: '${deployEnvironment}-${appName}-appumi-01'
  location: location
}
output applicationIdentityName string = applicationIdentity.name

resource webApp 'Microsoft.Web/sites@2022-03-01' = {
  name: '${deployEnvironment}-ncus-${appName}-app-01'
  location: location
  properties: {
    httpsOnly: true
    serverFarmId: appServicePlan.id
    siteConfig: {
      appSettings: [
        {
          name: 'ConnectionStrings__AzureSqlDatabase'
          value: 'Server=tcp:${sqlServerName},1433;Initial Catalog=${sqlServer::sqlServerDatabase.name};Authentication=Active Directory Default;Encrypt=True;MultipleActiveResultSets=True;'
        }
        {
          name: 'AZURE_CLIENT_ID'
          value: applicationIdentity.properties.clientId // See https://github.com/MicrosoftDocs/azure-docs/issues/105359
        }
      ]
    }
  }
  identity: {
    type: 'UserAssigned'
    userAssignedIdentities: {
      '${applicationIdentity.id}': {}
    }
  }
}

-- initialcreate.sql

-- $(productTeamIdentity), $(env) and $(applicationIdentity) are replaced in the pipeline.
-- Example: $(productTeamIdentity) is replaced with an Entra Group called `Products Team`, which contains a group of users responsible for the application.
-- Example: $(applicationIdentity) would be replaced with the name of the user-managed identity resource created in the infrastructure as code (dev-azuresql-umi-01) which the application is running as.

IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE [name] = $(productTeamIdentityName))
BEGIN
    EXECUTE('CREATE USER [' + $(productTeamIdentityName) + '] FROM EXTERNAL PROVIDER');
END;

IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE [name] = $(applicationIdentity))
BEGIN
    EXECUTE('CREATE USER [' + $(applicationIdentityName) + '] FROM EXTERNAL PROVIDER');
END;
GO

IF ($(env) = 'dev')  
    BEGIN
        ALTER AUTHORIZATION ON SCHEMA::[dbo] TO $(productTeamIdentityName)
    END
ELSE    
    BEGIN 
        ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO $(productTeamIdentityName)
    END

ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO $(applicationIdentityName)
ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO $(applicationIdentityName)

GO

# azure-pipelines.yaml

# install the dotnet-ef tool which is used to generate migrations.
- script: dotnet tool install --global dotnet-ef 
  displayName: Install .NET EF Core tools
# Run a custom dotnet command, which we're choosing to run migrations.
- task: DotNetCoreCLI@2
  displayName: 'Create migration'
  inputs:
    command: custom
    custom: ef
    arguments: 'migrations script --idempotent --project src/WebApplication --output $(Build.ArtifactStagingDirectory)/Migrations/migration.sql'

# azure-pipelines.yaml

- stage: Build
  jobs:
  - job:
    pool:
      vmImage: ubuntu-latest
    displayName: Publish
    steps:
    - task: DotNetCoreCLI@2
      displayName: dotnet publish
      inputs:
        command: 'publish'
        publishWebProjects: true
        arguments: '-o $(Build.ArtifactStagingDirectory)'
        modifyOutputPath: false
    - script: dotnet tool install --global dotnet-ef 
      displayName: dotnet tool install dotnet-ef
    - task: DotNetCoreCLI@2
      displayName: Create migration
      inputs:
        command: custom
        custom: ef
        arguments: 'migrations script --idempotent --project src/WebApplication --output $(Build.ArtifactStagingDirectory)/Migrations/migration.sql'
    - publish: $(Build.ArtifactStagingDirectory)
      displayName: Publish to Azure DevOps
      artifact: drop

# azure-pipelines.yaml

- stage: Deploy_NonProd
  dependsOn: Build
  jobs:
  - job: deploy
    pool:
      vmImage: windows-latest
    steps:
      # First, deploy the infrastructure setup in the bicep file. Our bicep file outputs some variables, which we capture in $jsonResult and set them as pipeline variables.
      - task: AzureCLI@2
        displayName: 'Deploy Infrastructure'
        inputs:
          # Replace `applicationDatabaseAdminsGroupName`, `applicationDatabaseAdminsObjectId`, and `azuresql` subscription name.
          # applicationDatabaseAdminsGroupName is the Entra group that contains the service principal doing the deploy as well as any additional users who would administrate the database. Example: APP_SqlDbAdmins
          # applicationDatabaseAdminsObjectId is the ObjectId of the group 
          azureSubscription: azuresql-dev
          # I chose powershell because bash jsonifying is not as easy.
          scriptType: ps
          scriptLocation: inlineScript
          # Run the bicep file and write the output variables to Azure DevOps
          inlineScript: |
            $jsonResult = az deployment group create `
              --resource-group dev-ncus-azuresql-rg-01 `
              --template-file $(Build.SourcesDirectory)/iac/main.bicep `
              --parameters `
                  applicationDatabaseAdminsGroupName=App_SqlDbAdmins `
                  applicationDatabaseAdminsObjectId=37f7f235-527c-4136-accd-4a02d197296e ` 
                  deployEnvironment=dev `
              --mode Complete `
              | ConvertFrom-Json            
      
            $sqlServerName = $jsonResult.properties.outputs.sqlServerName.value
            $sqlServerDatabaseName = $jsonResult.properties.outputs.sqlServerDatabaseName.value
            $applicationIdentityName = $jsonResult.properties.outputs.applicationIdentityName.value
            Write-Host "##vso[task.setvariable variable=sqlServerName;]$sqlServerName"
            Write-Host "##vso[task.setvariable variable=sqlServerDatabaseName;]$sqlServerDatabaseName"
            Write-Host "##vso[task.setvariable variable=applicationIdentityName;]$applicationIdentityName"
      - download: current # download the current repository so we can get the initialcreate.sql file.
        artifact: drop
      - task: SqlAzureDacpacDeployment@1
        displayName: 'Setup initial permissions'
        condition: 
        inputs:
          azureSubscription: azuresql-dev
          authenticationType: 'servicePrincipal'
          deployType: 'sqlTask'
          serverName: $(sqlServerName)
          databaseName: $(sqlServerDatabaseName)
          # target the sql folder and find any sql files in there (will find initialcreate.sql)
          sqlFile: '$(Pipeline.Workspace)\**\sql\*.sql'
          # Pass arguments to the sql file. The -Variable argument will replace $() variables inside the sql file.
          # replace productTeamIdentityName with your Entra group containing your team.
          SqlAdditionalArguments: -Variable "productTeamIdentityName='Product Team'", "applicationIdentityName='$(applicationIdentityName)'", "env=dev"
      - task: SqlAzureDacpacDeployment@1
        displayName: 'Deploy EF Migration'
        inputs:
          azureSubscription: azuresql-dev
          authenticationType: 'servicePrincipal'
          deployType: 'sqlTask'
          serverName: $(sqlServerName)
          databaseName: $(sqlServerDatabaseName)
          sqlFile: '$(Pipeline.Workspace)\**\Migrations\migration.sql'
      - task: AzureWebApp@1
        displayName: Deploy Web Application
        inputs:
          appType: webApp
          azureSubscription: azuresql-dev
          appName: dev-ncus-azuresql-app-01
          package: $(Agent.BuildDirectory)/drop/WebApplication1/*.zip

// Rest of bicep
resource sqlServer 'Microsoft.Sql/servers@2023-05-01-preview' = {
  name: '${deployEnvironment}-${appName}-dbs-01'
  location: location
  identity: {
    type: 'UserAssigned'
    userAssignedIdentities: {
      '${dbIdentity.id}': {}
    }
  }
  properties: {
    administrators: {
      azureADOnlyAuthentication: true
      sid: applicationDatabaseAdminsObjectId
      login: '${deployEnvironment}-${applicationDatabaseAdminsGroupName}'
    }
    primaryUserAssignedIdentityId: dbIdentity.id
  }

  resource masterDb 'databases' = {
    name: ‘master’
    location: location
    properties: {}
  }

  resource sqlServerDatabase 'databases' = {
    name: '${deployEnvironment}-${appName}-db-01'
    location: location
    sku: {
      name: 'Basic'
      tier: 'Basic'
    }
  }
}

// Set up a log analytics workspace to ingest logs
resource logAnalyticsWorkspace 'Microsoft.OperationalInsights/workspaces@2022-10-01' = {
  name: '${deployEnvironment}-ncus-azuresql-law-01'
  location: location
  properties: {
    retentionInDays: 90
  }
}

resource sqlDiagnosticSettings ‘Microsoft.Insights/diagnosticSettings@2021-05-01-preview’ = {
  scope: sqlServer::masterDb
  name: 'sqlServerDiagnosticSettings'
  properties: {
    workspaceId: logAnalyticsWorkspace.id
    logs: [
      {
        // See more at https://learn.microsoft.com/en-us/azure/azure-sql/database/auditing-setup?view=azuresql#configure-auditing-for-your-server
        category: ‘SQLSecurityAuditEvents’
        enabled: true
      }
    ]
  }
}