Minette Steynberg

Configuring the Azure SQL Database Firewall

February 28, 2017 by

Introduction

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.


Figure 1: create initial firewall rule

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.


Figure 2: Firewall 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.


Figure 3: Set server firewall from SQL Database portal

Once you are on the firewall settings screen, the rule name from and to IP addresses of the allowable range must be configured.

Figure 4: Parameters to configure

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:

and can be deleted using:


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:

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:

Viewing existing rules

To view existing database and server level rules, you can use the system view: sys.firewall_rules.

I.e.


Figure 5: Sys.firewall_rules result

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:


Figure 6: Sys.database_firewall_rules result

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.


Figure 7: Allow access to Azure services

How the rules are applied

Any connection attempt from either Azure or the Internet will be met by the firewall.

  1. 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.
  2. 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.
  3. If the IP address is not in the allowable range the connection will fail.

Authentication

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:

  • SQL Authentication
    Which is a username and password created on the SQL Server database.
  • Azure Active Directory Authentication
    This is integrated security which is domain based.

See more

Consider these Free tools and community editions for Azure SQL Database

References


Minette Steynberg

Minette Steynberg

Minette Steynberg has over 15 years’ experience in working with data in different IT roles including SQL developer and SQL Server DBA to name but a few. Minette enjoys being an active member of the SQL Server community by writing articles and the occasional talk at SQL user groups.

Minette currently works as a Data Platform Solution Architect at Microsoft South Africa.

View all posts by Minette Steynberg
Minette Steynberg
SQL Azure

About Minette Steynberg

Minette Steynberg has over 15 years’ experience in working with data in different IT roles including SQL developer and SQL Server DBA to name but a few. Minette enjoys being an active member of the SQL Server community by writing articles and the occasional talk at SQL user groups.

Minette currently works as a Data Platform Solution Architect at Microsoft South Africa.

View all posts by Minette Steynberg

1,682 Views