0
votes

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?

1

1 Answers

0
votes

Azure SQL Database is not supported. Azure SQL Managed Instance is supported with SharePoint Servers 2016 and 2019. See all the SQL Server options for SharePoint Server.

For SharePoint online you need Business Connectivity Services (BCS) to connect with data services and although the following document: Manage Business Connectivity Service Applications references Azure SQL Database, I believe that Azure SQL Managed Instance is supported but not Azure SQL Database. SQL Managed Instance is a full featured SQL Server instance on an Azure VM. SQL Database is a PaaS service with a subset of SQL Server functionality.