Automating Azure Analysis Services (AAS) Firewall Whitelisting Using Azure Data Factory

Introduction

Managing IP firewall rules in Azure Analysis Services (AAS) is typically a manual process that involves logging into the Azure portal, navigating to the resource, and adding IPs manually. This becomes a bottleneck in environments where:

• Teams are working from different locations
• IPs change frequently (like dynamic VPNs, cloud agents)
• Access needs to be provided temporarily or securely to different systems or users

Moreover, to manually add a firewall rule in AAS, a person must have Contributor-level access (or higher) on the resource. It’s not always safe—or practical—to give this level of access to everyone in the team.

Why was this pipeline built?

This pipeline was created to address a real-world challenge:

How can we allow teams to whitelist IPs in Azure Analysis Services on-demand, without giving them elevated portal access or requiring manual intervention?

By assigning Managed Identity permissions to ADF just once, we avoid the need to grant portal-level access to individual users. Now, anyone with basic trigger permissions can execute the pipeline and whitelist the required IP securely and consistently.

This approach strikes a balance between security, flexibility, and automation.

What does this Pipeline do?

Here’s a breakdown of the steps in the pipeline Cube_FirewallUpdate_Automate:

1. Get Cube Configuration (ACT_Cmn_GetCubeConfigDetails)

The pipeline fetches the necessary metadata—such as Subscription ID, Resource Group Name, and Cube Name—from a configuration table in Azure SQL Database. This makes the solution dynamic and environment-agnostic.

QUERY : 
SELECT
    [Subscription],
    [ResourceGroupNm],
    [CubeNm]
FROM
    dbo.Cube_Config
WHERE 
    [Environment] = '@{pipeline().parameters.EnvName}'
    AND [Is_Active] = 1

Created a Dataset & Linked Service to access the sql database.

---CREATE SCRIPT ------
CREATE TABLE dbo.Cube_Config (
    Subscription       VARCHAR(100)  NOT NULL,
    ResourceGroupNm    VARCHAR(100)  NOT NULL,
    CubeNm             VARCHAR(100)  NOT NULL,
    Environment        VARCHAR(50)   NOT NULL,     -- e.g., uat, dev, prod
    Is_Active          BIT           NOT NULL DEFAULT 1,
    Created_On         DATETIME      NOT NULL DEFAULT GETDATE(),
    Updated_On         DATETIME      NOT NULL DEFAULT GETDATE(),
    Updated_By         VARCHAR(100)  NOT NULL
);

---INSERT SCRIPT----

INSERT INTO dbo.Cube_Config (
    Subscription,
    ResourceGroupNm,
    CubeNm,
    Environment,
    Is_Active,
    Created_On,
    Updated_On,
    Updated_By
)
VALUES (
    'aadfa227-8356-48f8-ac09-c87e7fda15a4',
    'MyCSharpCorner_RG',
    'aascube',
    'uat',
    1,
    GETDATE(),
    GETDATE(),
    SYSTEM_USER
);

You can get values for these fields from Analysis Service’s ‘Overview’ section.

2. Get Existing Firewall Rules (ACT_Cmn_GetFirewallRules)

A Web activity uses ADF’s Managed Identity to make a GET request to the Azure Management API. This retrieves the current list of firewall rules configured on the AAS server.

URL: https://management.azure.com/subscriptions/@{activity('ACT_Cmn_GetCubeConfigDetails').output.value[0].Subscription}/resourceGroups/@{activity('ACT_Cmn_GetCubeConfigDetails').output.value[0].ResourceGroupNm}/providers/Microsoft.AnalysisServices/servers/@{activity('ACT_Cmn_GetCubeConfigDetails').output.value[0].CubeNm}?api-version=2017-08-01

Resource: https://management.azure.com/

3. Merge with New IP Rule (ACT_Cmn_MergeIPWithExisting)

The pipeline checks whether any existing firewall rules are present on the AAS server. If rules exist, it appends the new IP to the list. If there are no existing rules (i.e., the firewall is disabled), it creates a new rule list and enables the firewall automatically.

Example

In our case, the AAS firewall was initially disabled. Once we triggered the pipeline, it not only enabled the firewall but also added the specified IP, as shown in the screenshot below.

This approach ensures that the firewall is always correctly enabled, even for first-time runs, without needing any manual setup from the portal.

Value : 

@if(
  contains(activity('ACT_Cmn_GetFirewallRules').output.properties, 'ipV4FirewallSettings'),
  json(concat(
    '[',
      join(
        activity('ACT_Cmn_GetFirewallRules').output.properties.ipV4FirewallSettings.firewallRules,
        ','
      ),
      ',{"firewallRuleName":"',
        pipeline().parameters.IpFirewallName,
        '","rangeStart":"',
        pipeline().parameters.IpStartRange,
        '","rangeEnd":"',
        pipeline().parameters.IpEndRange,
      '"}',
    ']'
  )),
  json(concat(
    '[{"firewallRuleName":"',
    pipeline().parameters.IpFirewallName,
    '","rangeStart":"',
    pipeline().parameters.IpStartRange,
    '","rangeEnd":"',
    pipeline().parameters.IpEndRange,
    '"}]'
  ))
)

4. Update AAS with New Firewall Rules

The merged list is sent back to AAS via a PATCH request.

Note. We use PATCH because:

• POST is for creating new resources. When trying to update the firewall settings in AAS using POST, Azure returns an error:

  "The HTTP method 'POST' is not supported."  

  This is because POST is not allowed on the server-level endpoint in AAS. It's reserved for creating new child resources, not for updating properties.
• PUT replaces the entire resource. Using PUT requires you to provide all properties of the server, including "location", "sku", and backup settings, or the call will fail with errors like "LocationRequired".
• PATCH is technically a partial update method; in the context of AAS firewall rules, it behaves differently. PATCH will overwrite the entire ipV4FirewallSettings section. So, if you send only one firewall rule in your PATCH request, AAS will remove all existing rules and replace them with just that one — it doesn't merge.

URL: 
https://management.azure.com/subscriptions/@{activity('ACT_Cmn_GetCubeConfigDetails').output.value[0].Subscription}/resourceGroups/@{activity('ACT_Cmn_GetCubeConfigDetails').output.value[0].ResourceGroupNm}/providers/Microsoft.AnalysisServices/servers/@{activity('ACT_Cmn_GetCubeConfigDetails').output.value[0].CubeNm}?api-version=2017-08-01

Body: 
{
	"properties": {
		"ipV4FirewallSettings": {
			"firewallRules": @{variables('Range')} ,
			"enablePowerBIService": true
		}
		}

}

Resource: 
https://management.azure.com/

Execution Snapshots & Results

Below are a few images captured during the execution of the pipeline, showcasing how the firewall settings in Azure Analysis Services were automatically updated:

Before Execution

The firewall was completely disabled, with no IP rules configured in the AAS server.

Pipeline Triggered from ADF

The pipeline Cube_FirewallUpdate_Automate was executed with the environment set to UAT and IP range parameters provided through the trigger.

After Execution

Post execution, the firewall was automatically enabled, and the provided IP was successfully added to the list of firewall rules.

Subsequent Run

On executing the pipeline again with a new IP, the new IP rule was appended without affecting the existing rule, thanks to the merge logic inside the pipeline.

Important Considerations

• Avoid Duplicate Firewall Rule Names: If you plan to run the pipeline again to whitelist a new IP, make sure you use a unique firewallRuleName. AAS does not allow duplicate rule names.
  
• Multiple Executions: You can run the pipeline multiple times to whitelist additional IPs. Just change the IP range and rule name in the parameters.

Security

• The pipeline uses Managed Identity authentication.
• No secrets or passwords are hardcoded.
• Role-based access control is used to allow ADF to update AAS.

Future Enhancements

• Add a step to delete existing firewall rules using ADF (e.g., decommissioning access).
• Support whitelisting multiple IP ranges in a single execution.
• Add email alerts or logging to monitor rule changes.
• Integrate with Logic Apps or Functions for dynamic triggers.

Final Thoughts

This solution shows how Azure Data Factory can go beyond traditional ETL and become a powerful automation tool for cloud infrastructure tasks , especially when combined with a metadata-driven architecture. By storing configuration like Subscription ID, Resource Group, Cube Name, and Environment in a central SQL table, the pipeline becomes reusable, scalable, and easy to maintain across environments.

Once the ADF Managed Identity is properly configured with the right RBAC roles, any authorized user can trigger this pipeline to whitelist IPs safely and independently.

If you're working with dynamic IPs or multi-environment cubes, this kind of automation is a game-changer.

What’s Next?

• You can try this out yourself by cloning the pipeline JSON from my  ADF-AAS-Firewall-Automation.
• Contributions and suggestions are welcome!