Automating Azure SQL Firewall Rules with PowerShell

Understanding the Challenge

Managing firewall rules across multiple Azure SQL Servers can be a tedious and time-consuming task, especially when you need to update your IP access regularly. This script provides an automated solution to this common challenge.

Full code

$ipAddress = (Invoke-WebRequest -uri "http://ifconfig.me/ip").Content #Read-Host -Prompt 'What is your IP?' 
$subscription =  Read-Host -Prompt 'What is the name of your Azure subscription IP?' 
$firewallRule =  Read-Host -Prompt 'Please enter a name or label for your firewall. If this name exists then the existing rule will be replaced with this.'

$jsonArray = az sql server list  --subscription $subscription --query '[].{name:name, group:resourceGroup}'
$data = $jsonArray | ConvertFrom-Json
foreach ($object in $data) {
	$group = $object.group
	$name = $object.name
	
	az sql server firewall-rule delete -n $firewallRule -s $name -g $group --subscription $subscription 
	az sql server firewall-rule create -n $firewallRule -s $name -g $group --subscription $subscription --start-ip-address $ipAddress  --end-ip-address $ipAddress 
}

The Script Breakdown

Let’s analyze each component of this PowerShell script that helps manage Azure SQL Server firewall rules efficiently.

1. Getting the Current IP Address

$ipAddress = (Invoke-WebRequest -uri "http://ifconfig.me/ip").Content

This line automatically fetches your current public IP address using the ifconfig.me service. There’s also a commented-out alternative using Read-Host if you prefer manual IP entry.

2. Setting Up Parameters

$subscription = Read-Host -Prompt 'What is the name of your Azure subscription IP?'
$firewallRule = Read-Host -Prompt 'Please enter a name or label for your firewall.'

The script prompts for two crucial pieces of information:

• The Azure subscription name
• A name for the firewall rule (which will be used consistently across all servers)

3. Retrieving SQL Server Information

$jsonArray = az sql server list --subscription $subscription --query '[].{name:name, group:resourceGroup}'
$data = $jsonArray | ConvertFrom-Json

This section:

• Uses Azure CLI to list all SQL servers in the subscription
• Filters the output to only include server names and resource groups
• Converts the JSON response to PowerShell objects for easy processing

4. Processing Each Server

foreach ($object in $data) {
    $group = $object.group
    $name = $object.name
    
    az sql server firewall-rule delete -n $firewallRule -s $name -g $group --subscription $subscription 
    az sql server firewall-rule create -n $firewallRule -s $name -g $group --subscription $subscription --start-ip-address $ipAddress --end-ip-address $ipAddress 
}

For each SQL server, the script:

• Removes any existing firewall rule with the specified name
• Creates a new rule with the current IP address
• Uses the same rule name across all servers for consistency

Key Features

1. Automation: Eliminates manual firewall rule updates across multiple servers
2. Consistency: Uses the same rule name across all servers
3. IP Detection: Automatically detects the current public IP address
4. Cleanup: Removes old rules before creating new ones
5. Subscription Scoped: Works across all SQL servers in a subscription

Best Practices and Considerations

Security Considerations

1. Always verify your subscription context before running the script
2. Use meaningful names for firewall rules
3. Regularly review and clean up unnecessary firewall rules
4. Consider implementing additional logging

Performance Optimization

# Add error handling
foreach ($object in $data) {
    try {
        # Existing code
    } catch {
        Write-Error "Failed to update firewall rules for server: $($object.name)"
        Write-Error $_.Exception.Message
    }
}

Recommended Modifications

1. Error Handling: Add try-catch blocks for robustness
2. Logging: Implement detailed logging for auditing
3. Validation: Add parameter validation

Common Use Cases

1. Developer Environments: Quick access updates when working from different locations
2. Administrative Tasks: Bulk updates of firewall rules
3. CI/CD Pipelines: Automated environment access management
4. Disaster Recovery: Quick restoration of access during emergencies

References

1. Azure SQL Server Documentation
2. PowerShell Documentation
3. Azure CLI Reference