Remotely connect to data source on Azure VM SQL Server

[s15]

Hi all,

I have completed building simple data warehouse running on an Azure VM with SQL Server. The connection string is not intended to be published over the Internet. From the VM, I can use Power BI Desktop to connect to the data source. However, this requires me to RDP to the VM. I'd like to know if I can use Power BI Desktop on laptop to connect to my data source. I thought I would have to install Power BI On-premises data gateway to import data source to Power BI service then use Power BI Desktop to pull out the data. Is this technically possible?

Your recommendation is always appreciated.

[GilbertQ]

Hi @s15

If you have your SQL Server on a VM in Azure or On-Premise it will be the same way in which you connect, either directly or via for example a VPN into Azure. By doing it in this way you can then connect Directly with Power BI Desktop and either import the data into your Power BI Desktop, or use DirectQuery.

Then once you have done either import or DirectQuery, you can then install and configure the On-Premise Gateway on your Azure SQL VM. This will then allow you to refresh the data (with either the Import Mode, or DirectQuery)

[s15]

Hi @GilbertQ

Do you recommend to use Azure VPN Gateway (e.g. Point-to-Site) to allow Power BI Desktop to connect to database server in a VPN-ed network?

What I'm understanding from Power BI Gateway is that it is a tunnel which translates the local virtual machine network to accessible one so from Power BI Service Portal, I can connect with local virtual machine name.

There might be another option which is to configure DNS for my connection string. I'm testing it.

[GilbertQ]

Hi @s15

It all depends on how you want to connect to your Azure SQL VM.

What the Power BI Gateway does is it connects the Power BI Service to your On-Premise data. 

As per your error below, when you are setting up your Data Source you must make sure that both the Data Source Name and the Server name are identical in your Power BI Desktop connection also. If those do not match then the data will not be able to connect and refresh.

[s15]

It seems like I need Power BI Desktop to create a data source first right? I think there should have been something like "Power BI Gateway is the translator connection of the combination of local data source and Power BI Desktop"

[GilbertQ]

Yes you would need to create the data source in Power BI desktop and then also configure it in the gateway also