set-sql-azure-pi-rule.ps1

# --------------------------------------------------------------------------------
# Registers an IP rule to allow access to a SQL Azure DB from the current machine's IP.
# --------------------------------------------------------------------------------
# SETUP
# --------------------------------------------------------------------------------
# This assumes you are running PowerShell 7 or greater.
#
# Install the Azure and Azure SQL modules.
#
#    Install-Module -Name Az -AllowClobber -Scope CurrentUser
#    Install-Module -Name Az.Sql -AllowClobber -Scope CurrentUser
# 
# Connect to your account using the command below.  This will give you
# a URL and a code to use to log in.
#
#    Connect-AzAccount
#
# --------------------------------------------------------------------------------
# VARIABLES
# --------------------------------------------------------------------------------
# $subscription  - the subscription to use.
# $resourceGroup - the resource group that the database is in.
# $ruleName      - the IP rule name for the current machine.
# $server        - the SQL Azure server.
# --------------------------------------------------------------------------------

$ruleName = "$env:USERNAME-on-$env:COMPUTERNAME" # e.g. sean-on-laptop

$client = New-Object System.Net.WebClient
[xml]$response = $client.DownloadString("http://checkip.dyndns.org")
$ip = ($response.html.body -split ':')[1].Trim()

function Set-ServerAccess ($subscription, $resourceGroup, $server) {
    "Setting access rule for server $server in subscription $subscription" | Write-Host | Out-Null

    $context = Get-AzSubscription -SubscriptionName $subscription
    Set-AzContext $context

    Remove-AzSqlServerFirewallRule -ServerName $server -ResourceGroupName $resourceGroup -FirewallRuleName $ruleName -ErrorAction SilentlyContinue
    New-AzSqlServerFirewallRule    -ServerName $server -ResourceGroupName $resourceGroup -FirewallRuleName $ruleName -StartIpAddress $ip -EndIpAddress $ip
}

Set-ServerAccess 'your subscription name' 'your resource group' 'your sql azure server name'