Azure SQL Data Exfiltration Controls: Outbound Firewall Rules aka OFRs
This article is contributed. See the original author and article here.
Data loss prevention is a major concern with all customers who would like to have granular control over their data and how it gets exported from their databases on Azure.
The steps below guide on how Outbound Firewall Rules can be leveraged to improve the security posture and ensure data gets exported only to approved Azure Storage accounts. All other Azure Storage accounts are treated as unapproved unless explicitly whitelisted.
The steps below use Azure APIs and Powershell cmdlets to implement the lockdown and enable OFRs.
Pre-requisites:
1. Valid Service Principal based Azure Active Directory (AAD) token for authentication of requests.
2. Latest version of Azure Powershell cmdlets
To enable Restrict Outbound Network Access and add/create OFRs using APIs
1. Check the current OFR configuration of the SQL Server using a GET request on
where
{{subId}} = Subscription ID
{{sqlRg}} = Resource Group hosting the SQL server
{{sqlServer}} = name of the SQL server
It should show that the restrictOutboundNetworkAccess is disabled.
2. Create two storage accounts on Azure Storage. Example:
– auditallowstorage
– auditdenystorage
3. Export database to both storage accounts. The export should be successful for both accounts.
4. Issue a PUT request to Enable RestrictOutboundNetworkAccess on the SQL server using SQL API
with JSON body as
{ "properties" :
{"restrictOutboundNetworkAccess": "Enabled"},
"location": "<sql_server_region>"
}where
{{subId}} = Subscription ID
{{sqlRg}} = Resource Group hosting the SQL server
{{sqlServer}} = name of the SQL server
<server_region> = region where the SQL server is hosted
5. Verify that the restrictOutboundNetworkAccess property is now set to Enabled by issuing a GET request on
where
{{subId}} = Subscription ID
{{sqlRg}} = Resource Group hosting the SQL server
{{sqlServer}} = name of the SQL server
It should show that the provisioned state is “ready” for restrictOutboundNetworkAccess
6. Ensure that there is no existing Outbound Firewall Rule in place using this Powershell command:
Get-AzSqlServerOutboundFirewallRule -ServerName <sql_server_name> -ResourceGroupName <resource_group_name>where
<resource_group_name> = Resource Group hosting the SQL server
<sql_server_name> = name of the SQL server
7. Export database again to both of the storage accounts (auditallowstorage and auditdenystorage). This should fail.
8. Create OFR only for storage account auditallowstorage using this PUT request:
where
{{subId}} = Subscription ID
{{sqlRg}} = Resource Group hosting the SQL server
{{sqlServer}} = name of the SQL server
{{saName}} = Storage Account name for which OFR is created. In this case, its auditallowstorage
9. Verify that OFR was successfully created for storage account using this Powershell command:
Get-AzSqlServerOutboundFirewallRule -ServerName <sql_server_name> -ResourceGroupName <resource_group_name>
where
<resource_group_name> = Resource Group hosting the SQL server
<sql_server_name> = name of the SQL server
It should show the list of the allowed FQDN (Fully Qualified Domain Name). In this case, its auditallowstorage.
10. Export database to storage account auditallowstorage. This should be successful.
11. Export database to storage account auditdenystorage should still fail.
To disable Restrict Outbound Network Access and remove OFRs
1. Remove all Outbound Firewall Rules:
Remove-AzSqlServerOutboundFirewallRule -ServerName <sql_server_name> -ResourceGroupName <resource_group_name> -AllowedFQDN <sa_name>.blob.core.windows.net
where
<resource_group_name> = Resource Group hosting the SQL server
<sql_server_name> = name of the SQL server
<sa_name> = Storage Account Name
2. Issue a PUT request to disable RestrictOutboundNetworkAccess on the SQL server using SQL API
with JSON body as
{ "properties" :
{"restrictOutboundNetworkAccess": "Disabled"},
"location": "<sql_server_region>"
}
where
{{subId}} = Subscription ID
{{sqlRg}} = Resource Group hosting the SQL server
{{sqlServer}} = name of the SQL server
<server_region> = region where the SQL server is hosted
3. Verify that the restrictOutboundNetworkAccess property is disabled on the SQL server by issuing the following GET request on
where
{{subId}} = Subscription ID
{{sqlRg}} = Resource Group hosting the SQL server
{{sqlServer}} = name of the SQL server
To enable Restrict Outbound Network Access and add/create OFRs using Powershell
1. Execute the following command to enable restrictOutboundNetworkAccess property on the SQL server:
Set-AzSqlServer -ServerName <server_name> -ResourceGroupName <resource_group> -RestrictOutboundNetworkAccess "Enabled"
where
<server_name> = name of the SQL server
<resource_group> = name of the resource group
2. Check the current list of Outbound Firewall Rules on the SQL server:
Get-AzSqlServerOutboundFirewallRule -ServerName <server_name> -ResourceGroupName <resource_group>
where
<server_name> = name of the SQL server
<resource_group> = name of the resource group
3. Export database again to both of the storage accounts (auditallowstorage and auditdenystorage). This should fail.
4. Add a new Outbound Firewall Rule on the server using the command:
New-AzSqlServerOutboundFirewallRule -ServerName <server_name> -ResourceGroupName <resource_group> -AllowedFQDN <sa_name>.blob.core.windows.net
where
<resource_group> = Resource Group hosting the SQL server
<server_name> = name of the SQL server
<sa_name> = Storage Account Name
5. List the OFRs on the server using the following command:
Get-AzSqlServerOutboundFirewallRule -ServerName <server_name> -ResourceGroupName <resource_group>
6. Export database to storage account auditallowstorage. This should be successful
7. Export database to storage auditdenystorage should still fail.
Hope this was useful folks! Feel free to get in touch :)
Recent Comments