Azure SQL database connectivity from Power bI Service

[meeradominic]

Hi All,

My organisation recently started migrating data from on-premise SQL Server to Azure SQL database. And when the "Public Network Access" not enabled, for that database, I can't establish a connection to it from Power bI service using personnael cloud connection, authenticating using database credentials. On further reading the documentations, I could see that I have to use Vnet data gateway to connect to my database. And this involves some new Azure subscriptions and cost involved for each inbound & outbound data traffic. My question is, can I simply use the on-premise data gateway instead of this? Or is there any other way to connect securely to Azure SQL database without turning the "Public Network Access" enabled and with no additional cost involved? It really doen't make any sense that I cannot connect to Azure SQL database from Power BI without enabling public network access.

Any suggestions appreciated.

Thanks!

[v-veshwara-msft]

Hi @meeradominic ,

Following up to see if your query has been resolved. If any of the responses helped, please consider marking the relevant reply as the 'Accepted Solution' to assist others with similar questions.

If you're still facing issues, feel free to reach out.

Thank you.

[meeradominic]

@v-veshwara-msft  Thanks for your response. I have some follow-up questions on this. We do alreday have a on-premise data gateway, but it is not on the same Vnet & Subnet as the Azure SQL database. So, can we use this on-premise gateway by enabling the private newtwork access to the database?

[v-veshwara-msft · Accepted solution]

Hi @meeradominic ,

Thanks for your follow-up question.

If your existing On-Premises Data Gateway is not in the same VNet & Subnet as your Azure SQL Database, simply enabling Private Network Access on the database may not be enough for the gateway to connect. The gateway needs network-level access to the database, which requires a private network connection between them.

Here are your options:

1. If the Gateway is in a Different VNet – You need to set up VNet Peering between the gateway’s VNet and the VNet where your Azure SQL Database is hosted. This allows private communication between them.

2. If the Gateway is On-Premises – You must establish a VPN connection or ExpressRoute between your on-premises network and the Azure VNet. This will allow the gateway to securely communicate with the Private Endpoint of Azure SQL Database.

3. Recommended Alternative – Instead of complex network configurations, you can install the On-Premises Data Gateway on an Azure VM within the same VNet & Subnet as Azure SQL Database. This ensures direct private connectivity without needing VPNs or VNet Peering.

Let me know if you need further details on any of these approaches!

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and a kudos would be appreciated.

Best regards,
Vinay.

[v-veshwara-msft]

Hi @meeradominic ,

We haven’t heard from you since our last response and wanted to check if the provided solutions met your needs. If they did, please consider accepting the solution to help others benefit. Otherwise, feel free to reach out for further assistance.

Thank you!

[v-veshwara-msft]

Hi @meeradominic ,

Just following up on your issue. If the provided response has resolved your concern, please mark it as the Accepted Solution to help others with similar queries. If you need further assistance, feel free to reach out.

Thank you!

[v-veshwara-msft]

Hi @meeradominic ,

Thank you for reaching out in Microsoft Fabric Community.
To securely connect Power BI Service to Azure SQL Database while keeping Public Network Access disabled and avoiding additional costs associated with the Virtual Network (VNet) Data Gateway,

The recommended solution is to use the On-Premises Data Gateway installed on an Azure Virtual Machine (VM) within the same Virtual Network as your Azure SQL Database. This setup allows Power BI Service to securely connect to your database without requiring public network access.
Steps:

1. Create an Azure Virtual Machine (VM)
   --Deploy the VM in the same VNet and subnet as your Azure SQL Database.
   --Block public IP access and configure Network Security Groups (NSGs) to allow only required internal communication.

2. Install the On-Premises Data Gateway on the VM
   --Download the On-Premises Data Gateway.
   --Run the installer and sign in with your Power BI account.
   --Choose Standard Mode for the installation.
   --Ensure the VM can connect to Azure SQL Database via private network access.

3. Configure Power BI to Use the Gateway

   --In Power BI Service, go to Manage Gateways and add a new data source.
   --Use the Private Endpoint of your Azure SQL Database for the Server Name.
   --Authenticate using database credentials or Managed Identity.
   
   

Here are some related Community Discussions:
Data Gateway - Connect Power BI Service to Azure SQL Database behind a VNet
How to access Azure SQL Database without allowing public network access

Some useful Microsoft Documentation:
Install and Configure an On-Premises Data Gateway
Azure SQL Database Private Endpoints

This method ensures secure communication between Power BI Service and Azure SQL Database without exposing it to the internet, while also avoiding the additional costs associated with the VNet Data Gateway.

I hope this helps! Please let me know if you need any further clarification.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and a kudos would be appreciated.

Best regards,
Vinay.