SOLVED

Client side web parts accessing external data

%3CLINGO-SUB%20id%3D%22lingo-sub-53307%22%20slang%3D%22en-US%22%3EClient%20side%20web%20parts%20accessing%20external%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-53307%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20seen%20the%20CRUD%20exmple%20to%20accss%20data%20within%20SharePoint.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdev.office.com%2Fpatterns-and-practices-detail%2F11246%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdev.office.com%2Fpatterns-and-practices-detail%2F11246%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20looking%20for%20an%20example%20that%20accesses%20an%20OData%20service%20that%20gets%20it%20daat%20from%20my%20on-premises%20SQL%20Server%20database.%20Does%20such%20an%20example%20already%20exist%3F%20I%20am%20mainly%20worried%20about%20how%20to%20secure%20my%20data%20and%20my%20calls%20to%20the%20web%20services.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-53775%22%20slang%3D%22en-US%22%3ERe%3A%20Client%20side%20web%20parts%20accessing%20external%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-53775%22%20slang%3D%22en-US%22%3E%3CP%3EGot%20it%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F2387%22%20target%3D%22_blank%22%3E%40Pieter%20Veenstra%3C%2FA%3E%2C%20then%20yeah%20you're%20probably%20going%20to%20need%20to%20explore%20what%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1815%22%20target%3D%22_blank%22%3E%40Luis%20Ma%C3%B1ez%3C%2FA%3E%26nbsp%3Brecommended%20with%20the%20Azure%20Hybrid%20Connections.%20Takes%20a%20bit%20more%20setup%20work%2C%20but%20it%20should%20do%20the%20job.%3CBR%20%2F%3E%3CBR%20%2F%3EHere's%20a%20good%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fbiztalk-services%2Fintegration-hybrid-connection-overview%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Eoverview%20link%3C%2FA%3E.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-53773%22%20slang%3D%22en-US%22%3ERe%3A%20Client%20side%20web%20parts%20accessing%20external%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-53773%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F16549%22%20target%3D%22_blank%22%3E%40Jared%20Matfess%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20Office%20365.%20Initially%20we%20were%20going%20down%20the%20route%20of%20BCS%20however%20this%20doesn't%20perform%20at%20all.%20It%20takes%20about%2020%20seconds%20for%20a%20query%20when%20tables%20exceed%2015%20columns.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-53766%22%20slang%3D%22en-US%22%3ERe%3A%20Client%20side%20web%20parts%20accessing%20external%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-53766%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20both%20SharePoint%20%26amp%3B%20SQL%20On-Premises%20for%20you%3F%20If%20so%2C%20a%20simpler%20solution%20might%20be%20to%20go%20with%20Business%20Connectivity%20Services%20(BCS)%20to%20connect%20to%20SQL.%20You%20would%20then%20be%20able%20to%20expose%20the%20necessary%20tables%20as%20External%20Lists.%20From%20there%20you%20can%20either%20leverage%20the%20OOB%20listforms%20for%20performing%20CRUD%20operations%2C%20or%20build%20your%20own%20front-end%20leveraging%20the%20ListData.svc%20or%20REST%20API%20(SP2013%2B).%20You%20could%20then%20leverage%20traditional%20SQL%20security%20with%20Active%20Directory%20Groups%20on%20the%20backend%20to%20make%20sure%20users%20aren't%20accessing%20data%20they%20shouldn't.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-53678%22%20slang%3D%22en-US%22%3ERe%3A%20Client%20side%20web%20parts%20accessing%20external%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-53678%22%20slang%3D%22en-US%22%3EI%20think%20that%20for%20this%20scenario%20is%20where%20the%20Add-in%20model%20still%20fits%20well.%3CBR%20%2F%3EAnother%20option%20could%20be%20to%20build%20an%20Azure%20AD%20App%20securing%20a%20custom%20WebAPI.%20That%20web%20API%20can%20get%20data%20from%20your%20on%20premises%20infrastructure%20using%20an%20encrypted%20connection%20string%20and%20even%20with%20a%20specific%20connection%20between%20your%20Azure%20website%20and%20the%20On-Prem%20SQL%20Server%20(kind%20of%20this%20article%3A%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fapp-service-web%2Fweb-sites-hybrid-connection-connect-on-premises-sql-server%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fapp-service-web%2Fweb-sites-hybrid-connection-connect-on-premises-sql-server%3C%2FA%3E).%3CBR%20%2F%3E%3CBR%20%2F%3EThis%20spfx%20sample%20is%20calling%20an%20Azure%20AD%20secured%20custom%20WebAPI%20from%20spfx%3A%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fgithub.com%2FSharePoint%2Fsp-dev-fx-webparts%2Ftree%2Fmaster%2Fsamples%2Freact-aad-webapi%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fgithub.com%2FSharePoint%2Fsp-dev-fx-webparts%2Ftree%2Fmaster%2Fsamples%2Freact-aad-webapi%3C%2FA%3E%3C%2FLINGO-BODY%3E
Highlighted
MVP

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.

4 Replies
Highlighted
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
Highlighted

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.

Highlighted

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.

 

Highlighted

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.