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.
1. Azure Web App hosting WCF Service and External Lists
2. Azure Web App hosting WCF Data Service and Hosted Apps
Azure WCF Service Web App and External Lists
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
High-Level Steps:
1. Create a WCF Solution using Visual Studio
2. 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
3. Update Web.Config of the WCF service with required configuration for data calls
4. Create an Azure Web App
5. Publish the WCF Service to Azure Web App and get the single wsdl signature from the WCF service
6. Create an External Content Type using SharePoint Designer using the WSDL signature
7. Add GetItems and GetItem finder to ECT
8. Create an External List from ECT
Azure Web App hosting WCF Data Service and Hosted Apps
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.
High-Level Steps:
1. Create a WCF service project using Visual Studio
2. Install the EntityFramework Nuget package
3. Add a WCF data service file and implement EntityFrameworkDataService instead of DataService
4. Override the “InitializeService” method as below
5. Add an ADO.Net Entity Data Model project and configure it to fetch data from SQL Tables you want
6. Update Web.config with required configuration for data calls
7. Create an Azure Web App and enable SSL on it
8. Publish the WCF Service to Azure Web App
9. Next create a new SharePoint hosted app solution in Visual Studio
10. In the SharePoint hosted app solution, add an External Content type and select the Azure Web Application hosting the WCF data service as source
11. After the External Content type is created, then create an External List using ECT created above
12. The external list is now added to the Hosted app which can then be referenced in the app default page and app part
Hence 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.
Good One!
Super useful. I understand the second solution, but not sure in the first solution what that entails implementing “Use ADO.Net and WCF Service calls to fetch data using web methods”. Could you give a little more detail? thx