I am migrating from an on-prem SQL database and on-prem SharePoint server to Azure SQL Database and SharePoint Online.
In the current setup I have a SharePoint library containing Excel files that contain data from the SQL database. The connection is defined in an odc file. Excel Services Authentication is setup in this odc using a stored account. This allows data refresh from Excel Web App.
I am trying to setup the same thing in our new environment, but can't find any information on how to set it up. I have tried creating an excel file with a connection to the Azure SQL database and uploading that into a document library in SharePoint Online. If I open the workbook in the browser and click refresh data I get an error saying unable to refresh one or more data connections. If I open in the Desktop App then I can refresh without any issues. Is what I'm trying to achieve possible or do I need to rethink the setup?