Create/Update Azure SQL Server firewall rules using public IP

update-sqlserver-firewall-rule.ps1

Param([string] $subscription)

$subscriptionId;
switch ($subscription)
{
    'dev' {$subscriptionId = 'xxx'}
    'stg' {$subscriptionId = 'yyy'}
    'prd' {$subscriptionId = 'zzz'}
}

if (!$subscriptionId) {
    write-error 'Subscription not found'
    return;
} else {
    Write-Host "Subscription used $($subscription) with Id: $($subscriptionId)"
}

$ip = (Invoke-WebRequest -uri "http://ifconfig.me/ip").Content

write-host "Public Ip: $($ip)"

$list = az sql server list --query "[].{name:name, rg:resourceGroup}" --subscription $subscriptionId | convertfrom-json
foreach($server in $list) {
    $exists = az sql server firewall-rule list -g $server.rg -s $server.name --subscription $subscriptionId --query "[?name == 'Mohamad Dbouk']" | convertfrom-json
    if ($exists.length -gt 0) {
        Write-Host "Updated - $($server.name)"
        $result = az sql server firewall-rule update -g $server.rg -s $server.name -n "Mohamad Dbouk" --start-ip-address $ip --end-ip-address $ip --subscription $subscriptionId
    } else {
        Write-Host "Created - $($server.name)"
        $result = az sql server firewall-rule create -g $server.rg -s $server.name -n "Mohamad Dbouk" --start-ip-address $ip --end-ip-address $ip --subscription $subscriptionId
    }
}