Export All Azure SQL Whitelisted IPs with One PowerShell Script

Managing Azure SQL Server security at scale can be challenging, especially when you need to audit or document all whitelisted IP addresses across multiple servers in your subscription. A recent Microsoft Community Hub post by Anuradha_A introduces a practical PowerShell script that solves this exact problem by exporting all server-level firewall rules into a single, easily consumable CSV file.

The Challenge of Multi-Server IP Management

Azure SQL Servers use firewall rules to control which IP addresses can access the database. These rules can be configured at both the server level (applying to all databases on that server) and the database level. When organizations have numerous Azure SQL Servers across their subscription, tracking all whitelisted IPs becomes a tedious manual process that's prone to errors and security gaps.

How the Script Works

The PowerShell script takes a systematic approach to gather all firewall rules:

1. Subscription Context Setup: It first sets the Azure context to the target subscription using Set-AzContext -SubscriptionId "sub_ID"

2. Server Discovery: The script retrieves all Azure SQL logical servers in the subscription using Get-AzSqlServer

3. Firewall Rule Extraction: For each server, it fetches server-level firewall rules using Get-AzSqlServerFirewallRule

4. Data Collection: Each firewall rule is captured with key details including:

   • Subscription ID
   • Resource Group name
   • SQL Server name
   • Firewall rule name
   • Start IP address
   • End IP address

5. Error Handling: The script includes robust error handling that logs warnings for any servers that fail to process (due to permissions or other issues) while continuing to process remaining servers

6. CSV Export: All collected data is exported to AzureSqlServer_PublicFirewallIPs.csv in the Cloud Shell directory

Key Features and Benefits

• Centralized Auditing: Get a complete view of all whitelisted IPs across your entire subscription in one file
• Error Resilience: The script continues processing even if individual servers encounter issues
• Detailed Logging: Each entry includes full context about which server and resource group the rule belongs to
• Easy Download: The CSV file can be easily downloaded from Azure Cloud Shell for offline analysis
• Security Compliance: Helps organizations maintain compliance by providing a complete inventory of allowed IP ranges

Practical Use Cases

This script is particularly valuable for:

• Security Audits: Quickly generate reports for security compliance reviews
• Migration Planning: Document current access patterns before moving to new environments
• Access Reviews: Regularly audit who has access to your Azure SQL resources
• Incident Response: Quickly identify which IP ranges should be investigated during security incidents
• Documentation: Maintain up-to-date documentation of network access controls

Implementation Steps

1. Open Azure Cloud Shell (PowerShell mode)
2. Connect to your Azure account if not already connected
3. Copy and run the script, replacing "sub_ID" with your actual subscription ID
4. Once execution completes, click the Download button in Cloud Shell
5. Specify the path ./AzureSqlServer_PublicFirewallIPs.csv to download the file

Security Considerations

While the script itself doesn't modify any firewall rules, it does provide visibility into your network security posture. Organizations should:

• Regularly review the exported IP lists for unnecessary open ranges
• Consider implementing more restrictive access patterns where appropriate
• Use this data to inform decisions about network security groups and private endpoints
• Schedule regular exports to track changes over time

Script Limitations

The script focuses on server-level firewall rules and doesn't capture database-level firewall rules. For comprehensive security auditing, you may need to run additional queries against individual databases. Additionally, the script requires appropriate permissions to read firewall rules across all servers in the subscription.

Conclusion

This PowerShell script represents a practical solution to a common Azure administration challenge. By automating the collection of firewall rule information across multiple servers, it saves significant time while improving security posture through better visibility. The combination of error handling, detailed data collection, and easy export makes it a valuable tool for Azure SQL administrators and security teams alike.

The script demonstrates how simple automation can solve complex multi-resource management challenges in cloud environments, making it easier to maintain security and compliance at scale.