Accessing Azure SQL database from anywhere

Question

We have developed an SQL based application for Motorsport and some of our clients are looking at Azure to hold the database. Trouble is they travel around the world to races and as such will need to access the database using what-ever Internet connection they have and cannot pre-define IP addresses in Firewall rules. Is it possible to effectively disable the Azure firewall so that they just need to enter login credentials to the SQL server rather than having to be on specific IP address ranges?

Given the whole idea of the SQL database is access anywhere it is difficult to believe that you have to define access based on IP addresses but I can't find anything which suggests otherwise!

Answer 1

Before giving you mode advice on security, to answer your question, Yes you can allow All inbound to your Azure SQL Database using the following T-SQL

EXECUTE sp_set_database_firewall_rule N'Allow Azure', '0.0.0.0', '255.255.255.255';  

The range above allows all. This basically means this range is permitted to pass through firewall. This is for database-level firewall rule. For logical server-level rule, just setting the rule as follows

If database-level firewall rule is not set, the logical server-level rule is applied first.

First, giving direct access to your database over the Internet is a very bad practice from security perspective. Business/End-users are not often well trained in security awareness and the very high chance their computers are compromised. There are some approaches you should consider doing to improve security:

• Use built-in Azure SQL Database security feature in Azure such as Transparent Data Encryption (TDE) to always encrypt your databases. If possible, use Azure Key Vault to store the encryption master key to add more encryption layer to the "whole" world. Another feature is Dynamic Data Masking but I don't think it is useful since you allow database access level. Of course, masking some fields is worth considering. Plus, enable Threat Detection to monitor if any anomaly queries (e.g. SQL Injection).
• Integrate with Azure Active Directory to monitor access identity. Every access which is authenticated by Azure AD can be monitored and notified. In Azure AD, have a look at Conditional Access policy to see whether it is applicable to your business users. Saying all business users only travel to just a list of countries usually or they use managed computers. Azure AD (Premium) also gives you Sign-In Risk functionality which combines both Analaytics and Machine Learning to identify if a sign-in is potentially risky (from unknown person). If looking at Azure AD as an option, and more stronger then consider Azure AD Universal with Multi-factor authentication options.
• Establish an Azure VNET, then configure Point-to-Site (P2S) VPN to your Azure SQL Database. Fortunately recently Microsoft announces the ability to control inbound to your Azure SQL Database inside a given VNET. After setting P2S VPN, give to your business users certificate. Such a certificate needs to be installed on business users' laptop before they can connect to the VNET. Attackers without having access to their computers have no way to connect to your Azure SQL Database.
• Add an application layer (e.g. ASP.NET) and login page to let your business user access from this web application. This perhaps adds development efforts but this can help to eliminate at least some direct attack to your database connection string such as brute-force. In the application, handle SQL query to reduce direct SQL Injection. This way requires in-depth understanding of development.

If financial budget is limited, I'd highly suggest you to apply Azure AD and VNET first. Below is the cost drafting:

• Azure VPN Gateway: $29.2/month ($0.04/hour * 730). Basic plan is enough. The plan supports up to 128 P2S connections. If your number of business users are greater than 128, just create a new VPN Gateway.
• Azure AD: if you target to Free plan, you can store up to 500,000 users. If you like to use Conditional Access and reporting, you need to pay $6/user/month for Premium P1 plan
• Azure SQL Database Auditing & Threat Detection: $15/logical server/month. If Auditing is enabled, you are charged Blob storage but the cost for Blob should not really a concern.
• Azure App Service: if adding an application layer. The cost is around $60-70/month for small plan (Basic or Standard). Cost also includes development and deployment effort.

What I've said here may add more concerns on the effort, cost to build. Well, I'd leave that decision consideration to you. Just one thing, think about data breaches and your business reputation if an incident happens. The cost would be much more than the implementation.

Answer 2

My guess is that you have several options:

• securing the database with Azure Active Directory users. Each user can login tot the database with specific rights you could also make them readers and disabling the firewall. You could even implement row level security.

• Create a Azure API application that performs the actions on the database. Let the users login with there credentials to the api and pass those credentials to SQL server.

I think that in combination with row level security is one of the most secured options. On my blog: msftplayground I created a set of articles about it.

Answer 3

I strongly advise against it, but if it's development database, you can create an AllowAll rule in the Firewall: How can I allow unknown users to access my SQL (Azure) DB?

Better option, is to use a VPN server so that the users have to log in to the VPN to have access to the database. This way the Db is not accessible to everyone. You can further secure the VPN by adding a sign in certificate so only owners of the certificate can log in to VPN.