Azure SQL Database is a Platform as a Service (PaaS) from Microsoft that essentially gives you SQL Server databases at your fingertips in the cloud. It is a powerful service offering, but how is it secured?
In my previous blog post, I discussed what the Azure SQL Database firewall was and how it works at a high level. Although the firewall rules offer one layer of security, the Azure SQL Database firewall only blocks or allows requests to your databases—it doesn’t authenticate requests.
In the second installment of the “Securing Azure SQL Database” series, we will dig deeper into the Azure SQL Database firewall to understand why strong authentication is very important.
Authentication Still Matters
A client making a call from an allowed client IP address would still need to have a valid credential in the form of a SQL Server Authentication login, Azure Active Directory (AD) credential, or Azure AD token to be authenticated to complete a request. The firewall is just one of the first layers of defense before a request even gets to the point of checking credentials. This is an especially important point to make for two reasons:
- Network Address Translation (NAT)
- Access from Azure services and resources
1. Network Address Translation (NAT)
The full description of NAT goes beyond the scope of this blog post but to summarize, the IP address a client has on a local network likely isn’t the one that the Azure SQL Database service sees when a request comes in. The client running on a local network somewhere makes the request, which passes through several other computers and routers before making it to the Azure SQL Database service. One of the devices along that path will be a gateway, which will have an IP address of its own that is publicly addressable and that is what the Azure SQL Database service sees as the client IP address.
Think of this gateway as the concierge at a hotel. Many guests ask the concierge questions and make requests. The concierge then forwards those requests on to other services, departments, and guests without actually passing along the names of the original guests unless they have to. Responses to any requests go back to the concierge because that is the publicly known place to report back to. The concierge then knows how to supply those results to the guest because they kept track of who asked for what. In the case of the Azure SQL Database firewall, the service sees only the public address, which is acting as the concierge.
When adding a new firewall rule through the Azure Portal, it will actually tell you the client IP address it sees; however, that is helpful only if you are adding a rule for yourself or those who are behind the same gateway as you. When you are adding rules for people behind other gateways, you’ll need to find out their public IP address.
If they received the Azure SQL Database firewall error mentioned in my previous Securing Azure SQL Database blog post, it actually includes the IP address in the message. There are also many online websites, such as MXtoolbox, that you can use to see the public IP address. Choose an online tool you trust and then ask the person you are adding the firewall rule for to visit that website to get their public IP address.
So why is it important to understand their public IP address is being used? Well, because all clients behind the public IP address of that gateway will be allowed past the firewall once the firewall rule is configured. If that gateway is part of your on-premises IT infrastructure, it’s likely that the clients making requests are from only your company. However, it could also be a client connected to your company’s guest WiFi if your networking security doesn’t completely segment that off.
You might also need to check with your IT team to see if there are multiple IP addresses, or a range, that any request could be sent across from your company. For example, if your company has a secondary internet provider, it likely has multiple outbound IP addresses. You can then set up rules for the range or multiple addresses where it makes sense. If you aren’t sure, definitely verify with your networking gurus.
Let’s take the hotel metaphor a little further. Say you are traveling and connect to the hotel WiFi. When you then add an Azure SQL Database firewall rule for the client IP address the service sees while you are at the hotel, you are literally saying that everyone in that hotel can reach your database! Using a VPN that tunnels traffic while traveling might be something to look into.
2. Access from Azure Services and Resources
When you configure your Azure SQL Database firewall, there is an option to “Allow Azure services and resources to access this server,” and you can select either Yes or No.
The Azure environment is massive. It spans continents and has many, many resources, all of which have their own IP addresses. Sometimes these IP addresses also change as resources are moved or reconfigured. Imagine if you had to keep track of all the IP addresses for your web apps or virtual machines (VMs) that need to connect to your database and create rules for them all. That would be very painful and a lot to manage.
This is why the option to allow Azure services access exists. By selecting Yes, you are saying that any client within the Azure network can get past your firewall, even if those requests come from resources in different subscriptions, from different customers, etc. Anything running in Azure. This is like saying that anyone staying at the same brand of hotel around the globe can all get in. Needless to say, this can be pretty open; however, remember that the firewall isn’t meant to be authentication.
The fact that many different clients might be behind an allowed range is why authentication still matters. Using strong passwords for SQL Server Authentication logins (if you must use SQL Server Authentication) or leveraging the Azure AD authentication integration for Azure SQL Database is a must for any Azure SQL Database that will still be exposed if using only the Azure SQL Database firewall. Note that there are ways other than using the Azure SQL Database firewall to restrict access, and we will cover those methods in upcoming blog posts. The Azure SQL Database firewall is just the first layer of security.
Tips on Azure SQL Database Firewall and Authentication
Now that you understand a little more about why authentication still matters while using Azure SQL Database firewall rules, here are some tips:
- If possible, use Azure AD integration for authentication. Doing so enables you to leverage managed identities in Azure for your applications to access your data without having to manage and store passwords in your code and configuration. It also reduces reliance on SQL Server Authentication accounts.
- The server-level admin account you create with every Azure SQL Database can’t be turned off. Because it is also the account that has the greatest amount of permissions on the server, you must protect it as much as possible. Do not name the account “admin” and give it a weak password. Do not use the admin account as the way your applications connect to your database. Create a complex password for this account and store it securely away for “break glass” type scenarios.
- If you set up Azure AD authentication and give users access to specific databases as contained users, when they connect with tools such as SQL Server Management Studio (SSMS) and Azure Data Studio, they will need to specify the database they want to access in the connection settings. If they do not use the database name, it will attempt to connect to master first and, if they don’t have rights to master it, will give them an error on connection.
What’s Next?
In my next blog post, I will show you how to use PowerShell to view firewall rules.
Additional “Securing Azure SQL Database” Blog Posts
