Azure SQL firewall access

Some things were easier in the good old days before Dyn365 and Azure and some are better. Often the things that were better before tends to be things we need to relearn.

Allowing firewall access to Azure SQL (for example regarding BI) is easily scripted and here are 3 useful scripts for that purpose:

List current firewall rules:

SELECT * FROM sys.firewall_rules

If you need to grant access to the IPs 111.222.111.222 to 111.222.111.250:

EXEC sp_set_firewall_rule N'The_Name_That_Will_Be_Shown_On_The_List', '111.222.111.222', '111.222.111.250';
Getting rid of the above rule is just as simple:
EXEC sp_delete_firewall_rule N'The_Name_That_Will_Be_Shown_On_The_List'

The three above is all server rules so you need to run them towards the master database.

You can do the same tricks on database level:

Listing

SELECT * FROM sys.database_firewall_rules

Granting

EXEC sp_set_database_firewall_rule N'The_Name_That_Will_Be_Shown_On_The_List', '111.222.111.222', '111.222.111.250';

Deleting

EXEC sp_delete_database_firewall_rule N'The_Name_That_Will_Be_Shown_On_The_List'

 

Remember to be run them on the desired database.

 

Please keep in mind that you are messing with security and I – as usual – don’t take any responsibility in any damage you might make by using the above.

Advertisements

One thought on “Azure SQL firewall access

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s