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

2

January 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.

2 thoughts on “Controlling the firewall for an Azure SQL DB via T-SQL

  1. 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.

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,528 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: