How to use Azure Developer CLI to set up Azure SQL AAD administrator & IP firewall rules at deployment time

I have started using the Azure Developer CLI to standardize & simplify my deployments to Azure (both Infrastructure as Code deployment & application deployment).

I needed to deploy an Azure SQL database and I wanted it to use Azure AD authentication & have IP firewall rules set up by default to allow my local computer to access the DB, but not anyone else.

Normally, I would have had to manually look up my AAD user principal name & IP address manually and pass them in as command line arguments.

Luckily, the Developer CLI includes command and event hooks. These let me inject my own code as various stages of the deployment.

In my case, I wanted to look up these two values (user principal & IP address) and then pass them into the Bicep templates I am using to do my deployment.

Bicep template for deploying Azure SQL

Here is my template for creating the Azure SQL database. Notice that I accept the sqlAdminLoginName, sqlAdminLoginObjectId & sqlClientIpAddress as parameters to the template.

param sqlServerName string
param sqlDbName string
param location string
param sqlAdminLoginName string
param sqlAdminLoginObjectId string
param sqlClientIpAddress string

resource sqlServer 'Microsoft.Sql/servers@2022-05-01-preview' = {
  name: sqlServerName
  location: location
  tags: tags
  properties: {
    administrators: {
      azureADOnlyAuthentication: true
      administratorType: 'ActiveDirectory'
      tenantId: subscription().tenantId
      principalType: 'User'
      login: sqlAdminLoginName
      sid: sqlAdminLoginObjectId
    publicNetworkAccess: 'Enabled'

resource sqlDB 'Microsoft.Sql/servers/databases@2022-05-01-preview' = {
  parent: sqlServer
  name: sqlDbName
  tags: tags
  location: location
  sku: {
    name: 'Standard'
    tier: 'Standard'

resource clientFirewallRules 'Microsoft.Sql/servers/firewallRules@2022-08-01-preview' = {
  name: 'AllowClientIp'
  parent: sqlServer
  properties: {
    startIpAddress: sqlClientIpAddress
    endIpAddress: sqlClientIpAddress

Bicep parameters

In my main.parameters.json file, I use the Developer CLI placeholders for the values I need populated when the template is deployed. The Developer CLI will substitute the correct values.

    "$schema": "",
    "contentVersion": "",
    "parameters": {
      "environmentName": {
        "value": "${AZURE_ENV_NAME}"
      "location": {
        "value": "${AZURE_LOCATION}"
      "sqlAdminLoginName": {
        "value": "${AZURE_PRINCIPAL_NAME}"
      "sqlAdminLoginObjectId": {
        "value": "${AZURE_PRINCIPAL_ID}"
      "sqlClientIpAddress": {
        "value": "${MY_IP}"

The AZURE_ENV_NAME, AZURE_LOCATION & AZURE_PRINCIPAL_ID are default environment variables that the Azure Developer CLI will always have & populate. I needed to add and populate the AZURE_PRINCIPAL_NAME and MY_IP environment variables.

preprovision Azure Developer CLI hook

Now for the magic. The Developer CLI exposes the preprovision hook that I can add my own code into that will run before the Bicep templates are deployed.

In the azure.yaml file, I can add my custom code (PowerShell in my case).

name: wth-logicapp
    shell: pwsh
    continueOnError: false
    interactive: true
    run: |
      $upn = $(az ad signed-in-user show --query userPrincipalName -o tsv)
      azd env set AZURE_PRINCIPAL_NAME $upn

      $myIP = $(Invoke-WebRequest -Uri "").Content
      azd env set MY_IP $myIP
    project: ./src/api
    language: dotnet
    host: function

In this example, I run the Azure CLI command to find out who the signed-in user is and then query for the userPrincipalName. I also query a common website that lists what your current IP address is. With this information, I can populate the two environment variables.


Now that I have written my Bicep templates, set up the parameters file & added code to the preprovision hook, I can run my code.

azd up

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *