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.

Function Get-AllAzureSQLFirewallRules {
    $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) {
        If (!$SubscriptionList.Contains($SubscriptionName)) {
        Else {
            Select-AzSubscription $SubscriptionName

    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):

ServerName  FirewallRuleName    StartIpAddress    EndIpAddress
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 
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 

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:

Get-AllAzureSQLFirewallRules | Export-CSV -Path C:\Rules.csv

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.


1 of 1

This post has no comments. Be the first to leave one!

Join the discussion

Your email address will not be published. Required fields are marked *