I periodically review Azure PaaS resource edge security. As a part of that process I examine and cleanup AzureSQL firewall rules. Once you have more than a handful of subscriptions and AzureSQL databases, doing this manually starts becoming unfeasible. To that end, Azure Powershell is your friend…
As is typical, you need to install the Powershell AZ module on your workstation, after that you need to run.
Then supply your Azure credentials (preferably creds that have the ability to view and manage all of your AzureSQL databases and firewall rules, otherwise the rest is moot.
After that has been done, you can run the following code below which creates a function that can be run to easily catalog all of your AzureSQL Server Firewall policies across all PaaS servers in all subscriptions you have access to.
$Subscriptions = Get-AzSubscription
Function Use-Subscription ($SubscriptionName) {
$DefaultSubscription = Get-AzContext | Select-Object -ExpandProperty Subscription | Select-Object -ExpandProperty Name
$SubscriptionList = Get-AzSubscription | Select-Object -ExpandProperty Name
If (!$SubscriptionName) {
Return
}
If (!$SubscriptionList.Contains($SubscriptionName)) {
Exit
}
Else {
Select-AzSubscription $SubscriptionName
Return
}
}
ForEach ($subscription in $Subscriptions) {
Use-Subscription $subscription.name | out-null
$Servers = Get-AzSqlServer | Select-Object -Property ServerName, ResourceGroupName
Foreach ($server in $servers) {
$sqlservername = $server.Servername
$RGname = $server.ResourceGroupName
Get-AzSqlServerFirewallRule -Servername $sqlservername -ResourceGroupName $RGname | Select-Object -Property ServerName, FirewallRuleName, StartIPAddress, EndIPAddress
}
}
}
You will note there is a function within the function. This is a piece of code I wrote to walk across subscriptions for an Azure Automation script for SQL scaling. I am just re-using it here to do much the same. Once the above code has been run, you can then simply call the function:
This will take several minutes to run depending on the size of your environment. This is a “read-only” operation and will not make changes on your AzureSQL Server instances. What it does do is -> “walk” ALL of the Azure subscriptions your account has access to (even across tenants providing your account is a guest member with the necessary access) and generates a table output that provides the following (this is a fake example, hence the IP’s make no sense):
ContosoSQL05 Seattle_Office_IP 321.483.48.99 321.483.48.110
ContosoSQL05 Paris_Office_IP 952.83.8.99 952.83.8.99
ContosoSQL05 AllowAllWindowsAzureIps 0.0.0.0 0.0.0.0
AdventSQL100 Seattle_Office_IP 321.483.48.99 321.483.48.110
AdventSQL100 Paris_Office_IP 952.83.8.99 952.83.8.99
AdventSQL100 AllowAllWindowsAzureIps 0.0.0.0 0.0.0.0
ETC....
Something I like to do is output this to a CSV file so I can work with the data in Excel. That looks something like this:
This was just a quick-n-dirty write-up to make life easier and thus far it has done precisely that. Feel free to steal and use for your own benefit.
Cheers!