Configure Azure SQL Server firewall

Hi Scripters! Today want to show you how in easy way configure Azure SQL Server firewall for multiple IP addresses.

Last time I was working on IaaS configuration to allow connections from virtual machines to Azure SQL Server and decided to share my code with you. Script which I prepared is configured in following way. In first step it gathers all of the virtual machines in subscription and prepare rule array with name of the virtual machine and public IP address assign to it. Later on it gather all Azure SQL Server in the subscription, iterate across them and adding on each of them rule to allow access from each of the virtual machine in the subscription.

Script was created for my needs, however you can always limit SQL Servers which should be used, or simply create rule array manually according to your needs. Once you configure firewall to allow access from specific IPs you can easily execute queries from them using PowerShell.

Prerequisites:

  • Az module installed
  • Already logged in to Azure (if not run Connect-AzAccount)
  • Contributor access in subscription

Script:

param(
    $SubscriptionId = ''
)

Select-AzSubscription -subscriptionId $SubscriptionId

$RulesArray = @()

$Vms = Get-AzVm
$SQLs = Get-AzSqlServer

foreach($vm in $VMs){
    
    $pipName = "$($vm.Name)-ip"
    $IpAddress = (Get-AzPublicIpAddress -Name $pipName -ResourceGroupName "$($vm.ResourceGroupName)").IpAddress
    $Object = New-Object PSObject -Property ([ordered]@{ 
 
        Name                    = $vm.Name
        IPAddress               = $IpAddress

    }) 
    $RulesArray += $Object
}
foreach($sql in $SQLs){
    foreach($rule in $RulesArray){
        $existingRule = Get-AzSqlServerFirewallRule -ServerName $sql.ServerName -ResourceGroupName $sql.ResourceGroupName -FirewallRuleName $rule.Name -ErrorAction SilentlyContinue
        
        If(!$existingRule){
            Write-Output "Adding rule $($rule.Name) for SQL Server $($sql.ServerName)"
            New-AzSqlServerFirewallRule -ServerName $sql.ServerName -ResourceGroupName $sql.ResourceGroupName -FirewallRuleName $rule.Name -StartIpAddress $rule.IPAddress -EndIpAddress $rule.IPAddress
            Write-Output "Rule $($rule.Name) added for SQL Server $($sql.ServerName)"
            Write-Output "----------"
        }
        else{
            Write-Output "Rule $($rule.Name) is already created. Skipping..."
            Write-Output "----------"
        }
    }
}

I believe that after applying this script you can easily configure Azure SQL Server firewall.

Hope that it will be useful for some of you 😉

Enjoy!

3 thoughts on “Configure Azure SQL Server firewall

  1. hI Artur,
    Great script, and more useful ! Just 2 little remarks :
    > To build your $RulesArray var, it’s better to use $RulesArray = foreach {…} than $RulesArray += $Object.
    > Avoid use !, prefer -not. Ok, it’s the same (Logical Oprator), but with this the code is more readable.
    Regards
    Olivier

    1. Hi Olivier,

      Thanks for your suggestion.
      However don’t understand why it’s better to use foreach instead of adding object to array.
      Please explain.

      1. Hi Artur,
        Sorry for this misunderstanding. With the 2 ways, you use an Array fed by a PSCustomObject. The diffence is the following :
        My proposal :
        $Array = foreach { … $Object}
        You proposal
        Foreach { … $Object; $Array+=$Object}

        The 2 ways do exactly the same thing, but the first one is faster.
        Your proposed method works great, and if just working with a small number of elements (< 1000), this method of adding items to this array doesn't take up too much time. But under the covers, this method of adding items to an array isn't efficient. When the += operator is used, it's actually destroying the array and creating a new one. Since computers are so fast, you'll hardly notice but start working with arrays of larger and larger item counts, the lag is obvious.
        Lot of technical posts about this in the Internet are available.

        Regards
        Olivier

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.