SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

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.

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...