Client side web parts accessing external data

[Pieter Veenstra]

I have seen the CRUD exmple to accss data within SharePoint.

https://dev.office.com/patterns-and-practices-detail/11246

I'm looking for an example that accesses an OData service that gets it daat from my on-premises SQL Server database. Does such an example already exist? I am mainly worried about how to secure my data and my calls to the web services.

[#2 · Accepted solution]

I think that for this scenario is where the Add-in model still fits well.
Another option could be to build an Azure AD App securing a custom WebAPI. That web API can get data from your on premises infrastructure using an encrypted connection string and even with a specific connection between your Azure website and the On-Prem SQL Server (kind of this article: https://docs.microsoft.com/en-us/azure/app-service-web/web-sites-hybrid-connection-connect-on-premis...).

This spfx sample is calling an Azure AD secured custom WebAPI from spfx:
https://github.com/SharePoint/sp-dev-fx-webparts/tree/master/samples/react-aad-webapi

[#3]

Is both SharePoint & SQL On-Premises for you? If so, a simpler solution might be to go with Business Connectivity Services (BCS) to connect to SQL. You would then be able to expose the necessary tables as External Lists. From there you can either leverage the OOB listforms for performing CRUD operations, or build your own front-end leveraging the ListData.svc or REST API (SP2013+). You could then leverage traditional SQL security with Active Directory Groups on the backend to make sure users aren't accessing data they shouldn't.

[#4]

Hi @Jared Matfess,

This is Office 365. Initially we were going down the route of BCS however this doesn't perform at all. It takes about 20 seconds for a query when tables exceed 15 columns.

[#5]

Got it @Pieter Veenstra, then yeah you're probably going to need to explore what @Luis Mañez recommended with the Azure Hybrid Connections. Takes a bit more setup work, but it should do the job.

Here's a good overview link.