Controlling the firewall for an Azure SQL DB via T-SQL

Kenneth Fisher — Wed, 16 Jan 2019 14:00:28 +0000

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.

firewall – SQL Studies

Controlling the firewall for an Azure SQL DB via T-SQL