Configuring the Azure SQL Database Firewall
Configuring the Azure SQL Database Firewall
Figure 1: create initial firewall rule This rule can also be created using the REST API or Azure Powershell.
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: 123 EXECUTE sp_set_firewall_rule N'my_server_rule','168.0.0.2','168.0.0.2'; and can be deleted using: 123 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: 12345 EXECUTE sp_set_database_firewall_rule N'my_db_rule'; ,'168.0.0.0' ,'168.0.0.0' 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: 123 EXECUTE sp_delete_database_firewall_rule N'my_db_rule';
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: 123 SELECT * FROM sys.database_firewall_rules
Figure 6: Sys.database_firewall_rules result
Figure 7: Allow access to Azure services
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.
Which is a username and password created on the SQL Server database. Azure Active Directory Authentication
This is integrated security which is domain based. Author Recent Posts Minette SteynbergMinette 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 Latest posts by Minette Steynberg (see all) The end is nigh! (For SQL Server 2008 and SQL Server 2008 R2) - April 4, 2018 8 things to know about Azure Cosmos DB (formerly DocumentDB) - September 4, 2017 Introduction to Azure SQL Data Warehouse - August 29, 2017
SQLShack
SQL Server training EspañolConfiguring the Azure SQL Database Firewall
February 28, 2017 by Minette SteynbergIntroduction
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: 123 EXECUTE sp_set_firewall_rule N'my_server_rule','168.0.0.2','168.0.0.2'; and can be deleted using: 123 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: 12345 EXECUTE sp_set_database_firewall_rule N'my_db_rule'; ,'168.0.0.0' ,'168.0.0.0' 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: 123 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. I.e. 123 SELECT * FROM sys.firewall_rulesFigure 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: 123 SELECT * FROM sys.database_firewall_rules
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. 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.
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 AuthenticationWhich is a username and password created on the SQL Server database. Azure Active Directory Authentication
This is integrated security which is domain based. Author Recent Posts Minette SteynbergMinette 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 Latest posts by Minette Steynberg (see all) The end is nigh! (For SQL Server 2008 and SQL Server 2008 R2) - April 4, 2018 8 things to know about Azure Cosmos DB (formerly DocumentDB) - September 4, 2017 Introduction to Azure SQL Data Warehouse - August 29, 2017