Mind Circuitry

Background

Over the past year, I have been supporting a client with a multi-tenant application focused around data analytics. This article outlines some of the hurdles that were faced around Azure SQL Server when applying Defense in Depth security principles.

Note: The architectural decisions around the multi-tenancy aspect of the application was not designed by me, nor is is not under my control. My remit was to secure the application without significant design changes.

Architecture

This is a simplified architecture diagram of the product, with only the relevant elements included.

Data is pulled from the customer environment and stored within Azure SQL Server in the provider's tenancy. A PowerBI report is published to the customer's PowerBI App Service, which reads and visualizes data from the Azure SQL Server.

Problem

As part of regular security screening using the CIS Microsoft Azure Foundations benchmarking, it was identified that the Azure SQL Server had the following tick box enabled:

The documentation from Microsoft explains further:

When looking at this rule within PowerShell, it shows as 0.0.0.0/0!

Although the Microsoft Learn documentation says this is ONLY Azure IP Addresses, as a network guy, this screams to me says “everything”. Because we are applying defense in depth techniques (thus we cannot rely just on authentication alone), and this database contains customer data (with potentially PII within it), this checkbox needs to be disabled. Easy! ... well.... is it?

Options

Keeping in mind the requirement to have it as a multi-tenancy application, there were a number of options available (in order of least effort):

1. Programmatically get the PowerBI IP Address Ranges, and populate into ASQL Firewall Rules. Then disable “Allow Azure services and resources to access this server”.

2. Migrate the Azure SQL Server (ASQL) to Azure SQL Managed Instance(MI) or a Virtual Machine (VM). Apply a PowerBI Service Tag to the associated Network Security Group(NSG).

3. Implement Azure Firewall, connecting to a Private Endpoint on ASQL. Include an NSG on the Virtual Network to only allow IP's from PowerBI using it's Service Tag and\or put the Service Tag within the Azure Firewall rule.

4. Install Data Gateway in the Customers tenancy on Virtual Machine with a static Public IP address. Whitelist this IP Address in the ASQL Firewall Rules.

5. Use Virtual Network Gateway between customer tenancy and the service providers tenancy.

Let's run through these:

Option 1: Programmatically get PowerBI IP Address Ranges

This option involved getting all PowerBI Address ranges, and programmatically putting them into the ASQL firewall. This felt like a good solution – although not perfect, we are dramatically reducing the attack surface to just PowerBI App Service.

Firstly, before we write a script – let's get the JSON file from here, and then see how many address ranges there are for PowerBI to see if it's viable:

Well, this won't work, as there is a maximum of 256 rules for Server-level IP firewall rules.

Result: ❌ Rejected

Option 2: Use Virtual Network Gateway

This was a wildcard option – it would require work on the customer's tenancy side, and thus it was rejected quite early on, for the reasons that we should solve this project and secure the database with as little effort on the customer's tenancy as possible.

This involved using the Virtual Network Gateway and creating the relevant rules in the ASQL firewall to allow access from the customer's tenancy.

This article outlines more about how to achieve this, however for our requirements, it would be slightly different to their first diagram.

Result: ❌ Rejected

Option 3: Data Gateway in Customer Tenancy

Although this would technically work, it's additional resource that would require maintenance and additional resources at the customer's tenancy. For this reason alone, in a similar vein to Option 2, it was not a viable option for this issue.

Result: ❌ Rejected

Option 4: Migrate to Azure SQL MI / VM

There was feeling that this would work, however we were keen to move to PaaS services where possible. It was felt that running SQL Server on a VM would be a backwards step, as all the maintenance elements of running an VM would be required. The Managed Instance option was also possible to reduce these maintenance aspects, however it did come at quite a high cost. At this stage this option was rejected, how it was noted that this maybe revisited at a later time in the project.

Result: ❌ Rejected

Option 5: Implement Azure Firewall with Private Endpoints

This looked the most promising and following discussions with Microsoft, this was a strong contender, despite it not being their recommended solution. A Proof of Concept was set up in the following design:

This enabled us to access the database through a new hostname – customer.saasprovider.tld. When using SQL Authentication, it was important to pass the username including the server name:

sysadmin@customerdatabase.database.windows.net

To ensure only PowerBI endpoints could access the database, we added the Service Tag for PowerBI to a rule within the NSG.

It did not work! 😱 We had a suspicion that this was because the traffic was originating not from one of the IP addresses contained within the Service Tag. We were close.... When checking the documentation it stated that “Note: does not include frontend endpoints at the moment (e.g., app.powerbi.com).”. When raising this with Microsoft, they did not confirm or deny this, however they did outline the main cause of the issue. As the traffic was traversing Azure Firewall, it was being NAT'ed, therefore the source IP address accessing the ASQL private endpoint was not that of PowerBI, it was the internal IP address of the Azure Firewall! 🤯

Microsoft's suggestion was to add all the PowerBI IP address ranges into an Azure IP Group, and bind that to the relevant rule in the Azure firewall. We dutifully created an Azure Automation job to get the latest IP Addresses from the JSON file in #1, and populate an IP Groups object with said IP address. This worked a treat, however it was noted that Azure Firewall and IP Groups (to date) does not support IPv6 Addresses.

This finally looked like it was going to work! We started to plug in our PowerBI test report into the PoC and started configuration. Authentication was failing during the publishing phase from PowerBI to the PowerBI App Service. It appeared that when passing in username@customer.provider.tld failed, as ASQL had no understanding of that username. ASQL was expecting to see username@customerdatabase.database.windows.net, however we couldn't pass that username through as the ASQL Server was not available on the public internet!

At this point, we also discovered that even if we did run SQL on an Azure SQL Managed Instance, we would have exactly the same problem around authentication, thus option 4 was rejected again (and with confidence it would not work, even if the budget was appoved).

Result: ❌ Rejected

Next Steps

During the conversations with Microsoft, they suggested two more options:

• Use Semantic Model Sharing
• Power BI Embedded

Semantic Model Sharing although appeared to be a viable option, the lack of automation options around our DevOps deployment processes along with increased support administration also made this not a viable option. This is because we would have to share the semantic model with specific users, and thus they would need to be created as Guest Users within our EntraID.

Conclusion

This investigation took approximately six months, and included multiple scripts, proof of concepts and conversations with Solution Architects from Microsoft to help us meet the requirements of allowing access to Azure SQL Server in a multi-tenancy environment.

After discussions with senior management, the final result and conclusion was that the product should be re-architected so the report is published to the provider's tenancy, with a strong possibility of using PowerBI Embedded to surface this data via a web portal. This re-architecting is currently underway and being designed appropriately. In the meanwhile, we've learnt a huge amount of knowledge around how to secure Azure SQL Servers, which will do us in good stead moving forwards as other services and products are migrated from SQL Server on virtual machines or on premise. Hope you have learnt something too!