Right after I posted yesterday’s blog post about SQL Azure – Add IP Address to Firewall, I got the follow-up call from my client who frequently engagement me for Comprehensive Database Performance Health Check. The question was is there any way to list all the IP addresses which have access to the Azure Firewall and remove them via T-SQL in SSMS. Yes, there is a way and let us learn it.
List ALL IP Address with Access to Azure Firewall
It is pretty easy to list all the IP Address. You just have to write the following T-SQL and it will list them in the result window. Please make sure this will only work if your SSMS is connected with the SQL Database instance on the Azure for which you are trying to retrieve the firewall rules.
SELECT * FROM sys.firewall_rules
Well, that’s it. It is that simple. When you run the T-SQL above, it will list all the IP Address allowed for the server.
If you want to add any new server-level IP, you can run the following stored procedure:
EXECUTE sp_set_firewall_rule @name = N'AllowedIP', @start_ip_address = '127.0.0.1', @end_ip_address = '127.0.0.1'
Removing the IP address from the allowed list is very simple as well. Just run the following stored procedure:
EXECUTE sp_delete_firewall_rule @name = N'AllowedIP'
Additionally, if you want to update any rule, you can just run the same query about adding the rule by keeping the same name of the rule and it will update the IP address.
For example, if you want to change the range of the AllowedIP rule, you can just the following rule and it will change it.
EXECUTE sp_set_firewall_rule @name = N'AllowedIP', @start_ip_address = '127.0.1.1', @end_ip_address = '127.0.1.255'
You can also use PowerShell to add, remove and modify IP addresses as well.
Please do leave your feedback in the comments section. If you have any further question, you can reach out to me via email or on social media twitter.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
