Blog Post

Azure Database Support Blog
4 MIN READ

Control Data Exfiltration using Outbound Firewall Rules

abhijit_shah's avatar
abhijit_shah
Icon for Microsoft rankMicrosoft
May 23, 2022

Pre-requisites:

  1. Service Principle with necessary permissions to make API requests. Access token will be used for authentication for all API requests.
  2. Dependent Azure CLI and Powershell modules are installed

 

Steps (using Azure SQL API):

  1. Check the current OFR configuration of the SQL Server using a GET request on

    https://management.azure.com/subscriptions/%7b%7bsubId%7d%7d/resourceGroups/%7b%7bsqlRg%7d%7d/providers/Microsoft.Sql/servers/%7b%7bsqlServer%7d%7d?api-version=2021-02-01-preview
    

     

    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:

    - 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

    https://management.azure.com/subscriptions/{{subId}}/resourceGroups/{{sqlRg}}/providers/Microsoft.Sql/servers/{{sqlServer}}?api-version=2021-02-01-preview​

    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

    Expected result: API request is successful and returns a 202 Accepted code.


     

  5.  Verify that the restrictOutboundNetworkAccess property is now set to Enabled by issuing a GET request on

    https://management.azure.com/subscriptions/{{subId}}/resourceGroups/{{sqlRg}}/providers/Microsoft.Sql/servers/{{sqlServer}}/outboundfirewallrules?api-version=2021-02-01-preview​

    where
    {{subId}} = Subscription ID
    {{sqlRg}} = Resource Group hosting the SQL server
    {{sqlServer}} = name of the SQL server



  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


    Expected result: Command executes successfully and shows that no outbound firewall rules exist.

  7. Export database again to both of the storage accounts (auditallowstorage and auditdenystorage). This should fail. 

    Expected result: DB export fails for both storage accounts with error message: The operation was not allowed because of the outbound firewall rule configuration for "<storage account FQDN>"

     

  8. Create OFR only for storage account auditallowstorage using this PUT request:

     

    https://management.azure.com/subscriptions/{{subId}}/resourceGroups/{{sqlRg}}/providers/Microsoft.Sql/servers/{{sqlServer}}/outboundfirewallrules/{{saName}}.blob.core.windows.net?api-version=2021-02-01-preview


    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


    Expected result: The API request executes successfully and returns a 202 Accepted code.

     

  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

    Expected result: The command gets executed successfully and shows the FQDN of the allowed storage account.

      

  10. Export database to storage account auditallowstorage. This should be successful


    Expected result: DB successfully exported to storage account auditallowstorage.

     

     

  11. Export database to storage auditdenystorage should still fail


    Expected result: DB export to auditdenystorage should still fail with the error message: The operation was not allowed because of the outbound firewall rule configuration for "<storage account FQDN>"

     

    To remove all OFRs

    Execute the following PowerShell command:

    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


    Expected result: Command should execute successfully and list the FQDN for which OFR was removed



    To disable RestrictOutboundNetworkAccess 


    1. Issue the following PUT request using SQL API

     

    https://management.azure.com/subscriptions/{{subId}}/resourceGroups/{{sqlRg}}/providers/Microsoft.Sql/servers/{{sqlServer}}?api-version=2021-02-01-preview


    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


    Expected result: The API request executes successfully and returns a 202 Accepted code.

    2. Verify that the restrictOutboundNetworkAccess property is disabled on the SQL server by issuing the following GET request on

    https://management.azure.com/subscriptions/{{subId}}/resourceGroups/{{sqlRg}}/providers/Microsoft.Sql/servers/{{sqlServer}}?api-version=2021-02-01-preview

     

    where
    {{subId}} = Subscription ID
    {{sqlRg}} = Resource Group hosting the SQL server
    {{sqlServer}} = name of the SQL server


    Expected result: The API request executes successfully, returns a 200 OK code and the restrictOutboundNetworkAccess property is disabled.


     

     

Updated May 20, 2022
Version 1.0
No CommentsBe the first to comment