Juan Sebastian Timaran - Cloud Center of Excellence
Power BI is a tool that helps companies connect different data sources, visualize data, and discover relevant components. This is why it has become important for leaders who require constant decision-making and operational management to improve their efficiency and performance.
Azure SQL is commonly used as a data source nowadays, thanks to the rapid growth of Microsoft Azure cloud service deployments. It's common to find environments where most Power BI data sources are hosted in the cloud, such as SQL Databases, Blob Storage, Table Storage, Cosmos DB, etc. In this blog post, we'll focus on SQL databases as the source of our data, since this service is one of the most deployed within the Azure portal.
Connection scenario
As part of the best security practices, Azure SQL Databases are typically created to be accessed only from a Private Network. This represents a more complex connectivity scenario when Power BI tries to retrieve data from this kind of source. As shown in the image below, once Public Access is disabled in SQL DB, the online version of Power BI won’t be able to retrieve any data, as SQL will detect that the request comes from the internet."
This revision maintains the flow and clarifies some points for better understanding.
How to solve it
To solve this connectivity problem, you need to follow the following steps:
- Go to Azure portal and Access to the subscription where your SQL DB is deployed.
- Select Resource Providers and make sure that PowerPlatform is registered.
- Go to a Virtual Network where you can select/create a subnet (/28 or /27).
- Once Subnet is created or choose, select “Microsoft.PowerPlatform/vnetaccesslinks” in subnet delegation.
- Go to Power Bi Online.
- Go to settings and select “Manage connection and gateways”.
- Select “Virtual Network (vnet) data gateway” > New
- Select the subscription, Resource group and Vnet where your subnet was created.
- Assign permissions if specific people need access to vnet data gateway.
This set up will allow Power BI online to use Private IP address range to access SQL DB privately.
Improving efficiency and performance
TP is a Microsoft Azure Solutions Partner for Data & AI, highlighting our expertise in creating tailored analytics and AI solutions. We help businesses tackle challenges, boost efficiency, and gain valuable insights.
Visit our technology services page for more details.
Other impactful stories