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

, 2019-01-29 (first published: )

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.





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.


1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...


1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.


360 reads