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

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.

SPOAzureBCSHybrid

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

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.

SPOAzureAppsHybrid

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 below method


public static void InitializeService(DataServiceConfiguration config)
{
// TODO: set rules to indicate which entity sets and service operations are visible, updatable, etc.
// Examples:
config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
// config.SetServiceOperationAccessRule("MyServiceOperation", ServiceOperationRights.All);
config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V3;
config.UseVerboseErrors = true;
}

view raw

Azure WCF Service

hosted with ❤ by GitHub

 

  1. Add an ADO.Net Entity Data Model project and configure it to fetch data from SQL Tables you want
  2. Update Web.config with required configuration for data calls
  3. Create an Azure Web App and enable SSL on it
  4. Publish the WCF Service to Azure Web App
  5. Next create a new SharePoint hosted app solution in Visual Studio.
  6. In the SharePoint hosted app solution, add an External Content type and select the Azure Web Application hosting the WCF data service as source
  7. After the External Content type is created, then create an External List using ECT created above
  8. 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.

7 Comments

  1. Asish,
    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?
    Thanks,
    Srikanth

    Like

    Reply

    1. 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.

      Thanks,
      Asish

      Like

      Reply

Leave a comment