Connect to off-premises external vendor SQL database

[johnmelbourne]

Hi,

We have an external vendor providing a database solution and I want to connect to it via Power BI. Can someone direct me to some resources as I am having issues and most help refers to connecting to a SQL server on premises. 

The external vendor has installed an on-premises gateway, and that gateway is now enabled in my Power Bi service account.  

How do I connect to it?  I have the server name, database name and database login credentials. Now I just need to connect a report (either from the service or from desktop) to the data source.

Any ideas how this is done?

On reflection, I think what I need to do is get the external vendor to connect locally within its domain, then save a pbix, send it to me for upload to the service, then it may use the gateway to connect, and I can connect to the service datasource. Would that work?

Thanks

John

[lbendlin]

how are you accessing that database now from your Power BI desktop?  Do you have a VPN tunnel into the vendor?

[johnmelbourne]

We are not accessing the vendors database at the moment at all. They send files / make files available for download.

I am guessing if they connect locally within their domain to the database, then send me the .pbix file via email, then I upload to the service, it will use the gateway to access the database referenced in the .pbix.

Do you think that will work?

[lbendlin]

This will leave you flying blind, and rather sooner than later will become a maintenance nightmare. I would try to find alternative arrangements.

[johnmelbourne]

@lbendlin Why do you say flying blind? If the vendor imports the data into a pbix and keeps relationships, then the vendor sends the pbix to us, then we rework the model to something efficient and publish to the service, which connects via the vendors gateway to the data source and refreshes the data, what is the issue here?

What is the 'maintenance nightmare?'.  I note the database structure is highly unlikely to change.

Can you elaborate?

[v-kelly-msft]

Hi  @johnmelbourne ,

If you did so,you should first make sure that you have access to the data source provided by the vendor,then you  should also make sure that you can connect to the related gateway for refresh.Both the 2 steps may cause issues during maintenance.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[johnmelbourne]

Thanks, I already have a configrued gateway that is connecting to the external database. I think getting them to create a pbix locally and then me uploading to the service is the way forward.

Thanks

[v-kelly-msft]

Hi @johnmelbourne ,

Yes,when you get the .pbix file,modify the data source credential,which should be same with the setting in gateway.See below:

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!