Can't update data Excel Online from Azure SQL datasource

Question

Here's what I've done:

1. Created new Excel file on my desktop. Added connection to Azure SQL using database authentication. Read in a view.

2. Save this document to Sharepoint for my tenant. It's hosted in the shared files folder in group I am an admin for.

3. Opening this file in Excel Online and trying to refresh first gives me a warning that this soruce might not be safe (no way to remove this warning afaik, but whatever). After clicking past this I am greeted with the error:

External Data Refresh Failed We were unable to refresh one or more data connections in this workbook. The following connections failed to refresh:

It seems like such an easy thing, yet as always Microsoft finds new ways to baffle me. I'm not getting any explanation to what the issue is and searching the internet brings up articles for Sharepoint 2013.

Answer 1

The functionality you are looking for is part of Excel Services. It's hard to find any information regarding Excel Services and SharePoint online, but I don't think it's fully supported. You only get links, pointing to SP 2013, Excel Services and Secure Stores.

There's this post from the Techcommunity that might give some insight: https://techcommunity.microsoft.com/t5/SharePoint-BI/Excel-Services-and-On-Premises-SQL-Data/td-p/3263. From the post it seems you need to user PowerPivot to connect to your data source.

Depending on your needs, I would strongly suggest to look into PowerBI. That is the path forward for BI related tasks and is nicely supported in especially the modern UI of SharePoint.