Connecting to SQL server offsite via on-prem gateway using Power BI desktop

[johnmelbourne]

Hi,

We have an external vendor that is hosting a SQL database which we need to connect to through Power Bi desktop. They have installed the on-premises gateway succesfully and from our Power BI service gateway settings menu (I have a pro account and am the admin) I am getting "Online: You are good to go.", which is great.

Now I need to connect to the database from Power BI desktop. I have a Pro account  I am having issues and not sure where the problem lies.

I have credentails of 

Username:  domain\username

Servername: UAT-SERVERNAME

Database: UAT-DBNAME

Can I connect from Power Bi desktop from external to the domain, into the external vendors domain from Power Bi desktop?

What comibination of credential should I use in these menus?

Should I use windows, database or Microsoft account?

I am not sure what account to use, Windows, Database or Microsoft.

I am not sure what level to apply, servername, or servername;UAT-DBNAME

Does Power BI desktop connect to the database via the Power BI service, to the on-premises gateway then to the database. 

Or does Power Bi desktop connect via Power Bi desktoip to the external vendor gateway, then to the database?

In one combination of trying to connect I am getting the error 40, which has to do with opening communication ports. That is the error in power BI desktop, but the service is connected fine.  Do they perhaps have to open the appropriate ports so I can connect via power Bi desktop even though the Power BI service gateway appears online and ready to go?

Any assistance would be appreciated.

[lbendlin]

I'm afraid that's not how this works.

Your third party vendor hosts an on-prem database in their network. They need the gateway to allow the Azure Service to access that database. This has nothing to do with your Power BI Desktop.

Most likely you access your vendor's network through a VPN. If so, then your connection from your Power BI Desktop to the vendor's database should be transparent as long as you are connected to the VPN.

One other option would be to host your database not at the vendor's network but in the public cloud (for example on Azure).  That would remove the need for a gateway, but obviously would come with some security concerns that you will want to address first.

[johnmelbourne]

Thanks @lbendlin 

I had an idea that might be the case. That I needed to be within their network to access the database, and couldn't go from PBI DT to Service to their network. 

I don't currently have access to the external vendors network at all. So instead of that, could the vendor create a pbix file, connect to the SQL server within their own network, import the data into the pbix, send the pbix to me to model and build a report / reports, which I then upload to the service which then can refresh through the service and gateway to the SQL server?

And then perhaps could i download the pbix from the service, with updated data everynow and then? 

Would that work?

and if it would work, is that just a mess and I should avoid arrange VPN access to directly access the SQL server.

Thanks for your advice and knowledge sharing. 

John

[lbendlin · Accepted solution]

It would work to a point - you could only do very limited work in Power Query since you could not refresh the SQL data source at all.  You could still do the usual UI work and DAX etc.

It might be quicker if you set up your own SQL Server with some sample data and the same structure as the Vendor's server.  That way you can test the full cycle on the desktop, and only have to make sure to change the connection string when you hand it back over to the vendor or when you publish yourself. Might want to try using parameters on the service for that.