Azure SQL Server: Get Azure SQL Server Firewall Rules

Post via

By Saad Khamis

August 25, 2022

36401 views

Contents hide

5 Get Azure SQL Server Firewall Rules

6 In conclusion

Introduction

In this blog I will share one way to get Azure SQL Server firewall rules and the option to save the result in a CSV file. I will also show how to use parameters in PowerShell command.

My blogs have relatively simple, and sometimes complex, examples. I’m hoping that you will be able to tailor them to your need or use them in your own scripts.

The goal of this blog is to show one way to accomplish a task. It is not to show how to write a perfect script, solution or process to accomplish a task.

Prerequisites

First thing to remember is to install Azure PowerShell. Otherwise, you can use Cloud Shell if you prefer to stay within Azure Portal.

PowerShell Cmdlets

Split-Path Returns the specified part of a path.
Test-Path Determines whether all elements of a path exist.
Get-AzSqlServer Returns information about SQL Database servers.
Get-AzSqlServerFirewallRule Gets firewall rules for a SQL Database server.

Sign in to Azure

Sign in to Azure. If you have multiple subscriptions or tenants, make sure to sign in to the correct subscription.
You can use Set-AzContext to set the tenant, subscription, and environment for cmdlets to use in the current session.

PowerShell

Connect-AzAccount -Subscription "aa1111a1-1111-1a1a-11a1-1111a1a1a1a1

1

Connect-AzAccount -Subscription "aa1111a1-1111-1a1a-11a1-1111a1a1a1a1

Get Azure SQL Server Firewall Rules

The script is easy to follow.

Note the use of parameters in PowerShell command, $SelectProperties. This makes easy to select command output. This is specially useful and helpful when you have complex long scripts.

# Define what properties you would like to select. This makes the script easy to change without looking for and updating the actual command.
$SelectProperties = @{ Property = 'ServerName','FirewallRuleName','StartIpAddress','EndIpAddress' }

# If you like to export the result to a CSV file.
# If you don't need to export the result, either set the variable to '', $null or don't define it.
$ExportTo = 'C:\Support\SqlServerFirewallRule.csv'

# Need to export result?
If ($ExportTo -and $ExportTo -ne '') {
    # Verify parent folder exits.
    $ParentFolder = Split-Path -Path $ExportTo -Parent
    If (-not (Test-Path $ParentFolder)) {
        # Display an error message and stop.
        Write-Host "&#91;$ParentFolder] does not exist." -ForegroundColor Red
        Break
    }
}

$SQLServers = Get-AzSqlServer # Returns information about SQL Database servers.

$FirewallRules = @()
# Loop for each SQL server
ForEach ($SQLServer in $SQLServers) {
    # Get firewall rules for a SQL Database server.
    $FirewallRules += Get-AzSqlServerFirewallRule -ServerName $SQLServer.ServerName -ResourceGroupName $SQLServer.ResourceGroupName | Where {$_.StartIpAddress -ne '0.0.0.0' -and $_.EndIpAddress -ne '0.0.0.0'} | Select @SelectProperties
}
# Export results if requested.
If ($ExportTo -and $ExportTo -ne '') {
    $FirewallRules | Export-Csv -Path $ExportTo -NoTypeInformation
}
# Display results.
$FirewallRules

# Define what properties you would like to select. This makes the script easy to change without looking for and updating the actual command.

$SelectProperties = @{ Property = 'ServerName','FirewallRuleName','StartIpAddress','EndIpAddress' }

# If you like to export the result to a CSV file.

# If you don't need to export the result, either set the variable to '', $null or don't define it.

$ExportTo = 'C:\Support\SqlServerFirewallRule.csv'

# Need to export result?

If ($ExportTo -and $ExportTo -ne '') {

# Verify parent folder exits.

$ParentFolder = Split-Path -Path $ExportTo -Parent

If (-not (Test-Path $ParentFolder)) {

# Display an error message and stop.

Write-Host "[$ParentFolder] does not exist." -ForegroundColor Red

Break

}

$SQLServers = Get-AzSqlServer # Returns information about SQL Database servers.

$FirewallRules = @()

# Loop for each SQL server

ForEach ($SQLServer in $SQLServers) {

# Get firewall rules for a SQL Database server.

$FirewallRules += Get-AzSqlServerFirewallRule -ServerName $SQLServer.ServerName -ResourceGroupName $SQLServer.ResourceGroupName | Where {$_.StartIpAddress -ne '0.0.0.0' -and $_.EndIpAddress -ne '0.0.0.0'} | Select @SelectProperties

}

# Export results if requested.

If ($ExportTo -and $ExportTo -ne '') {

$FirewallRules | Export-Csv -Path $ExportTo -NoTypeInformation

}

# Display results.

$FirewallRules

In conclusion

We explored how to get Azure SQL Server firewall rules. We also used parameters in PowerShell command.

Did you find this blog easy to follow and helpful to you? I certainly would love to hear your feedback and suggestions. So, let me know in the comments below. Happy PowerShelling.

Disclaimer

Purpose of the code contained in blog is solely for learning and demo purposes. Author will not be held responsible for any failure or damages caused due to any other usage.