Connecting POWER BI TO SQL in private azure VM

Anonymous
2026-05-14T10:28:08.2866667+00:00

Hi Team,

We are planning to develop a centralized Power BI dashboard for SQL Server configuration and environment visibility across customer environments. Current requirement is mainly focused on collecting static SQL Server configuration details from approximately 80 customer SQL Servers hosted on Azure VMs.

Most of the customer environments are running SQL Server Standard Edition, and the customer environments are hosted across different VNets/private Azure networks.

Before proceeding further, we would like guidance from the Pro-Arch team regarding the standard and approved approach for enabling secure connectivity between Power BI and SQL Servers hosted on private Azure VMs. Since customer environments are isolated across multiple VNets, we would also appreciate guidance on the recommended approach from scalability and security perspectives.

Please share the recommended standard approach and any prerequisites or governance considerations we should follow before implementation.

SQL Server on Azure Virtual Machines

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Manoj Kumar Boyini 19,590 Reputation points Microsoft External Staff Moderator
    2026-05-17T08:46:34.1733333+00:00

    Hi @Anonymous

    Since all 80 SQL Servers are hosted on private Azure VMs across different subnets/VNets within the same subscription, the recommended Microsoft-supported approach is to use a centralized secure connectivity model instead of exposing SQL Servers directly to Power BI.

    1. The recommended architecture is to deploy a centralized On-premises Data Gateway cluster on dedicated Windows VMs inside a secure Hub/Management VNet in Azure. This gateway acts as the secure bridge between Power BI Service and the private SQL Servers using outbound-only communication, without requiring public exposure of the SQL Servers.
    2. Configure a Hub-and-Spoke network topology where the Hub VNet hosts the gateway cluster and the Spoke VNets contain the SQL Server VMs. Connectivity between VNets should be established using VNet peering or VPN/ExpressRoute depending on the existing network design. NSGs/firewall rules should allow SQL access only from the gateway subnet to the SQL Servers over private IPs.
    3. For security and compliance, SQL Servers should remain private-only with no public inbound access to TCP 1433. Use dedicated read-only SQL accounts (or domain-integrated authentication where applicable) for metadata/configuration collection, enable SQL auditing, and securely manage credentials through gateway configuration and Azure Key Vault wherever possible.
    4. Since the requirement is primarily to collect static SQL configuration/environment details, the preferred enterprise design is to use a centralized collection layer instead of allowing Power BI to directly query all 80 SQL Servers continuously. A scheduled collection process using Azure Automation, PowerShell, Azure Functions, SQL Agent jobs, or Data Factory can gather the required metadata and store it in a central repository such as Azure SQL Database, Log Analytics, Data Lake, or Synapse.
    5. Power BI should ideally connect only to this centralized repository rather than maintaining 80 independent live connections. This approach significantly improves scalability, reduces gateway complexity, simplifies credential management, minimizes refresh failures, and provides better long-term operational governance.
    6. For high availability and scalability, deploy at least 2 gateway nodes in cluster mode and monitor gateway performance regularly. Additional nodes can be added later if refresh workloads increase.
    7. Additionally, if centralized governance and inventory visibility are required, Azure Arc-enabled SQL Server and Azure Monitor/Log Analytics can also be integrated to provide broader compliance, monitoring, and operational insights.

    Reference documentation:

    Was this answer helpful?

  2. varchar02 51 Reputation points
    2026-05-14T10:50:21.11+00:00

    Hi Sujit,

    What I understood from your statement is it's a multi-tenant management. I assume all 80 customers have their own Subscription/TenantId. One option that will work is to install a On-premises Data Gateway (Std Edition) in each customer VNet ( if they are somehow connected you can form them into a cluster and deploy Gateway in that cluster. This approach is well secured since you are isolating each customer by deploying Gateway in their own systems. You will have to store SQL credentails (SQL Authentication with read-only access) in PBI's gateway. Using a single id & password will hamper the audit and roration if required. You need to enable SQL Server Audit logs for all connections sourced from the gateway account.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.