An interesting point was brought up at the Boston Azure Meetup on Thursday: It’s difficult, at the moment, to secure an Azure SQL Database from unauthorized network access, if your needs to access it are in any way complex.
In a typical on-premises SQL Server setup, where the database server needs to be exposed through a public network, traditionally that SQL Server machine will be put behind a firewall; probably a hardware device, maybe a proxy server, perhaps a VPN … but something to restrict network access to only certain clients.
The same is true of a SQL Server virtual machine in Azure. You can not only configure a VM firewall for that device, but you can also put that SQL Server VM in a network security group or assign it individual access control lists to restrict the IPs that can address the machine.
You can also go hybrid in this setup. For example, you could deploy a VPN to control access to an Azure-based SQL Server VM from an on-premises device, such as a hardware firewall, proxy server or a specific network device which is downstream from a firewall or proxy.
Firewall limitations in Azure SQL Database
Unfortunately, the platform-as-a-service (PaaS) Azure SQL Database offering does not offer this level of flexibility, at least not at the moment.
As Bill Wilder pointed out during the Boston Azure meetup, you can assign firewall rules in Azure at the server and database level.
So if my firewall needs are direct and simple — that is, there’s a handful of IP addresses that need to be able to reach my Azure SQL Database, or better yet, a narrow range of IP addresses — then Azure SQL Database has the exact same network security I would find in an on-premises or virtual machine setup of SQL Server.
What’s problematic in Azure SQL Database is knowing what range of Azure-based IP addresses may need to address a database or server.
While I can assign a fixed IP address to an Azure VM or cloud service, depending on how I have my autoscaling configured I may wind up with resources, following scale-out, that are not originating requests from that IP address.
I also can’t assign public static IP addresses to other compute resources, such as Batch, Functions or Logic Apps, for example. I can get a fixed external IP address for a Web App, but it means implementing a custom domain and SSL certificate. (While those are probably a good idea, they aren’t necessarily appropriate for all uses, especially temporary sites.)
Other compute resources? Sorry, no public-facing IP addresses available.
The Azure services loophole and its dangers
Azure works around this by offering you the option to permit all Azure-based services to pass through your Azure SQL Database server / database firewall unmolested.
As was pointed out by several astute Boston Azure participants, that’s pretty insecure, since it means any Azure-based service, created by anyone, can bypass the firewall. As in, hundreds of thousands of potentially unblocked attacks.
One would assume that Microsoft would have means to both detect malicious access attempts from within Azure, and a means of putting a quick end to them. But I’m pretty sure I would not trust that if I was the compliance officer for a major medical insurance provider.
As Bill Wilder also pointed out, network access is not authentication; while an Azure user might be able to address your Azure SQL Database, that doesn’t mean she can log in, so using least-privileged users with strong passwords and usernames remains important.
Proxies for now; NSG and VNets for the future?
As another participant noted — sorry, I didn’t see who it was, so I can’t give credit — one could spin up a VM firewall / proxy and route all inbound and outbound requests to the Azure SQL Database through that machine. That certainly solves the problem of network access, especially since I can assign a cloud service a static IP and put redundant VMs behind it. But it’s an expensive and kind of clumsy solution to what ought to be a much more straightforward fix.
The obvious fix to the consensus of those at Boston Azure is to allow Azure SQL Database servers to be placed in a network security group; or better yet, a virtual network. Or at least, restrict that “Allow access to Azure services” button to only those services that are part of the same subscription.
A network security group would allow for the creation of complex firewall rules and generally simplify firewall management. A virtual network would provide the most flexible routing and firewall support and would significantly improve response times to Azure SQL Databases for virtual machines within that virtual network, including any scale-out instances.
Of course, you can effectively achieve that same result if you use SQL Server VMs in a virtual network, instead of PaaS. But that sacrifices the cost savings of PaaS — especially for high-end Standard and Premium Azure SQL Databases — and requires you to manage those servers.
For now, SQL Server VMs are most secure
It would not surprise me if Microsoft is working on this very issue and is testing the suggestions forwarded at Boston Azure.
Again: Being able to address a database does not mean being able to log in, and if you have configured your database with transparent data encryption and least privileges, it also doesn’t mean getting useful access, even if you can log in.
That said, for high-security needs, it’s clear that at the moment, the safest security bet is SQL Server VMs.