Forum Discussion

evanliang's avatar
evanliang
Icon for Microsoft Employee rankMicrosoft Employee
10 years ago
Solved

Not able use data source configured on on-premises data gateway

I installed on-premises data gateway on one box, and addedQL server DB as one data source, all connections are green. And gateway can be found on PowerBI service page, up and running.   Wherever da...
  • evanliang's avatar
    evanliang
    10 years ago

    Thanks much you guys replies. Eventually I figured out the reason. Originally I though Data Gateway could be used by Power BI Desktop app, but actually it's not the truth. Because Power BI wiki for Get Data from SQL is a little not clear to me on what machanisim is inside.

     

    Backend SQL service is up and running properly. The machine(dev box) where is running Power BI Desktop app needs to be able to connect the SQL service directly using the particualr address pointing to backend SQL service. And gateway needs to configure exactly same address and DB name configured in Power BI Desktop app. And Power BI service internally does magic thing to match data source setting and get data from gateway service.

     

    Based on my findings. here are knowledge points what I have learned

     

    1. Gateway Service is only avaiable to Power BI service and no configurtion is required between both

    2. Power BI Service does mapping the data source with exactly server address used in Power BI Desktop app

    3. Power BI Desktop app on where machine needs to be able to access SQL service directly

    4. Gateway Service on where machine needs to be able to access SQL service directly

     

    Thanks

    Evan

Anonymous's avatar
Anonymous
Not applicable
10 years ago

Hi evanliang，

Based on your description, you successfully add SQL Server data source under your gateway but fail to connect to SQL Server from Power BI Desktop. Are the power BI Desktop and SQL Server installed in two separate machines? If so, please also check the following things besides checking SQL Server Service status.

1. Make sure you are able to ping the server where SQL Server is installed.
2. Ensure that you enable TCP/IP protocol in SQL Server Configuration Manager(SSCM).
3. Make sure that the SQL Server Browser service is running.
4. Configure the Windows Firewall for the SQL Server port and SQL Browser service if firewall is on.

Reference:
https://www.mssqltips.com/sqlservertip/2340/resolving-could-not-open-a-connection-to-sql-server-errors/

Thanks,
Lydia Zhang

evanliang's avatar
evanliang
Icon for Microsoft Employee rankMicrosoft Employee
10 years ago

Thanks much you guys replies. Eventually I figured out the reason. Originally I though Data Gateway could be used by Power BI Desktop app, but actually it's not the truth. Because Power BI wiki for Get Data from SQL is a little not clear to me on what machanisim is inside.

 

Backend SQL service is up and running properly. The machine(dev box) where is running Power BI Desktop app needs to be able to connect the SQL service directly using the particualr address pointing to backend SQL service. And gateway needs to configure exactly same address and DB name configured in Power BI Desktop app. And Power BI service internally does magic thing to match data source setting and get data from gateway service.

 

Based on my findings. here are knowledge points what I have learned

 

1. Gateway Service is only avaiable to Power BI service and no configurtion is required between both

2. Power BI Service does mapping the data source with exactly server address used in Power BI Desktop app

3. Power BI Desktop app on where machine needs to be able to access SQL service directly

4. Gateway Service on where machine needs to be able to access SQL service directly

 

Thanks

Evan