What is the name of the firewall rule created by 'Allow Azure services and resources to access this server'

Question

I have 'Allow Azure services and resources to access this server' set to ON for my hosted Azure database server.

Every night at 11:00pm we run a script to remove all firewall rules which were put in place by developers who access the database during the working day. (Basically, this is our implementation of JIT firewall access for database access.) I am noticing that the option to 'Allow Azure services and resources to access this server' consistently toggles to OFF after this script runs.

Here's the relevant line of code in our Runbook:

Get-AzSqlServerFirewallRule -ServerName r******ql -ResourceGroupName r*****rg | Where {$_.FirewallRuleName -notlike "Locked_*"} | Remove-AzSqlServerFirewallRule

I imagine, although it's not explicitly displayed, that the option to allow Azure services creates a firewall rule which is being deleted by my script. I need to know the pattern for what the name of this firewall rule looks like, so that I can exclude it from the removal statement.

Answer 1

You could turn the option of Allow Azure services and resources to access this server to Yes then run Get-AzSqlServerFirewallRule -ServerName r******ql -ResourceGroupName r*****rg , you will see that FirewallRuleName is AllowAllWindowsAzureIps

PS C:\Users\myuser> Get-AzSqlServerFirewallRule -ServerName xxx-Server -ResourceGroupName xxx-Resources


ResourceGroupName : xxx-Resources
ServerName        : xxx-Server
StartIpAddress    : 0.0.0.0
EndIpAddress      : 0.0.0.0
FirewallRuleName  : AllowAllWindowsAzureIps

What you want will be like this,

Get-AzSqlServerFirewallRule -ServerName r******ql -ResourceGroupName r*****rg | Where {$_.FirewallRuleName -notlike "Locked_*" -and $_.FirewallRuleName -notlike "AllowAllWindowsAzureIps" } | Remove-AzSqlServerFirewallRule