public/Clear-AllSqlFirewallRules.ps1
<#
.Synopsis Removes all firewall rules currently added to the SQL server given. .Description Removes all firewall rules currently added to the SQL server given. .Parameter SubscriptionId The unique ID of the Azure subscription where the SQL Azure Server is located. .Parameter AzureSqlServerName The name of the SQL server .Parameter TenantId The unique ID of the tenant where the subscription lives in for faster context switch. .Example Clear-AzdAllSqlFirewallRules -SubscriptionId [Id] -SqlServerName mySQLServerName Removes all existing firewall rules from server `mySQLServerName` #> Function Clear-AllSqlFirewallRules { [CmdLetBinding()] Param ( [Parameter(Mandatory = $true)] [string] $AzureSqlServerName, [Parameter(Mandatory = $true)] [string] $SubscriptionId, [Parameter(Mandatory = $false)] [string] $TenantId, [switch] $NoLogo ) begin { if (!$NoLogo.IsPresent) { Write-Logo $MyInvocation.InvocationName } New-FunctionStartup # ensure that we are at the correct subscription Set-SubscriptionContext -TenantId $TenantId -SubscriptionId $SubscriptionId -NoLogo if (!$?) { Write-HostError "Could not set context." return } } process { $server = Get-AzSqlServer | Where-Object -Property ServerName -EQ $AzureSqlServerName if (!$server) { Write-HostError "Could not find SQL Azure Server $AzureSqlServerName in subscription $SubscriptionId" return } $existintRules = Get-AzSqlServerFirewallRule -ServerName $server.ServerName -ResourceGroupName $server.ResourceGroupName $amount = $existintRules.Length if ($amount -eq 0) { Write-HostError "Terminating because no firewall rules where found on Azure SQL $AzureSqlServerName" return } Write-HostInfo "Found $amount firewall rules on server $AzureSqlServerName" Write-HostDebug "Removing no-delete-rules from resource group" $locks = Remove-NoDeleteLocksForResourceGroup -ResourceGroupName $server.ResourceGroupName foreach ($rule in $existintRules) { $ruleName = $rule.FirewallRuleName if ($ruleName -ne "AllowAllWindowsAzureIps") { Remove-AzSqlServerFirewallRule -ServerName $server.ServerName -ResourceGroupName $server.ResourceGroupName -FirewallRuleName $ruleName | Out-Null if (!$?) { Write-HostError "Failed to remove firewall rules: $_" } Write-Host "Removed rule $ruleName" -ForegroundColor Cyan } else { Write-HostDebug "Ignoring default rule $ruleName" } } } end { Write-HostSuccess "Removed all firewall rules from server $AzureSqlServerName" if ($locks) { Write-HostDebug "Re-adding no-delete-rules for resource group" -NoNewline New-NoDeleteLocksForResourceGroup -ResourceGroupName $server.ResourceGroupName -Locks $locks Write-HostSuccess "Done" } else { Write-HostDebug "Skipping re-adding of locks because no locks where found prior to the operation." } } } |