Microsoft Azure Security Holes with SQL Server Databases

Written by: Luke Chung, President

Microsoft SQL Azure Security Introduction

Having Microsoft Azure host SQL Server databases on their servers is very cost-effective and efficient. Within minutes, one can have a SQL Server database hosted in the cloud and available to applications on the cloud or on premise.

As with all cloud resources, and especially databases, security is a huge concern. Fortunately, SQL Azure includes features to restrict what can connect to your database server but you need to know how to use them and realize that the default settings do not protect you best.

Setting Server Firewalls and Virtual Networks

From the Azure portal, choose "SQL servers" from the left column of resources, and select your SQL Server. The overview appears. On the left column under Security, select "Networking":

Allowing Specific IP Addresses and Range of IP Addresses

These settings let you restrict the IP addresses that can communicate with your server. If a request comes from an IP address that's not on the list, the commands are ignored. This is an important feature for cloud solutions so that only permitted sources are allowed to get data from your server and databases.

Cannot Open Server Error

When unauthorized connection tries to open the database, it triggers an error like this:

Cannot open server 'ServerName' requested by the login. Client with IP address '111.222.33.44'. 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 rule for this IP address or address range. It may take up to five minutes for this change to take effect.

Setting the Authorized IP Addresses

On the right pane, you can set the range of IP addresses that you'll allow to contact this server. You can specify individual IP Addresses or a range of IP Addresses and provide a friendly name to remember it:

You could specify a range of all IP addresses, but that would defeat the purpose of the firewall. You should restrict the IP Addresses to just the sources that are authorized to connect to your database. That doesn't eliminate all security problems but is an important step for any cloud database.

In addition to specific IP Addresses, there's a section below this to specify Virtual Networks. If your organization is using a VPN with a fixed IP address, you can just add that to the list of IP Addresses but a VPN in the cloud can be assigned here and if its IP Address changes, the connection remains authorized.

Firewall rules can also be managed through Transact-SQL (T-SQL) at the server's Master database level:

sys.firewall_rules to display the server-level rules
- Run: SELECT * FROM sys.firewall_rules
sp_set_firewall_rule to create a server-level rule
sp_delete_firewall_rule to delete an existing server-level rule

IP Addresses for Each Database

You can also set IP addresses in each database using Transact-SQL:

sys.database_firewall_rules to display the database-level rules
- Run: SELECT * FROM sys.database_firewall_rules
sp_set_database_firewall_rule to create a database-level rule
sp_delete_database_firewall_rule to delete an existing database-level rule

Database Rules Take Precedence Over Server Rules!

A database with its own firewall settings can be moved to another server and allow the same connectivity regardless of the server level rules, but I find this counter-intuitive.

I assumed the server rules would be checked first to restrict the IP Addresses that could contact any of its databases, but the opposite is true. The database firewall settings are checked and if the IP address fails, the server level rules are checked.

This also means that if you inherit a SQL Server database, you need to check its firewall settings to make sure they don't leave a security hole when you host it on a new server.

For more details, visit this Microsoft article: Azure SQL Database and SQL Data Warehouse firewall rules

Adding IP Addresses for Each Resource that Uses the Server and Databases

So what IP addresses do you include?

External Connections

If you need to connect to the database outside of Azure, you'll need to include those IP addresses. For instance, if you are developing a web application on your PC, your local IP Address needs to be included so that you can run your application locally and get to the data. Ideally, your organization has a fixed IP address you can use or a VPN that you connect to when working remotely. Alternatively, you may need to add every IP Address that you use during development such as your home connection.

Similarly, if you or your users need to connect to the data directly via Microsoft Access or Excel, their IP Addresses need to be included or if your organization has a VPN, use that IP address. If your users are travelling around and trying to connect from random airports and hotels, that will be very challenging if you don't have a VPN to provide a secure connection.

Internal Azure Connections

By default, all Azure resources can connect to your server and databases hosted on Azure:

This is helpful if you are connecting to your database from web sites hosted on Azure Virtual Machines, App Service Plans, etc. It eliminates the need to manually specify the IP Address of each resource in your list.

Allowing All Azure Services to Connect to Your Server is a Huge Security Hole!

If you "Allow access to Azure Services" set to On, you create a huge security hole for your server and every database in it.

Not only can all your resources connect to your databases, Any Azure resource from any organization can connect to your database.

This setting is NOT restricted to the Azure resources in your subscription. It'd be nice to restrict permissions to the current subscription or list of subscriptions but that's not possible. It's everything on all of Microsoft Azure or you need to specify each IP address.

Turn Off Permissions to All Azure Services

Set the permissions to OFF to disallow all Azure services to connect to your SQL server:

Explicitly Specify the IP Addresses Allowed

To avoid the ability of rogue Azure resources from breaching your database security, you need to manually specify the IP Address of every resource that may connect to your server and databases. This can be a real pain.

For instance, if you have a web application connecting to the SQL Server database from an App Service Plan, you need to open that plan, choose the particular service, and in the Settings section, select Properties: