Tuesday, December 5, 2017

Azure SQL Database Firewall Rules

Today I had a use case where I wanted to grant external access to one of my Azure SQL databases that was hosted in an elastic pool along with other client databases. 

If you are unaware, in Azure SQL, there are two types of firewalls.  The first is at the server level, and applies to all databases hosted on a particular server.  The second is a "database" firewall, where rules are configured within a given database an only applies to that database.  The second method is particularly useful when using Azure SQL and opting for a "database-contained" approach. 

You can read more about the different database firewall options and the order of operations here.

In any event, database contained firewall rules fits my need as I want to grant access to only a single database on a particular server.

To view the firewall rules currently in place:
select * from sys.database_firewall_rules

To add a new rule:
execute sp_set_firewall_rule @name = 'fwrule' @start_ip_address = '<ip>', @end_ip_address  = '<ip>'

To remove a rule:
execute sp_delete_database_firewall_rule @name = 'fwrule'

One key thing to note is that database firewall rules do not appear on the Azure portal, and are probably not reviewed in either Azure Security Center, Threat Detection, or otherwise.  As far as I can tell, these events are also not part of SQL diagnostics.  The only service that might capture them is the audit, but those would need to be reviewed manually.

Remember to actively review your databases for these types of changes and limit who can make them.