How to connect PBI Desktop to a remote SQL Server held on a third-party's Virtual Machine?

[D_PBI]

Hi all,
I would like to use Power BI Desktop, on my local PC, to extract data from SQL Server that is held on a third-party's Virtual Machine. How do I do this?

Right now it is failing when I try and I do not know why. My hunch is it's not been configured/opened up correctly at the third-party end. To confirm this, I would to understand what is it exactly I need to choose as my connection method and enter as the configuration properties, and how. The more I look into it the more I need a sense check - hence posting this.

Forget the PBI Service concerns for now. I am simply trying to connect from PBI Desktop.
I've been told I have read access to the database (not all contents/objects on the SQL Server but only at a specifc database level).

The public DSN for the SQL Server is (replacing sensitive information with 'x'):
xxxxx-xx-sage1.uksouth.cloudapp.azure.com
The SQL Server name is (replacing sensitive information with 'x'):
xx-sage1
The database name is (replacing sensitive information with 'x'):
XX_Live_Data

Without wanting to muddy watters, this third-party have created a On-Premises Standard Data Gateway and added me as 'Connection Creator'. Through this, I have successfully been able to add a Data Source against this Gateway in the PBI Service. In creating this Data Source I had to use a Domain before my username (i.e. domain\username) and then enter the password the third-party provided. So in acheiving the creation of this Data Source I must have access to the target database on the remote SQL Server, albeit all being configured in the PBI Service.

The Domain and Username are (replacing sensitive information with 'x'):
XX-SAGE1\xxxxx.xxxxx

All I want to do is connect PBI Desktop to the remote SQL Server. If you can help guide me on what configuration I need then, if it doesn't work, I can go back and say I'm doing the right thing from a PBI perspective, so it must be the secuirity/opening up of the Server from my local machine that isn't correct.

Your help will be much appreciated.
Thanks.

[D_PBI]

@Anonymous - The requirements increased and so I never went down this route in the end. Instead, I've implemented Azure Data Factory to perform data integration processes.

[Anonymous]

Hello there,
Were you able to resolve this problem? If yes, Can you please guide us how? I'm facing exactly same issue at the moment. ANy help in thsi regard would be highly appreciated. 

Thanks!!

[Greg_Deckler]

@D_PBI The #1 thing that causes problems with this is that your machine needs to be added to the allowed IP address list for the firewall.

[D_PBI]

Thanks @Greg_Deckler - based on the information I've provided in my post, are you able to conform (maybe a screenshot) of the connection method and confirguration properties that I should be using/entering?  If I had this then I will have confidence that what I'm trying is correct and that it's something outside of PBI (me) that is failing.

[Greg_Deckler]

@D_PBI So if you have an Azure SQL Server for example, you would go to portal.azure.com. You would bring up the properties for you SQL Server. Then you would go to Networking on the left-hand side and under the Public access tab at the top you would ensure that under Public network access that "Selected networks" is toggled on. Then, under Firewall rules a little further down the page you would create a rule allowing the IP address of your computer.