Connect to on-premise database through Gateway from my dev?

[KNO]

Hi

I am thinking about a way to connect to a customer on-premise SQL db. I have done the following steps

1. installed the On premise gateway on customers machine
2. Created a Data source on power platform
3. Tried to connect from Visual Studio Tabular Project to the data source on Power platform

I returns  (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)". So nothing going through.

Any suggestions?

[Anonymous]

Hi  @KNO ,

1. You can pull its users into guest users through Azure AD.

2. Go to the data set interface and give the user the build permission for this data set.

3. Use Power bi to connect to this dataset, you can collect the information of this dataset.

This is the related document, you can view this content:

https://docs.microsoft.com/en-us/power-bi/guidance/whitepaper-azure-b2b-power-bi

https://docs.microsoft.com/en-us/power-bi/connect-data/service-datasets-build-permissions

Best Regards,

Liu Yang

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

[GilbertQ]

Hi @KNO 

You will have to use the customers Power BI login when installing the gateway so that it connects to their tenant.

[Burningsuit]

Hi @KNO 

You will need an account on the Customer's Power BI tennant. Then give that account access to the Gateway that serves the SQL database. You can then use Dataflows to create a connection to the SQL database, select and clean the table(s) you want from the Datasebase. You can then use Power BI desktop to pull the Dataflows into a Datamodel for visualisation. This way you only need a Power BI account in the customer's tennant. No need for Tunneling/VPN's etc.

Hope this helps

Stuart

[KNO]

Hi and thanks for answers. I have one question. The account that should be created on the Customer Power BI Tennant, should that be an account bound to the customer tennant or can the customer just add my own account?

[GilbertQ]

Hi @KNO 

Your PBIX will have to connect to the same underlying data source directly. 

Then once you upload your PBIX file you will then map it through to the same data source. If it does not map through it will show you in the dataset settings what data source is needed.

Here is a working example: Tutorial: Connect to on-premises data in SQL Server - Power BI | Microsoft Docs

[KNO]

Hi and thanks for answering - Will I not be able to utilize the Gateway from my developer pc? Do i need a tunnel/vpn to the customers SQL instance in order to connect to the SQL from my dev machine?