Create & Delete firewall rule to Azure SQL (for Azure DevOps deploy using az-cli)

azure_sql_firewall.sh

## Set these variables to Azure DevOps Pipeline ##
# subscriptionId, resourceGroupName, serverName


### TASK: Create Firewall Rule ###
# Pick variables
subscriptionId=$(subscriptionId)
resourceGroupName=$(resourceGroupName)
serverName=$(serverName)

# Settings
ipaddr=$(curl ipinfo.io/ip)
dateNow=$(TZ=":Asia/Taipei" date +%Y%m%d_%H%M)
firewallRuleName="Azure_DevOps_Created_at__${dateNow}"

# Showing metas
echo "executed firewall name is ${firewallRuleName}, IP address is ${ipaddr}"

# add new firewall rule
az sql server firewall-rule create \
  --start-ip-address $ipaddr \
  --end-ip-address $ipaddr \
  --name "${firewallRuleName}" \
  --resource-group "${resourceGroupName}" \
  --server "${serverName}" \
  --subscription $subscriptionId

  
### TASK: Remove firewall rule ###
# Pick variables
resourceGroupName=$(resourceGroupName)
serverName=$(serverName)

# delete firewall rules
firewallIds=$( \
az sql server firewall-rule list \
  --resource-group "${resourceGroupName}" \
  --server "${serverName}" \
  --query "[?contains(name, 'Azure_DevOps_Created_at__')].id" \
  --output tsv \
)
az sql server firewall-rule delete --ids "${firewallIds}"


### Raw API ###
bodyTemplate='{ "properties": { "startIpAddress": "%s", "endIpAddress": "%s" } }'
body=$(printf "$bodyTemplate" "$ipaddr" "$ipaddr")

az rest --method PUT \
  --url "https://management.azure.com/subscriptions/${subscriptionId}/resourceGroups/${resourceGroupName}/providers/Microsoft.Sql/servers/${serverName}/firewallRules/${firewallRuleName}?api-version=2014-04-01" \
  --body "${body}" \

az rest --method DELETE \
  --url "https://management.azure.com/subscriptions/${subscriptionId}/resourceGroups/${resourceGroupName}/providers/Microsoft.Sql/servers/${serverName}/firewallRules/${firewallRuleName}?api-version=2014-04-01"
  --output tsv

delete_firewall_rule.pwsh

$resourceGroupName="$(resourceGroupName)"
$serverName="$(serverName)"
$ipaddr=curl ipinfo.io/ip
echo "current IP is ${ipaddr}"

$firewallIds=`
az sql server firewall-rule list `
  --resource-group "${resourceGroupName}" `
  --server "${serverName}" `
  --query "[?contains(name, 'Azure_DevOps_Created_at__')].id" `
  --output tsv
echo "${firewallIds}"
az sql server firewall-rule delete --ids $firewallIds

pipeline.yml

# Azure DevOps
variables:
  subscriptionId: '12345678-abcd-efgh-ijkl-123456789010'
  resourceGroupName: 'test'
  serverName: 'mssql-test'
steps:
- task: AzureCLI@2
  displayName: 'Azure CLI'
  inputs:
    azureSubscription: 'Connection String'
    scriptType: bash
    scriptLocation: inlineScript
    inlineScript: |
      {script_above}