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.

Connect-AzAccount

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

Get-AllAzureSQLFirewallRules

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

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.

Cheers!

One comment on: Azure Powershell Function for Auditing AzureSQL Firewall Policies

  1. Pingback: Audit Your Blobs Using Azure Powershell « KiloRoot

Join the discussion

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