SharePoint Online cannot directly connect to on-premises data sources such as SQL Server. A recommended approach is to use Hybrid with SharePoint 2013/2016 but adds an overhead of infrastructure and maintenance costs. Hence to overcome it, I am going to describe in this blog how to use the Azure PaaS workloads and connect to on-premises data sources using BCS.
Using Azure Hybrid Connection (refer this post) and BCS with Azure Web App hosting WCF endpoint, we can now expose on-premises SQL data to SharePoint Online and Cloud by external content types (ECTs) or SharePoint Hosted Apps.
Below are two approaches by which BCS can connect these data sources to SharePoint
- Azure Web App hosting WCF Service and External Lists
- Azure Web App hosting WCF Data Service and Hosted Apps
1. Azure Web App hosting WCF Service and External Lists
Approach Overview: In this approach, we would host a Azure Web App hosting WCF Service in Azure which will connect to the SQL Server On prem using Azure Hybrid connection. After having the data exposed via Azure WCF Service, we could use SharePoint BCS Service to create an External Content Type using SharePoint Designer based on a Web Service. For more information to create External Content type using WCF services, please read this article.
Pros: The advantage of using this approach is the reusability of External Content Types (ECT). ECTs can be used across multiple lists and sites in the same site collection. ECTs can also be used for complex associations across multiple types of data.
Cons: Some shortcomings of this approach are
- Dependency on pass through authentication for users and/or implement custom authentication to authenticate with WCF by passing SQL authentication
- Added development effort because of WCF build and hosting
- Create a WCF Solution using Visual Studio
- Use ADO.Net and WCF Service calls to fetch data using web methods. Implement at least two web methods – one to return all items and one to return a specific item
- Update Web.Config of the WCF service with required configuration for data calls
- Create an Azure Web App
- Publish the WCF Service to Azure Web App and get the single wsdl signature from the WCF service
- Create an External Content Type using SharePoint Designer using the WSDL signature
- Add GetItems and GetItem finder to ECT
- Create an External List from ECT
2. Azure Web App hosting WCF Data Service and Hosted Apps
Approach Overview: In this approach, we would a host a WCF Data service in Azure Web App and then create a SharePoint hosted app that will consume the fields in the WCF Data service to create a external list. After the external list is created in the SharePoint App, we would use the App Hope page to display the content as needed.
Pros: The advantages of using a WCF Data Service is that the OData method maps directly to the schema of the SQL table which makes it easy to build and maintain. Additionally, using SharePoint hosted apps isolates the CRUD operations from the Host Web decreasing the overhead of external content types and external lists.
Cons: The disadvantage of using this approach is that the data is scoped within the app and cannot be exposed to Host Web components making interaction limited to Web App only. There is a customization requirement to expose and operate on this data in the App Web.
- Create a WCF service project using Visual Studio
- Install the EntityFramework Nuget package
- Add a WCF data service file and implement EntityFrameworkDataService instead of DataService.
- Override the below method
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
|public static void InitializeService(DataServiceConfiguration config)|
|// TODO: set rules to indicate which entity sets and service operations are visible, updatable, etc.|
|// config.SetServiceOperationAccessRule("MyServiceOperation", ServiceOperationRights.All);|
|config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V3;|
|config.UseVerboseErrors = true;|
- Add an ADO.Net Entity Data Model project and configure it to fetch data from SQL Tables you want
- Update Web.config with required configuration for data calls
- Create an Azure Web App and enable SSL on it
- Publish the WCF Service to Azure Web App
- Next create a new SharePoint hosted app solution in Visual Studio.
- In the SharePoint hosted app solution, add an External Content type and select the Azure Web Application hosting the WCF data service as source
- After the External Content type is created, then create an External List using ECT created above
- The external list is now added to the Hosted app which can then be referenced in the app default page and app part.
In this blog, we have seen the two choices to host BCS connectivity services via Azure PaaS workloads, advantages and disadvantages of each and broad level steps to configure them.
Thanks for your post. I have followed the first approach
“1. Azure Web App hosting WCF Service and External Lists” but I couldn’t able to browse External list on SharePoint online I mighe be missing something. Can you please tell me where is the link betweeen HybridConnectionmanager and WCF service hosted on Azure?
Hi Srikanth, The Hybrid connection manager will need to be hosted on a on-premises system (could be any system and doesn’t need to be on the SQL server) which has a trusted connectivity to the SQL Server. In case you are looking for networking connectivity set-up in Azure web app, it is located in the Networking option.
The idea is that you are opening a tunnel between your on premises network to cloud via the specific port calls to SQL via an intermediate server.
Let me know if that answers your query.
Why not just use the sharepoint sql server connector?
Because that only works for SQL in Azure, no to on-prem.
Yeah, as it needs an exposed connector or use an internet based DNS name but it is risky
Ashish, Can I use sharepoint online in office 365 to connect to an OData Service.
Yes you can, depending on how you plan to access and if the OData service is exposed externally over the web. If not, you might want to create a BCF service to host a connector, more documentation here – https://docs.microsoft.com/en-us/sharepoint/dev/general-development/external-content-types-in-sharepoint