Forum Discussion

Greg_Davurse's avatar
Greg_Davurse
Helper I
10 years ago
Solved

Unable to refresh using On Premise Gateway

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    10 years ago

    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's avatar
    Greg_Davurse
    10 years ago

    Hi Anonymous, 

     

    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

Anonymous's avatar
Anonymous
Not applicable
10 years ago

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's avatar
Greg_Davurse
Helper I
10 years ago

Hi Anonymous

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    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's avatar
      Greg_Davurse
      Helper I
      10 years ago

      Hi Anonymous,

       

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

       

      Thanks,

       

      Greg 

      • Anonymous's avatar
        Anonymous
        Not applicable
        10 years ago

        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