Azure SQL Server Firewall Rule add multiple IP address using PowerShell for database connectivity

Question

How do i add multiple IP addresses to Azure SQL Server using PowerShell so that multiple adminstrator can connect to database from their machines using SSMS. It can be done via portal but we have closed that route and everything is done via PowerShell

I have this code but i think it does not fit our requirement.

 $ServerFirewallRule = New-AzSqlServerFirewallRule -ResourceGroupName $ResourceGroupName `
    -ServerName $ServerName `
    -FirewallRuleName "AllowedIPs" -StartIpAddress $StartIp -EndIpAddress $EndIp

for example

admin 1 ipaddress-1: 158.****

admin 2 ipaddress-2: 196.****

Answer 1

Please try the following PowerShell that add the current client IP Address to the Azure SQL Firewall white list. Your administrators can run the PowerShell manually or they can schedule a Windows schedule task that can run the PowerShell when a computer starts.

$subscriptionName = 'Your Subscription'
$ipGetCommand = 'http://www.iplocation.net/find-ip-address' 
$firewallRule = 'My-Home'
$serverName = "Your Server Name";
$webclient = New-Object System.Net.WebClient
$queryResult = $webclient.DownloadString($ipGetCommand)
$queryResult -match '\b\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}\b'
$currentPublicIp = $($matches[0])

Select-AzureSubscription -SubscriptionName $subscriptionName



If ((Get-AzureSqlDatabaseServerFirewallRule -ServerName $serverName -RuleName $firewallRule) -eq $null) {
    New-AzureSqlDatabaseServerFirewallRule -ServerName $serverName -RuleName $firewallRule -StartIpAddress $currentPublicIp -EndIpAddress $currentPublicIp
}
else {
    Set-AzureSqlDatabaseServerFirewallRule -ServerName $serverName -RuleName $firewallRule -StartIpAddress $currentPublicIp -EndIpAddress $currentPublicIp
}