This Runbook will Add/Modify the Firewall Rules on an Azure SQL Server.
This can be run interactively by removing the AzureRunAsConnection section.
Additional it can be configured to run using Params (post to follow later)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 |
##################### INFO ##################### # Script Orignal Developed by jinhash - https://github.com/azureautomation/sql-azure-firewall-management # 10/2021 - Updated by Richhimself to use AZ Commands and new formats + Runs using RunAs in Azure Automation ##################### Connect to Azure using RunAsAccount ##################### $connectionName = "AzureRunAsConnection" try { $servicePrincipalConnection=Get-AutomationConnection -Name $connectionName "Logging in to Azure..." Connect-AzAccount ` -ServicePrincipal ` -TenantId $servicePrincipalConnection.TenantId ` -ApplicationId $servicePrincipalConnection.ApplicationId ` -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint } catch { if (!$servicePrincipalConnection) { $ErrorMessage = "Connection $connectionName not found." throw $ErrorMessage } else{ Write-Error -Message $_.Exception throw $_.Exception } } ##################### Script Configuration ##################### # Leave empty to process all subscriptions $subscriptions = @("ENTER SUBSCRIPTION NAME") #Resource Group Name $rgname = "ENTER RESOURCE GROUP NAME" # Name of the server you want to set a rule for such as "qnfm83tqem" # If left blank, rules will be adjusted for all servers found in the subscription(s) $servername = "ENTER SERVER NAME" # This cannot be left blank $ruleName = "ENTER RULE NAME" $startIpAddress = "x.x.x.X" $endIpAddress = "x.x.x.x" # If true, rule is added/updated. # If false, any existing matching rule is removed $addOrUpdate = $true #Error Action $ErrorActionPreference = "Continue" ################################################################ if(!$ruleName) { throw [Exception] "You must specify a string for ruleName" } if (!$subscriptions) { $subscriptions = Get-AzSubscription | Select -ExpandProperty SubscriptionName } foreach ($subscription in $subscriptions) { "Processing Subscription $subscription..." Select-AzSubscription -SubscriptionName $subscription "Getting list of servers..." $servers = Get-AzSqlServer if($serverName){ "Only getting servers for $($servername)" $servers = $servers | where {$_.ServerName -eq $serverName} } $serverCount = $servers | Measure | Select -ExpandProperty Count $i = 0 "Processing $($serverCount) server(s)" foreach ($server in $servers) { $i++ $serverRules = Get-AzSqlServerFirewallRule -ServerName $server.ServerName -ResourceGroupName $rgname $existingRule = $serverRules | where { $_.FirewallRuleName -eq $ruleName } if($addOrUpdate){ if ($existingRule) { if($existingRule.StartIpAddress -eq $startIpAddress) { "Skipping unchanged rule for server $($i) of $($serverCount): $($server.ServerName)" } else { "Updating pre-existing rule for server $($i) of $($serverCount): $($server.ServerName)" Set-AzSqlServerFirewallRule -ServerName $server.ServerName -FirewallRuleName $ruleName -StartIpAddress $startIpAddress -EndIpAddress $endIpAddress -ResourceGroupName $rgname } } else { "Adding rule for server $($i) of $($serverCount): $($server.ServerName)" New-AzSqlServerFirewallRule -ServerName $server.ServerName -FirewallRuleName $ruleName -StartIpAddress $startIpAddress -EndIpAddress $endIpAddress -ResourceGroupName $rgname } } else{ "Removing pre-existing rule for server $($i) of $($serverCount): $($server.ServerName)" Remove-AzSqlServerFirewallRule -ServerName $server.ServerName -FirewallRuleName $ruleName -ResourceGroupName $rgname } } } |