In previous blog posts in the “Securing Azure SQL Database” series, I covered the first option for securing your Azure SQL Databases—Azure SQL Database firewall rules. Although firewall rules are easy to configure, they have a tendency to quickly grow in number if you have a lot of people who need to connect to your servers remotely. This is especially true with a remote workforce during a time when everyone is working from home.
In this blog post, I will cover a couple ways to view all the firewall rules in an Azure SQL Database subscription.
Viewing Firewall Rules in Azure Portal
When you use Azure Portal to view the Azure SQL Database firewall rules, you have to pull up each Azure SQL Server individually and then click on the firewall rules. You might find that you have a consistent set of rules that end up on every server, such as rules for access from the corporate offices. These rules are easy to check for and maintain.
The rules you have to watch out for are the one-off rules. These can be transient rules that need to be up for only a specific amount of time (for example, to allow access from a conference where you are showing off a product). Other one-off rules might be more permanent, such as for remote employees’ home offices or, scarily, the local coffee shop. (Seriously, don’t do that!) The one-off rules are the ones that are most likely to get out of hand, and reviewing them often is a good idea.
Retrieving Azure SQL Database Firewall Rules with PowerShell
To review the Azure SQL Database firewall rules, it would be nice to pull a list of them rather than having to traverse the Azure Portal and check on each server. To pull that list, we are going to use PowerShell.
There is an Azure PowerShell cmdlet, Get-AzSqlServerFirewallRule, that we can leverage. This cmdlet really does all the heavy lifting for you. We are going to include it as well as some other cmdlets to pull all the firewall rules for a given subscription.
You’ll need to have the Azure PowerShell cmdlets installed and have already authenticated using the Connect-AzAccount cmdlet. You can also do this directly from a Cloud Shell instance in your browser, as the Azure PowerShell cmdlets are already installed for you and it automatically authenticates you.
Get-AzSqlServer `
| %{ Get-AzSqlServerFirewallRule -ServerName $_.ServerName -ResourceGroupName $_.ResourceGroupName } `
| Select-Object -Property StartIPAddress, EndIPAddress, FirewallRuleName, servernameWhat About Database-Level Firewall Rules?
As my previous blog post discussed, Azure SQL Database firewall rules can be stored at the server level as well as the database level. The PowerShell cmdlets can only pull the server-level firewall rules because those are the only rules exposed from the management plane APIs in Azure.
If you want to review the database-level rules, you’ll need to write some code that connects to each database directly and pull data from the sys.database_firewall_rules DMV. This method is a little more involved because you have to connect to each database, handle authentication to the database, etc. Oh, and don’t forget that since you are connecting to each database, a firewall rule at some level would likely need to exist already for wherever you are running your command from.
Wrapping Up
It’s important to review your Azure SQL Database firewall rules regularly to verify that the access that exists is what you expect it to be. Is the Azure SQL Database firewall the best way to secure your data? That’s not really for me to decide. As we continue with the “Securing Azure SQL Database” blog post series, we’ll look at a few other security options. You’ll then have enough knowledge to confidently choose the Azure SQL Database security option that best fits your scenarios and requirements.
Additional “Securing Azure SQL Database” Blog Posts
Mike (@mikewo) is the Site Reliability Engineering Manager for SentryOne, working on cloud based products, services, and related technologies. Mike has over 20 years of experience in the industry, and for the last decade has been focusing on cloud technologies. He was one of the first Microsoft Azure MVPs, first recognized in 2010, and has been awarded an MVP each year since. Mike also blogs at mvwood.com.
