Change "Allow access to Azure services" settings of Azure SQL Server using powershell

Question

I want to modify (enable/disable) "Allow access to Azure services" settings of Azure SQL Server using powershell, based on a value from my JSON configuration

I do have referred couple of StackOverflow, all the answers are to create a firewall rule "0.0.0.0" to enable and "255.255.255.255" to disable.

Based on answers, I have the below logic. It works sometimes and does not work sometimes. However, I am looking for a better solution. Do we have a single PowerShell cmdlet that does the job?

## Allow/Disallow Access to Microsoft services
    # Get the firewall rules list
    $full_sql_server_fwrules = Get-AzureRmSqlServerFirewallRule -ResourceGroupName $full_sql_server_list_iterator.ResourceGroupName -ServerName $full_sql_server_list_iterator.ServerName
        if(($services | where {$_.name -eq $full_sql_server_list_iterator.ServerName}).allowazureservices){
            Write-Output "allowazureservices = True. Enabling Access to Azure services"

            # Flag variable to check if the 'Allow access to Azure Services' is ENABLED.
            # cmdlet throws an error - If we try to enable 'Allow access to Azure Services', when it is already enabled.
            $access_to_MS_services_flag = $false
            foreach($full_sql_server_fwrules_iterator in $full_sql_server_fwrules){
                if($full_sql_server_fwrules_iterator.StartIpAddress -ne "0.0.0.0" -and $full_sql_server_fwrules_iterator.EndIpAddress -ne "0.0.0.0"){
                    $access_to_MS_services_flag = $true
                }
            }

            if(!$access_to_MS_services_flag) {
                Write-Output "Enabling.. 'Allow access to Azure Services'..."
                New-AzureRmSqlServerFirewallRule -ServerName $full_sql_server_list_iterator.ServerName -ResourceGroupName $full_sql_server_list_iterator.ResourceGroupName -AllowAllAzureIPs
            }     

        }elseif(!($services | where {$_.name -eq $full_sql_server_list_iterator.ServerName}).allowazureservices){
            Write-Output "allowazureservices = False. Disabling Access to Azure services"
            # IF 'Allow access to Azure Services' is ENABLED. SQL Server will have a firewall with StartIP and EndIP as 0.0.0.0
            foreach($full_sql_server_fwrules_iterator in $full_sql_server_fwrules){
                if($full_sql_server_fwrules_iterator.StartIpAddress -ne "0.0.0.0" -and $full_sql_server_fwrules_iterator.EndIpAddress -ne "0.0.0.0"){
                    Remove-AzureRmSqlServerFirewallRule -FirewallRuleName $full_sql_server_fwrules_iterator.FirewallRuleName -ServerName $full_sql_server_fwrules_iterator.ServerName -ResourceGroupName $full_sql_server_fwrules_iterator.ResourceGroupName
                }
            }
        }

score 0 · Answer 1 · edited Feb 14 '20 at 00:31

You could try the below option for creating/modifying the settings through Powershell to change “Allow access to Azure services” settings :

1) Use New-AzureSqlDatabaseServerFirewallRule to set this.

New-AzureSqlDatabaseServerFirewallRule -ServerName "SERVER NAME" -AllowAllAzureServices

2) And if you want to modify the settings, Try below:

PS C:\>Set-AzSqlServerFirewallRule -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -FirewallRuleName "Rule01" -StartIpAddress "192.168.0.197" -EndIpAddress "192.168.0.199"

You can also take a look at below article for further info :

Azure SQL Database Firewall Configuration Settings

If you haven't looked at this before, Here is another article that might help for what you are looking for:

Set Azure Sql Server Firewall Rules

I am looking for something direct like we have with ADLS: Set-AzureRmDataLakeStoreAccount -Name $full_datalake_store_list_iterator.Name -AllowAzureIpState Disabled — Manjunath Rao, Apr 26 '19 at 05:48

Change "Allow access to Azure services" settings of Azure SQL Server using powershell

1 Answers1