Can't access DB behind enterprise gateway on VM

[wedwo]

I'm not able to connect to my database sitting on an Azure VM behind the enterprise gateway... or rather, I don't know the next step to access it.

SQL Server is running on the VM.  I've set up the on premises data gateway on the VM and the network status says connected.  In Power BI online I can configure the gateway and have added a data source to it.  Clicking 'Test all connections' returns 'Online, you're good to go' at the gateway cluster level and 'Connection successful' at the data source level.  My user is added as a user who can publish reports that use this data source.

Now I run Power BI desktop on my PC (not on the VM), but I'm unable to connect to the data source.  I've tried 'Get data -> SQL server.  In the server name, am I meant to add the SQL Server instance name (SERVER\INSTANCE) from the VM as I would normally do on the VM?  This gives me a network-related or instance-specific error as I would expect since I'm not on the same LAN as the VM.  What do I add for Server?  How do I connect to it via the Gateway?

Do I have to run Power BI desktop on the VM and publish from there?  Surely not?

[v-xicai · Accepted solution]

Hi @wedwo ,

If SQL Server is running on Azure VM, you needn't to use gateway to refresh data in Power BI Service. In Power BI Desktop, you can refer to the video below which demonstrates how to connect to SQL Server database in Azure VM step by step.

https://www.youtube.com/watch?v=uEsnTlCX94s

If SQL Server is running on premise VM, then you need to considerate the difference of domain .

1.If your local computer Domain is the same with the VM Domain, then you needn't go to VM to run Desktop, just connect the SQL Server Database as usual.

2.If your local computer Domain is different from the VM Domain, then you need to run the desktop in on premise VM and publish from there, about how to install and configure getaway in VM, see:On-premises data gateway . For refresh via gateway in Power BI Service, you can refer to Manage your data source - SQL Server .

In addition, about how to find the difference of domain, you may need use Ping prompt, for further professional help, you can consult in SQL Server Community Forum.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[wedwo]

I believe there is a gap in the documentation in this regard.  What needs to be made clear is that, in order to publish anything behind the PowerBI gateway, PowerBI desktop and the gateway need to be running on a domain with direct access the resources you need to query (DB, files, etc.).  Once those data sources have been published to PowerBI in the cloud via the gateway, they are available to any users who have been assigned access to them via their PowerBI account from PowerBI desktop or PowerBI in the cloud on any internet connected computer.

It took me some time to realise that the gateway is not a two-way system - it only pushes data to the PowerBI cloud service from behind your firewall, it does not allow connections to the data hosted on your server directly.