Forum Discussion
Excel Services and On-Premises SQL Data
If you're talking about Excel Online in Office 365 connecting to on prem data, that is possible with the Gateway if you're using PowerPivot. You can use Power BI to schedule an Excel Workbook refresh from on premises data.
If you're talking about on prem Excel Services or Excel Online, you can configure the data connection to refresh on open.
John White Does it allow user to do the on demand refresh from on prem database if user is added in the ad group which gives appropriate permission to the database? I mean it works in excel desktop application but would it work the same way in Excel online too? The issue with current on prem data gateway is it runs from local machine rather then running from vm instance so the gateway can't be guaranteed to be running all the time from local machine. We want users to be able to refresh the power query dataset if they are connected to the network so excel desktop application works fine in that manner but having E5 license for that comes with its own cost I was wondering if the same is possible with F5 licensing where it only allows office online version.
John White can you share more information on how to configure Excel Online to access on-premise SQL Data. And does that also apply to Mac Office 365 Excel. I have attached an error I received when I tried refreshing excel online that is connected to an On-premise SQL Server
How Excel is connected, Power Query?
SergeiBaklan Yes, I am using Power Query
ambroseJoel - All that you should need is an on-premises data gateway installed, and the data source in question registered. However, the error that you are showing indicated something else, which is odd, but I'm not sure about.
John White is the getaway that you are referring to the same one for Power BI? or do we have another one? and how do you connect it to Excel?
I have tried this multiple times. If you still are monitoring this thread I sure would appreciate some help to understand how you accomplished this.
