Azure SQL has introduced the ability to set firewall rules at the database level. A database firewall is different than a server firewall which can be configured via the Azure Portal. The database firewall gives you the ability to not only limit connections at the Azure SQL Server level but also at the database level.
Why would you want to do this?
It all comes down to security. It is now possible to host multiple customer databases on a single instance, the database connections can be segregated via the firewall rules for added security. By using the database firewall, you can ensure only the IP addresses you define can access the databases it is assigned to access.
Understanding the different firewall options for accessing the database system is a must for the SQL Server DBA. When an end-user reports a connection issue, they are not going to be aware of the additional layers of security. The SQL Server DBA must be able to work with the network support administrators to ensure the client IP address can make it through all the firewalls to make a connection to the database.
To determine if there is a firewall configured for the database issue the following SQL command:
USE [DBANME_HERE]
GO
SELECT * FROM sys.database_firewall_rules
This query will return the ID, name, starting IP range, ending IP range, when the rule was created, and the last time it was modified. This will give a list of all the different firewall rules which have been configured for a given database.
To see the last modification to the firewall rules or to help troubleshoot if something has changed recently execute the following query:
‘
USE [DBNAME_HERE]
GO
SELECT * FROM sys.database_firewall_rules
ORDER BY modify_date DESC
To add a database firewall rule, execute the following T-SQL command.
EXEC sp_set_database_firewall_rule @Name = 'HumanResources'
,@start_IP_Address = '0.0.0.1',@end_ip_address = '0.0.0.20'
;
It is recommended to set the name of the rule to something meaningful, making modifying the rule easier later. The Name field is limited to 128 characters, however, this should be sufficient to make the name meaningful. An example would be “HumanResources”.
The above code gives a range of 20 IP addresses that are allowed to connect to the database.
Please note that at the time this blog was written, the Azure SQL database can only support up to 128 different firewall rules for a given database. Always consider using IP ranges if at all possible when creating rules if there are concerns with exceeding this limitation.
The sp_set_database_firewall_rule is executed against the database where the connections will be limited to the range of IP for connections. Once this has been executed, only connections defined in the given range will have permission to connect to the specific database.
To update or modify the IP address for a given rule use the same stored procedure used to create the rule.
EXEC sp_set_database_firewall_rule @Name = 'HumanResources'
,@start_IP_Address = '0.0.0.10'
,@end_ip_address = '0.0.0.20'
If there are errors from a given IP address but you know it has been added to the database firewall, connect with a different IP address that can connect and issue the following query to flush the cache to allow new connections.
DBCC FLUSHAUTCACHE
If a database firewall was created in error or if the firewall rule needs to be deleted, issue the following command in the database to delete the rule:
EXEC sp_delete_database_firewall_rule @Name = 'HumanResources'
Summary:
Adding a Database firewall rule in Azure SQL can help ensure security by limiting access to your databases stored in the cloud. Database firewalls do add a layer of complexity to the routing of connections to your database.
This post was originally published here.