ASP.NET + SQL Server + Managed Identity with BICEP

Connection strings with plaintext passwords have long been a security anti-pattern. While using dedicated database users is better than admin credentials, any plaintext authentication poses security risks.

In traditional on-premise environments, organizations typically mandate integrated authentication using Active Directory service accounts, enabling centralized permission management and enhanced security.

Azure implements this concept through managed identities. Azure services can utilize either system-assigned identities (unique per service instance) or user-assigned identities (reusable across services). While configuring managed identities through the Azure Portal is straightforward and well-documented, implementing them programmatically via IaaS (BICEP or ARM) requires more attention.

Plan of action

In this article I’ll provide all the different parts for a BICEP script you need to fully deploy App Service (with App Service Plan), the SQL Database and any necessary identities to setup the permissions between them. Note: You can download all the files referenced in this article here and skip to the execution part at the end if you want to.

The Goal

We want to just run BICEP files and at the end be able to call the URL of our deployed AppService and see a successful connection to the database.

Deploying Rights are Trickier than Standard Resources

Deploying the base resources (App Service, App Service Plan, SQL Server and Database) is fairly straight forward with BICEP. But in order to add the identity that the AppService will use to the db_reader and db_writer group of the SQL database there are a couple of steps we need to do:

1. Run SQL Server with an identity that has Directory.Read.All permissions.
2. Create the SQL login for the AppService managed identity (SQL Server uses the permissions from Step 1 to lookup the user in the Entra).
3. Add the login to the right database groups to give the AppService access.

We will need to use deploymentScripts and the new beta BICEP graphExtensions in order to deploy those rights.

Deployment Identity and 2 seperate files

Can’t this be one BICEP File? Well we need a temporary user managed identity (created by the first BICEP file) during the deployment of the second BICEP file in order to run the deploymentScript with the right permissions. This can be deleted afterwards but as of right now BICEP (well the underlying) ARM can only create resources currently.

Due to the graphExtensions requiring the identities we create to already exist in Entra before running we need to split the deployent into 2 files. From my experience so far you don’t have to wait more than a few seconds to run the second BICEP file, but you cannot run both steps in one file.

Step 1: Parameters and Identities

In the first step we create just the first BICEP file (part1.bicep) to define our parameters and two user managed identities.

• The first identity (sqlServerIdentity) is the one used for the SQL Server instance.
• The second identity (temporaryDeploymentUserAssignedIdentity) is a temporary identity we use to run our deploymentScripts which add permissions on the SQL server. (Deployment scripts as part of BICEP don’t run as the deploying user, but rather need such an identity.)

We will run this part first to ensure they are available for graphExtensions when we run the next step.

@description('The SID of the Administrator User for the SQL Server.')
param dbAdminSid string

@description('Prefix used for grouping resources (2-10 characters), e.g. project or product short code')
param prefix string

@description('Environment name that determines the deployment context, e.g. prod, staging, dev')
@minLength(2)
@maxLength(10)
@allowed([
  'prod'
  'staging'
  'dev'
])
param environment string

@description('Geographic region code denoting where these resources belong to, e.g. us, eu. Note that resourceGroupLocation should be set to the actual Azure region.')
@minLength(2)
@maxLength(10)
@allowed([
  'us'
  'eu'
])
param region string

// Identity for SQL Server to run as
resource sqlServerIdentity 'Microsoft.ManagedIdentity/userAssignedIdentities@2023-01-31' = {
  name: '${prefix}-${environment}-${region}-sqlserver-1-identity'
  location: resourceGroup().location
}

// A temporary identity needed to perform specific tasks, this should be deleted later
resource temporaryDeploymentUserAssignedIdentity 'Microsoft.ManagedIdentity/userAssignedIdentities@2023-01-31' = {
  name: '${prefix}-${environment}-${region}-temporary-deployment-identity'
  location: resourceGroup().location
}

Step 2: Create the main resources

Next we create the BICEP file that contains most of our resource definitions.

The resources we need to define are:

• appServicePlan: A linux server farm using the Basic tier
• appService: The app service for a .NET 9 App
• sqlServerAdminGroup: The group to administer the sql server consisting of the dbAdminSid specified in the parameters and our temporary deployment identity (so we can add a login)
• sqlServer: The logical SQL server
• database: The actual SQL server database on Basic tier (DPU mode)
• databaseFirewall: The firewall setting to let Azure services connect to the database
• sqlServerIdentity: The identity used by SQL server
• sqlServerIdentityRoleAssignment: Adds the identity to the Directory.Read.All role in Entry to allow adding of identities as logins to SQL Server (used for adding the AppService identity)
• addAppServiceUserToDatabase: The actual script to add the AppService identity with db_reader and db_writer roles in the database.
• appServiceConnectionStrings: Adds the connection string to the AppService

The naming of each resource follows this pattern <prefix>-<environment>-<region>-<servicetype>-<additional> (using variables defined in the parameters file) which gives you resource names like demo1-dev-us-appservice-web1.

extension microsoftGraphV1

// Some of the same params as before
param dbAdminSid string
param prefix string
param environment string
param region string

// Get existing (global) Graph Service Principal
resource graphSpn 'Microsoft.Graph/[email protected]' existing = {
  appId: '00000003-0000-0000-c000-000000000000'
}

// Get identity created in Part 1
resource temporaryDeploymentUserAssignedIdentity 'Microsoft.ManagedIdentity/userAssignedIdentities@2023-01-31' existing = {
  name: '${prefix}-${environment}-${region}-temporary-deployment-identity'
}

// App Service Plan
resource appServicePlan 'Microsoft.Web/serverfarms@2024-04-01' = {
  name: '${prefix}-${environment}-${region}-appserviceplan-linux1'
  location: resourceGroup().location
  sku: {
    name: 'B1'
    tier: 'Basic'
  }
  kind: 'linux'
  properties: {
    reserved: true
  }
}

// App Service
resource appService 'Microsoft.Web/sites@2024-04-01' = {
  name: '${prefix}-${environment}-${region}-appservice-web1'
  location: resourceGroup().location
  properties: {
    serverFarmId: appServicePlan.id
    siteConfig: {
      linuxFxVersion: 'DOTNETCORE|9.0'
      alwaysOn: true
    }
  }
  identity: {
    type: 'SystemAssigned'
  }
}

// This is the administrator group for the sql server which includes
// the managed identity and the specific admin user
resource sqlServerAdminGroup 'Microsoft.Graph/[email protected]' = {
  uniqueName: '${prefix}-${environment}-${region}-sqlserver-1-admins'
  displayName: '${prefix}-${environment}-${region}-sqlserver-1 Administrators'
  mailEnabled: false
  mailNickname: '${prefix}-${environment}-${region}-sqlserver-1-admins'
  securityEnabled: true
  owners: [
    dbAdminSid
  ]
  members: [
    dbAdminSid
    temporaryDeploymentUserAssignedIdentity.properties.principalId
  ]
}

// SQL Server
resource sqlServer 'Microsoft.Sql/servers@2024-05-01-preview' = {
  name: '${prefix}-${environment}-${region}-sqlserver-1'
  location: resourceGroup().location
  properties: {
    publicNetworkAccess: 'Enabled'
    administrators: {
      administratorType: 'ActiveDirectory'
      principalType: 'Group'
      login: '${prefix}-${environment}-${region}-sqlserver-1-admin-group'
      sid: sqlServerAdminGroup.id
      tenantId: subscription().tenantId
      azureADOnlyAuthentication: true
    }
    restrictOutboundNetworkAccess: 'Disabled'
    primaryUserAssignedIdentityId: sqlServerIdentity.id
  }
  identity: {
    type: 'UserAssigned'
    userAssignedIdentities: {
      '${sqlServerIdentity.id}': {}
    }
  }
}

// SQL Database
resource database 'Microsoft.Sql/servers/databases@2024-05-01-preview' = {
  parent: sqlServer
  name: '${prefix}-${environment}-${region}-database-web1'
  location: resourceGroup().location
  sku: {
    name: 'Basic'
    tier: 'Basic'
    capacity: 5
  }
  properties: {
    collation: 'SQL_Latin1_General_CP1_CI_AS'
  }
}

// SQL Firewall Rule
resource databaseFirewall 'Microsoft.Sql/servers/firewallRules@2024-05-01-preview' = {
  parent: sqlServer
  name: 'AllowAllWindowsAzureIps'
  properties: {
    startIpAddress: '0.0.0.0'
    endIpAddress: '0.0.0.0'
  }
}

resource sqlServerIdentity 'Microsoft.ManagedIdentity/userAssignedIdentities@2023-01-31' existing = {
  name: '${prefix}-${environment}-${region}-sqlserver-1-identity'
}

// Assign the Directory.Read.All role to the SQL Server identity - this needs to be close to the last step as the identity it takes a while to propagate
resource sqlServerIdentityRoleAssignment 'Microsoft.Graph/[email protected]' = {
  appRoleId: '7ab1d382-f21e-4acd-a863-ba3e13f7da61'
  principalId: sqlServerIdentity.properties.principalId
  resourceId: graphSpn.id
}

// Add AppService managed identity as db_writer and db_reader
resource addAppServiceUserToDatabase 'Microsoft.Resources/deploymentScripts@2023-08-01' = {
  name: 'Grant_AppService_Identity_Database_Rights'
  location: resourceGroup().location
  kind: 'Azurebash'
  properties: {
    azbashVersion: '10.0'
    scriptContent: '''
      $sqlServerFqdn = "$env:SQL_SERVER_NAME"
      $sqlDatabaseName = "$env:SQL_DATABASE_NAME"
      $sqlUser = "$env:MANAGED_IDENTITY_NAME"

      # Install SqlServer module
      Install-Module -Name SqlServer -Force -AllowClobber -Scope CurrentUser
      Import-Module SqlServer

      $sqlCmd = @"
      -- Create the user with details retrieved from Entra ID
      CREATE USER [$sqlUser] FROM EXTERNAL PROVIDER

      -- Assign roles to the new user
      ALTER ROLE db_datareader ADD MEMBER [$sqlUser]
      ALTER ROLE db_datawriter ADD MEMBER [$sqlUser]
"@
# Note: the string terminator must not have whitespace before it, therefore it is not indented.

      Write-Host $sqlCmd

      $connectionString = "Server=tcp:${sqlServerFqdn},1433;Initial Catalog=${sqlDatabaseName};Authentication=Active Directory Default;"

      Invoke-Sqlcmd -ConnectionString $connectionString -Query $sqlCmd

    '''
    timeout: 'PT5M'
    retentionInterval: 'PT1H'
    cleanupPreference: 'OnSuccess'
    environmentVariables: [
      {
        name: 'SQL_SERVER_NAME'
        value: sqlServer.properties.fullyQualifiedDomainName
      }
      {
        name: 'SQL_DATABASE_NAME'
        value: database.name
      }
      {
        name: 'MANAGED_IDENTITY_NAME'
        value: appService.name
      }
    ]
  }
  identity: {
    type: 'UserAssigned'
    userAssignedIdentities: {
      '${temporaryDeploymentUserAssignedIdentity.id}': {}
    }
  }
}

// App Service Connection String Configuration
resource appServiceConnectionStrings 'Microsoft.Web/sites/config@2024-04-01' = {
  parent: appService
  name: 'connectionstrings'
  properties: {
    DefaultConnection: {
      value: 'Server=tcp:${sqlServer.properties.fullyQualifiedDomainName},1433;Initial Catalog=${database.name};Authentication=Active Directory Default;'
      type: 'SQLAzure'
    }
  }
}

Step 3: Deploy a minimal ASP.NET site to the AppService for testing (Optional)

These two files are enough to deploy all resources and rights. If you would like to test the rights are correctly set, I have this repo that is a minimal ASP.NET project that tries connecting to the database and gives you back the result.

Use the following BICEP last to configure that repo as the source for the AppService and run a sync (deploying the file). After a few minutes you will be able to call the URL of the AppService and should get a successful connnection response. (You can check on the deployment in Azure Portal under Deployment Center > Deployments in Azure Portal.)

// Some of the same params as before
param dbAdminSid string
param prefix string
param environment string
param region string

// OPTIONAL: This will deploy a minimal ASP.NET App that tries connecting
// to the database using the managed identity.
resource appService 'Microsoft.Web/sites@2024-04-01' existing = {
  name: '${prefix}-${environment}-${region}-appservice-web1'
}

resource appServiceSourceControl 'Microsoft.Web/sites/sourcecontrols@2024-04-01' = {
  parent: appService
  name: 'web'
  properties: {
    repoUrl: 'https://github.com/aduggleby/Samples-AspNetConnectsToSqlWithIdentity'
    branch: 'master'
    isManualIntegration: true
  }
}

resource temporaryDeploymentUserAssignedIdentity 'Microsoft.ManagedIdentity/userAssignedIdentities@2023-01-31' existing = {
  name: '${prefix}-${environment}-${region}-temporary-deployment-identity'
}

resource addTemporaryDeploymentUserToContributorToInitiateSync 'Microsoft.Authorization/roleAssignments@2022-04-01' = {
  name: guid(resourceGroup().id, temporaryDeploymentUserAssignedIdentity.id, 'Contributor')
  properties: {
    principalId: temporaryDeploymentUserAssignedIdentity.properties.principalId
    roleDefinitionId: subscriptionResourceId(
      'Microsoft.Authorization/roleDefinitions',
      'b24988ac-6180-42a0-ab88-20f7382dd24c'
    ) // Contributor role
    principalType: 'ServicePrincipal'
  }
}

// Deployment script to sync the repository
resource appServiceSyncScript 'Microsoft.Resources/deploymentScripts@2020-10-01' = {
  name: 'Initiate_Sync_from_repo_for_${appService.name}'
  location: resourceGroup().location
  kind: 'AzureCLI'
  identity: {
    type: 'UserAssigned'
    userAssignedIdentities: {
      '${temporaryDeploymentUserAssignedIdentity.id}': {}
    }
  }
  properties: {
    azCliVersion: '2.50.0'
    timeout: 'PT30M'
    retentionInterval: 'P1D'
    environmentVariables: [
      {
        name: 'RESOURCEGROUP'
        value: resourceGroup().name
      }
      {
        name: 'APPSERVICE'
        value: appService.name
      }
    ]
    scriptContent: '''
      az webapp deployment source sync \
        --name $APPSERVICE \
        --resource-group $RESOURCEGROUP
    '''
  }
  dependsOn: [
    appServiceSourceControl
  ]
}

Step 4: Create the parameters file

The BICEP scripts use a handful of variables that you need to provide. This is easiest done with a parameters.json file. (We cannot use a .bicepparam file here as they are linked 1:1 with a specific BICEP script and we want to reuse the same parameters for every BICEP file.)

The required value are:

• dbAdminSid: Sid of the Azure AD user that will be added to the SQL Server admin group
• prefix: Prefix used for grouping resources (2-10 characters), e.g. project or product short code
• environment: Environment name that determines the deployment context, e.g. prod, staging, dev
• region: Geographic region code denoting where these resources belong to, e.g. us, eu. Note that resourceGroupLocation should be set to the actual Azure region.

The last three are only used for naming the resources.

{
	"$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentParameters.json#",
	"contentVersion": "1.0.0.0",
	"parameters": {
		"dbAdminSid": {
			"value": "123abc"
		},
		"prefix": {
			"value": "demo1"
		},
		"environment": {
			"value": "dev"
		},
		"region": {
			"value": "us"
		}
	}
}

Step 5: Run the deployment

Until graphExtensions is out of beta, you need to create a bicepconfig.json to enable this experimental feature.

{
	// See https://aka.ms/bicep/config for more information on Bicep configuration options
	// Press CTRL+SPACE/CMD+SPACE at any location to see Intellisense suggestions
	"analyzers": {
		"core": {
			"rules": {
				"no-unused-params": {
					"level": "warning"
				}
			}
		}
	},
	"experimentalFeaturesEnabled": {
		"extensibility": true
	},
	"extensions": {
		"microsoftGraphV1": "br:mcr.microsoft.com/bicep/extensions/microsoftgraph/v1.0:0.1.9-preview"
	}
}

I prefer to deploy BICEP files into a new region, but you can use an existing (or even deploy to a subscription but that would required changes to the file).

az group create --name <RG> --location westeurope

The deploy each BICEP, replacing RG with the resource group name you want to use.

az deployment group create -g <RG> -f part1.bicep -p parameters.json
az deployment group create -g <RG> -f part2.bicep -p parameters.json
az deployment group create -g <RG> -f part3-optional.bicep -p parameters.json

Post deployment step

If deployment is successful you can now delete the <prefix>-<environment>-<region>-temporary-deployment-identity identity that we needed to deploy using this Azure CLI command (replacing USERNAME and RG with the respective values)

az identity delete --name USERNAME --resource-group <RG>

Conclusion

It works! With the right pieces in the right order we can achieve an automated deployment of ASP.NET apps with SQL Server using managed identities.

Could it be smoother? Absolutely. Having to run two parts may be a limitation of the beta graphExtensions and I hope we can do this all in one file in future as using identities is Microsoft’s recommended approach after all. But in the end two files and an (optional) manual deleting of an identity is not the worst trade off for achieving our goal.