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.

Prompting for values in SQL script

This is something that I forget 2 seconds after I’ve used it so now I’ll put it here as a note to self.

I have a series of TSQL scripts that I use once in different circumstances and often it’s a while between each go. In some of the I need to add some environment specific information and instead of having to scroll down through the text the query editor offers a way of prompting for parameters through a tagging of text like this:

select <Parameter name, Data type, Default value>

Before executing the script you press CTRL+SHIFT+M (this is the part I usually forget) and that makes the editor prompt you like this:

Replace the Default value with the appropriate text and click OK. This will update your SQL script to this:

select 1234

Not the most mind-blowing thing in the world, but a great help.