Controlling the firewall for an Azure SQL DB via T-SQL
2January 16, 2019 by Kenneth Fisher
The other day I took a Microsoft Learn course about securing Azure SQL DB. It was really enjoyable and I learned quite a bit, but one of the things that really stood out was the fact that you can control the firewall via T-SQL. Now the reason this stands out to me is because usually when I think of Azure SQL DB vs on-premises MS SQL I think of what functionality is missing, not additional functionality.
Probably the most important thing to remember about this is the fact that Azure uses a whitelist approach to firewalls by default. Nothing is allowed in unless specifically granted access. That means that you are either going to have to start by opening the firewall up completely (what the lab had me do) or open a specific hole to your PC (probably the safer thing to do). Then you’ll be able to connect to the SQL database and once there you have access to the following:
| Catalog View or Stored Procedure | Level | Description |
| sys.firewall_rules | Server | Displays the current server-level firewall rules |
| sp_set_firewall_rule | Server | Creates or updates server-level firewall rules |
| sp_delete_firewall_rule | Server | Removes server-level firewall rules |
| sys.database_firewall_rules | Database | Displays the current database-level firewall rules |
| sp_set_database_firewall_rule | Database | Creates or updates the database-level firewall rules |
| sp_delete_database_firewall_rule | Databases | Removes database-level firewall rules |
The above table was taken from here.
You’ll notice you can add, remove and view firewall rules at the server and database level. You may be wondering though if this is really all that useful. Particularly since you are going to have to open a firewall rule in order to connect so you can work with the firewall rules.
Here’s my take. If I’m dealing with a single database (or maybe 2 or 3) I’ll use the GUI. It’s easy and quick. That said, past that 2-3 it takes too long to use the GUI for each individual database. You’re better off writing code to handle it. Even so, that code isn’t going to be T-SQL. Depending on your environment you’re going to use PoSH or CLI. I mean that’s what they are designed for right? Not to mention you aren’t directly connecting to the database so you don’t have to open a firewall rule to even get started. (Here’s the link for the PoSH and CLI commands.)
So when would the T-SQL code come in useful? Well, again depending on your environment, it could be really handy for data collection. T-SQL is what I’m comfortable with so it’s where I go when I’m collecting data about my environment. On the other hand, you may also be using one of the scripting languages. If so go with those. Basically, continue on with what you’ve already got. Lots of options here. So where do I think T-SQL is going to shine? Troubleshooting. There’s a connection issue, I’m going to connect to look at the log, permissions, whatever. Now, while I’m there I can also check the firewall rules.
Exposing the firewall rules to T-SQL is by no means necessary, but I can see it being handy. And .. well .. options.
SQL Studies 
I had the same issues when I spun up my first SQL VM in Azure. In order to connect to it remotely, I had to open up the firewall completely. Definitely could have benefited from the course you took.
Yea, it was pretty good course. I’m slowly but surely getting a handle on firewalls 🙂