The Azure SQL Database firewall lets you decide which IP addresses may or may not have access to either your Azure SQL Server or your Azure SQL database.
When creating an Azure SQL Database, the firewall needs to be configured before anyone will be able to access the database. By default, no external access to your SQL Database will be allowed until you explicitly assign permission by creating a firewall rule.
An initial server level rule will need to be created using the portal before you will be able to access your SQL Database server.
How to create the initial server level rule
To create the initial server level firewall rule, you need to go to the Firewall settings in Azure and add an IP range which will be allowed access.
Access from the client you are connecting from can be added by clicking on the Add client IP button. This will automatically add a rule for the IP address for the client you are currently connection from as both the Start and End IP. Allowing you to access the server on which your database resides.
This rule can also be created using the REST API or Azure Powershell.
Types of firewall rules
There are 2 types of firewall rules:
Server level rules
Server level rules allow access to the Azure SQL Server. Which means that the client will have access to all the databases stored on that SQL Server.
Server level rules are stored in the mater database.
Only subscription owners or contributors can create server level firewall rules using the Azure portal , PowerShell or the REST API. Server principal logins or Azure Active Directory Administrators can create rules using Transact-SQL.
Typically, this access will be given to administrators or anyone who may need access to all the databases.
As a best practice, server level access should only be given when absolutely necessary and database level rules must be used wherever possible.
Creating a server level rule
In Azure, you can get to the server firewall configuration screen in the portal in 2 ways:
- Go to your Azure SQL Server and select the Firewall option under settings.
- When have selected your SQL Database in the Azure portal you can click on the Set server Firewall button. This sets the server firewall. The database firewall cannot be configured in the portal.
Once you are on the firewall settings screen, the rule name from and to IP addresses of the allowable range must be configured.
Server level rules can also be configured using Transact-SQL, PowerShell or the REST API. This is beyond the scope of this article.
To create or modify a server level rule using Transact-SQL the following statement can be executed on the master database:
EXECUTE sp_set_firewall_rule N'my_server_rule','184.108.40.206','220.127.116.11';
and can be deleted using:
EXECUTE sp_delete_database_firewall_rule N'my_server_rule';
Database level rules
Unlike server level rules, the database level rules are stored within the relevant database.
Database level rules cannot be created using the Azure portal or PowerShell, it can only be done using Transact-SQL.
Using database level rules adds security by ensuring that clients do not have access to database that they don’t need and it also makes it easier to move databases, since the rules are contained within the database itself.
Creating a database level rule
Database level rules can only be created using Transact-SQL. The following T-SQL command can be used to create or modify an existing rule:
EXECUTE sp_set_database_firewall_rule N'my_db_rule';
The first parameter is the rule name, followed by the first IP address that you wish to give access to. The third parameter is the last IP address in the range you wish to give access to.
Setting the start IP address and the end IP address to the same address will only provide access to that one specific IP address.
CONTROL permissions are required on the database on which you want to create the firewall rule.
Once the command has been issued to change a rule, the change can take up to 5 minutes to take effect.
To delete a database firewall rule use:
EXECUTE sp_delete_database_firewall_rule N'my_db_rule';
Viewing existing rules
To view existing database and server level rules, you can use the system view: sys.firewall_rules.
SELECT * FROM sys.firewall_rules
This must be executed on the master database will display server level rules. Note how the AllowAllWindowsAzureIps have both a start and an end IP address of 0.0.0.0.
To view existing database level rules you can execute the following command on the relevant database:
SELECT * FROM sys.database_firewall_rules
Allowing access from Azure
To allow connection from Azure to your Azure SQL Server, the Allow access to Azure services must be set to on.
This effectively adds a rule with a from and to address of 0.0.0.0.
It is important to remember that this also allows access to anyone else with an Azure subscription. So configuring permissions on your SQL Server itself is pivotal.
How the rules are applied
Any connection attempt from either Azure or the Internet will be met by the firewall.
The database level rules will be applied.
Any client which has an IP address which falls within the allowable range of the specific database level firewall rule, will be allowed to pass through to the database directly.
- The server level firewall rules will be applied. If the abovementioned check failed. If the IP address of the client falls within the allowable range of the server level rule, access will be granted to all the SQL Databases in the server.
- If the IP address is not in the allowable range the connection will fail.
The firewall restricts the clients which are allowed to connect to your SQL Database. But it does not authenticate users. User authentication happens at the database level. Similarly to SQL on premise, two methods of authentication can be used:
Which is a username and password created on the SQL Server database.
Azure Active Directory Authentication
This is integrated security which is domain based.
- Configure a Windows firewall for database engine access
- Security Center for SQL Server Database Engine and Azure SQL Database
- Securing your database
- Configuring the Azure SQL Database Firewall - February 28, 2017
- How to create and configure SQL Server Agent Alerts - October 24, 2016
- 5 handy Transact-SQL tips you may (or may not) know - September 23, 2016
Minette currently works as a Data Platform Solution Architect at Microsoft South Africa.
View all posts by Minette Steynberg