How do I connect Office 365 SharePoint to an on-premise SQL Server?

Question

We use SharePoint online and have custom SFPX webparts. How do I connect our hosted SharePoint to an on-prem SQL server? We built a REST API that works to receive the information from SQL but the SFPX is not able to see the API. I need recommendations. Should I build the API in Azure and add a SQL database in Azure that replicates to the on-prem SQL server?

Answer 1

Hi @Patrick Klungle ,

Thank you for posting in this community. According to my search, here are some recommendations based on your situation:

1. Utilize Azure Hybrid Connections: Azure Hybrid Connections enable Azure services, such as Azure App Service, to securely connect to on-premises resources without extensive firewall reconfiguration. By implementing this, your Azure-hosted API can access your on-premises SQL Server. This approach is detailed in a case study where SPFx web parts connect to on-premises databases via Azure-hosted REST APIs using Hybrid Connections.https://pnp.github.io/blog/post/connecting-to-sharepoint-online-to-on-premises-databases-with/
2. Implement Business Connectivity Services (BCS): SharePoint's BCS feature allows SharePoint Online to connect to external data sources, including on-premises SQL Servers. By configuring BCS with your Azure-hosted API, you can create external content types and lists in SharePoint that reflect your on-premises data. This method is outlined in Microsoft's documentation on connecting SharePoint to on-premises databases. https://learn.microsoft.com/en-us/answers/questions/76915/sharepoint-connecting-to-sql-on-premise https://learn.microsoft.com/en-us/sharepoint/dev/general-development/create-hybrid-connectivity-apps-for-sharepoint
3. Consider Data Replication to Azure SQL Database: If maintaining a direct connection between SharePoint Online and your on-premises SQL Server proves challenging, consider replicating your on-premises SQL data to an Azure SQL Database. This approach allows your Azure-hosted API to interact with cloud-based data, simplifying connectivity and potentially improving performance. However, ensure that the replication process aligns with your data consistency and latency requirements.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.