Unable to refresh using On Premise Gateway

[Greg_Davurse]

I am having an issue connected my SQL DB to my Power BI Report. 

I created a set of reports in Power BI Desktop and then Published the reports to powerbi.com, at that time, I also downloaded the On-premise Gateway on the server hosted the SQL DB (where i am pulling my data for the reports). I have successfully added the Gateway to my Power BI and loaded the associated SQL DB datasource, which the status shows Connected. 

However, when I try and refresh my data or Schedule a Refresh, I have no luck, and I receive an error saying my data gateway (Personal Gateway) is offline or couldn't be reached....am I missing something? I thought I didn't need the Personal Gateway since I was using the On-Premise Gateway.  

Also, when I go to Settings --> Datasets, my Gateway Connection and Schedule Refresh is grayed out. It tells me before I can schedule a refresh i need to install a data gateway (Personal), etc...but I have already done this with the On-Premise Gateway, as mentioned above. 

I have uninstalled an reinstalled the gateway a couple times now, and still nothing. 

Any thoughts or help on how to resolve this issue are greatly appreciated!

[v-yuezhe-msft · Accepted solution]

Hi @Greg_Davurse,

Based on your screenshot, you have multiple data sources（SQL Server, Excel） in your current PBIX file. In your scenario, besides adding SQL data source under the on-premises gateway, you will need to add Excel data sources under the gateway. This way, you will be able to schedule refresh for this dataset. I have tested this scenario successfully.


Thanks,
Lydia Zhang

[v-yuezhe-msft]

Hi @Greg_Davurse,

When you add SQL Server data source to the on-premises gateway, please make sure server and database name completely match between Power BI Desktop and the data source within the on-premises data gateway.

For example, if you supply an IP Address for the server name, within Power BI Desktop, you will need to use the IP Address for the data source within the gateway configuration. Otherwise, you will not able to refresh the data source. For mode details, please check the following article.

https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-enterprise-manage-sql/  

Thanks,
Lydia Zhang

[Greg_Davurse]

Hi @v-yuezhe-msft

Thanks for the feedback. I've checked to make sure my server and DB name are completely the same between Power BI Desktop and the data source within the on-premise gateway, and I believe they are the same, but still no luck. Please take a look at the screen shots below and let me know what you think. Also, I'm using a Trial Pro Licencse, so would that matter? 

The first source shown below is the remote server and DB I am trying to connect to. The other two data sources are excel files that I don't need updates from, as the data is static (would this be causing an error?)

This is where I having trouble...trying to schedule a refresh on the reports I've loaded into Power BI, but it tells me I need to install a personal gateway...but already have the On-premise gateway setup and successfully connected.

Any thoughts?

Thanks,

Greg Gillespie

[v-yuezhe-msft]

Hi @Greg_Davurse,

Which authentication method do you choose when you connect to SQL Server from Power BI Desktop? If you choose Windows authentication, you will need to choose the same authentication method when adding data source under the gateway.

Thanks,
Lydia Zhang

[Greg_Davurse]

Hi @v-yuezhe-msft,

I am using Basic authentication in Power BI Desktop and in my Gateway data source.  

Thanks,

Greg 

[v-yuezhe-msft · Accepted solution]

Hi @Greg_Davurse,

Based on your screenshot, you have multiple data sources（SQL Server, Excel） in your current PBIX file. In your scenario, besides adding SQL data source under the on-premises gateway, you will need to add Excel data sources under the gateway. This way, you will be able to schedule refresh for this dataset. I have tested this scenario successfully.


Thanks,
Lydia Zhang

[Greg_Davurse · Accepted solution]

Hi @v-yuezhe-msft, 

The two excel files were definitely the issue. Thanks for pointing that out. In order to make this work for my customer, I had to upload these excel files to my customer's OneDrive and access them that way. (I didn't realize Power BI would constantly reference those excel files - I thought once I imported the data, I could eliminate that connection, as the data is static and would never change). However, in order to access the two excel files in OneDrive, you have to use Oauth credentials, and I am also trying to use the On-Premise Enterprise gateway on the customer's Server to access their SQL as well, but when I mashup my scenarios , excel files and the SQL server, my On-Premise gateway is greyed out.   

I was infomored this is a current limitation of the On-Premise Gateway, as it does not support OAuth Authentication, so as a work around, in order to get my report to work properly/refresh, I installed the personal gateway on the Server, as it supports OAuth. 

Thanks again for the help!

Greg Gillespie