Connecting POWER BI TO SQL in private azure VM

Question

Connecting POWER BI TO SQL in private azure VM

Anonymous

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.

Senthil kumar 2,410 Reputation points

2026-05-14T13:00:48.96+00:00

Hi Sujit,

Is there any possible to fetch data using API from client end and store in central server and map to Power BI.

Thanks.

2 answers

Your answer

Senthil kumar 2,410 Reputation points

2026-05-14T13:00:48.96+00:00

Hi Sujit,

Is there any possible to fetch data using API from client end and store in central server and map to Power BI.

Thanks.

Answer 1

varchar02 51

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.

Anonymous

2026-05-14T11:11:53.42+00:00

Hi Varun,

There are 80 VMs in 80 different subnets in which SQL server is installed in all these VMs. These VMS are all under one subscription only. We want a centralized dashboard which will fetch data from all these SQL servers and get this data on PowerBI. Please note that the steps should be secure and compliant. Please help out on the same.

Answer 2

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.

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.
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.
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.
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.
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.
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.
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:

Manoj Kumar Boyini 19,590 Reputation points Microsoft External Staff Moderator

2026-05-18T13:03:24.12+00:00

Hi @Anonymous

I hope you had a chance to review the information shared earlier, and I hope this information has been helpful! If you still have questions, please let us know what is needed in the comments so the question can be answered.