When I first started working with cloud technologies, one of the first questions that came up was around security (typically as it related to the data on a system). There seemed to be some resistance, and rightfully so, to just taking your company’s data and pushing it onto a SQL Server that happened to have a direct connection to the internet. So, given that, by default, Azure SQL Database is a Platform as a Service (PaaS) that is just like attaching your SQL Server to the internet, how is security handled?
In this blog series, I will cover the options for securing data on your Azure SQL Database. First up is the method used since the start of the Azure SQL Database service (back when it was still called SQL Azure)—the Azure SQL Database firewall.
The Azure SQL Database Firewall
Have you seen this error message before?
Cannot open server ‘someAzureSQLServer’ requested by the login. Client with IP Address ‘74.120.12.123’ is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rules for this IP address or address range. It may take up to five minutes for this change to take effect.
If you have, then you’ve encountered the Azure SQL Database firewall. This message must be a contender for the most helpful error message ever created by Microsoft. Not only does it tell you why you can’t connect, but it also tells you how to solve it! Some of the terms, such as the Windows Azure Management Portal, might be out of date, but let’s face it, this error message is so much better than “method ‘ ~’ of object ‘ ~’ failed.”
As the error message mentions, you can configure your allowed Client IP addresses using the Azure Portal if you are doing one-off, manual updates. You can also automate the process using the REST API, Azure CLI, PowerShell, or T-SQL. Microsoft documents the process pretty well, so I won’t cover it here.
How It Works
The Azure SQL Database firewall restricts requests to your Azure SQL Database to only specific IP addresses that you allow. This security option is available for Azure SQL Database and Azure Synapse Analytics (formerly Azure SQL Data Warehouse). It is not available for Azure SQL Database Managed Instance, which I will cover in a future blog post. You would want to restrict the list of allowed Client IP addresses to only those sources that you trust with your data.
As each request comes in, the IP address of the client that the service sees is compared to the allowed Client IP address ranges from two different lists. If the client IP address falls within one of the defined ranges on either list, the request is allowed to continue.
Two Lists to Check?
I mentioned that the client IP address that comes in is checked against two lists, but why is that? The allowed client IP addresses can be configured at two different levels, the server level and the database level, which is something a lot of people don’t realize. I would venture a guess that the vast majority of IP firewall rules are created at the server level, simply because if you use the Azure Portal, PowerShell, Azure CLI, or REST API–based mechanisms to create a firewall rule, it only creates it at the server level. The only way to create a database-level rule is to use T-SQL while connected to the database.
When a request comes to the Azure SQL Database service, first the database-level firewall rules are checked. If the client is on the allowed list at the database level, then it’s allowed through. If the address isn’t on the database-level list, it is compared to the server-level list. If the address is not on either list, it is rejected.
So, why have two lists? Database-level rules are great for providing fine-grained control over access to databases on a logical server that might have many databases on it. The database-level rules are also good for the mobility of the database. When the firewall rules are set at the database level, the database can be moved to other servers and the rules will still apply. Sure, the clients will have to change which sever they point to, but you won’t have to recreate all the firewall rules for that database on the other server. Also, since the database-level rules are stored within the database, replication will mirror the rules across the replicas automatically.
Server-level rules are great for your administration clients, which need to get to all the databases on a given server, or if you just have a handful of databases and all clients are coming from the same IP ranges. The level you choose is dependent on your scenario but the more fine-grained control, the better.
It is important to note that Azure Synapse Analytics does not have database-level firewall rules, only server-level rules. Also, the server-level rules are cached at the database level in memory for performance reasons, which means that if you remove a server-level rule, it might not immediately block requests on new connections. You might need to run DBCC FLUSHAUTHCACHE at the database level if you want it to immediately block requests. For active connections, you’d need to use the KILL command to kill the connections first.
Tips for Using Azure SQL Database Firewall Rules
Now that you understand a little more about Azure SQL Database firewall rules, the following are a few tips for using them:
- Create a standard for your firewall rule names. Some of the tooling that will automatically create Azure SQL Database firewall rules for you, such as Azure Data Studio, SQL Server Management Studio (SSMS), and the Azure Portal, will default to a horrible name for the rule (e.g., “ClientIPAddress_2020-4-14_14-57-10”). This naming convention isn’t helpful other than to tell you when the rule was created. Azure Portal and SSMS allow you to specify a rule name (although it still defaults to the horrible naming convention), as does the command-line tooling. Come up with a strategy for naming the rules so that it’s more obvious as to what each rule is for. Names such as “Charlotte HeadQuarters Primary” and “MWood-HomeOffice” are at least somewhat helpful. “ClientIPAddress_2020-4-14,” not as much. Nor is naming a rule by the IP address. (Yes, I’ve seen this before and it’s as helpful as it sounds.)
- Review your Azure SQL Database firewall rules often. You can leverage Azure SQL Database Auditing to capture changes to firewall rules at both the server and database level, or you can use PowerShell to return a list of the rules you have in place. If you decide not to use Azure SQL Database Firewall rules, and instead choose one of the methods I’ll cover in future blog posts (e.g., Private Link), you will definitely still want to audit your Azure SQL Database firewall to ensure no one is creating a hole around your desired configuration.
- If you see a firewall rule you don’t recognize or that doesn’t fit your naming rules, remove it. Work with your team to explain why it is important for these rules to be identifiable and validated regularly.
- Don’t be afraid to use database-level rules, although they can only be created via T-SQL.
What’s Next?
In upcoming blog posts, I will cover additional ways you can secure access to your data on Azure SQL Database, including Private Link and service endpoints. The second blog in this series will cover how, even with the firewall capability, strong authentication is still very important.
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.
