Connect SharePoint Online and SQL Server On-Premises with BCS/SharePoint Apps using Hybrid Connection and WCF Services

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

Azure Web App hosting WCF Data Service and Hosted Apps
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 “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.

Resolving “User not found” issue while assigning permissions using SharePoint CSOM

I was recently working on a SharePoint Online project where we were trying to automate library creation and provide required permissions on those libraries. We had an issue while modifying permissions with CSOM code on SharePoint libraries when the Created By user had left the company.

In this post I will outline the cause and the resolution as there was no online reference for resolving this error.

Issue: The CSOM code was throwing an error “User not found” even when creating a User object from web.EnsureUser() method.

Cause: The User object returned by web.EnsureUser() method was empty but not null and hence couldn’t be instantiated while adding after breaking permissions.

Resolution: The resolution to this issue was to explicitly load of the user object, then catch the exception while loading, and set a flag to false which could be later be checked to prevent the add method from erroring out. Yeah, this is a roundabout way of overcoming the issue but it works. Hopefully it will save you some hours.

Below is the code that could be used to do that.

Use Azure Hybrid Connections to get on-premises data from SQL to SharePoint Online

Azure Hybrid Connections are an easier and less complicated way to connect cloud applications with on-premises SQL data. This provides great extensibility options for SharePoint Online such as,

  1. Provider Hosted Apps hosted in Azure
  2. Business Data Connectivity using WCF services hosted in Azure
  3. SharePoint Hosted Apps using BCS external sources.

In this blog, I will illustrate the steps to configure Azure Hybrid Connections. In a nutshell, the diagram below outlines the data flow in Hybrid connections.

AzureHybridConnection1_Asish

Firstly, in the on-premises SQL server, if you have a named instance then assign a static port to it and expose it through the firewall. If SQL is installed on the default instance, then make sure 1433 is exposed outside the firewall.

Next, log into the Azure Portal and create a Resource Group, add an Azure Web App, and then add a Hybrid Connection from Networking section
(Azure Web App -> Networking -> Configure Hybrid Connection)

AzureHybridConnection2_Asish

Note:Hybrid connections can also be added by other resources such as Azure Functions or other apps that can be tied to an App Service plan.
Note:The number of Hybrid Connections are limited by the type of App Service Plan.  A brief table of allowed connections is below. It is important to note that the Free App Service Plan doesn’t have any Hybrid Connections. It is shown in the table below
Pricing Plan Number of hybrid connections usable in the plan
Basic 5
Standard 25
Premium 200
Isolated 200

Next, add a New Hybrid Connection. In Endpoint Host, enter the fully qualified name of your SQL server along with domain. In the port field as in the below screenshot, provide the details of the SQL server port the instance is exposed at.

Note: No need to qualify the details of instance as server\instance in the endpoint host field as the application code will have to specify the connection details in it. The Hybrid connection will only need to just know the endpoint.
Note: You could also select existing hybrid connections from other resource groups.

AzureHybridConnection3_Asish

After the Hybrid connection is created, it will show up in the Azure Portal as in below screenshot

AzureHybridConnection4_Asish

Next, download the Connection Manager using Download Connection Manager. It is basically a download with pre-configured Azure subscription details which, when installed in an on-premises system environment (preferably in the same Data center as the SQL Server), acts as a listener to Azure Web App requests.

After installing the Hybrid Connection UI manager, connect to the Azure Subscription account to find the available hybrid connections. After selecting the connection, if the listener can connect to SQL it would show as Connection Successful.

AzureHybridConnection5_Asish

After the connection is successful, in the Azure Portal, the number of listeners will show as 1 and connection status to Connected.

In this blog, we saw how we could create Azure Hybrid connections to connect an on premises SQL with an Azure App Service. In the next blog, we will discuss the steps to consume this connection and connect SPO with the SQL data sources.

Implementing Bootstrap and Font-awesome in SharePoint Framework solutions using React

Responsive Design has been the biggest driving factor for SharePoint framework (SPFx) solutions. In a recent SPFx project for a customer, we developed a component using React, Bootstrap and Font-awesome icons for a responsive look and feel. While building the UI piece, we encountered many issues during the initial set up, so I am writing this blog with detail steps for future reference. One of the key fixes mentioned in this post, is for the WOFF2 font-type file which is a component in font-awesome and bootstrap.

In this blog post, I will not be detailing the technical implementation (business logic and functionality) just focusing on the UI implementation. The following steps outline how to configure SPFx React client side web parts to use Bootstrap and Font-awesome CSS styles

Steps:

  1. Create a SharePoint Framework project using Yeoman. Refer this link from Microsoft docs for reference.
  2. Next, install JQuery, Bootstrap and Font-awesome using npm so that it can be available from within node_modules
    npm install jquery --save
    npm install @types/jquery --save-dev
    npm install bootstrap --save
    npm install @types/bootstrap --save-dev
    npm install jquery --save
    npm install @types/jquery --save-dev
    

    Check the node_modules folder to make sure they got installed successfully

  3. Now locate config.json file in config folder and add the entry below for third party JS library references.
    "externals": {
          "jquery": {
          "path": "node_modules/jquery/dist/jquery.min.js",
          "globalName": "jQuery"
        },
        "bootstrap": {
          "path": "node_modules/bootstrap/dist/js/bootstrap.min.js",
          "globalName": "bootstrap"
        }

    Then reference them in the .ts file in the src folder using import

    import * as jQuery from "jquery";
    import * as bootstrap from "bootstrap";
    
  4. For CSS reference, we can either refer to the public CDN links using SPComponentloader.loadCss() or else refer to the local version as below in the .tsx file
    Note: Don’t use ‘require’ for js scripts as they are already imported in above step. If included again it will cause a component load error.

    require('../../../../node_modules/bootstrap/dist/css/bootstrap.css');
    require('../../../../node_modules/bootstrap/dist/css/bootstrap.min.css');
    require('../../../../node_modules/bootstrap/dist/css/bootstrap-theme.css');
    require('../../../../node_modules/bootstrap/dist/css/bootstrap-theme.min.css');
    require('../../../../node_modules/font-awesome/css/font-awesome.css');
    require('../../../../node_modules/font-awesome/css/font-awesome.min.css');
    
  5. When using React, copy the html to the .tsx file in the components folder. If you want to use the HTML CSS classes as-is and not the SASS way, refer to this blog post. For image references, here is a good post to refer.
    For anyone new to React as me, few tips below for styling:
    1. Use className instead of HTML class attribute
    2. In order to use inline styles, use style={{style attributes}} or define an object, since everything in JSX are elements
  6. When ready, use gulp serve to launch your solution in local workbench.
    Important: If you’re using custom icons or fonts from the above CSS libraries, you will receive Typescript errors saying that loader module was not found for WOFF2 font type. Here, you will need to push the custom loader for WOFF2 font type through gulpfile.js as below.First install url-loader from npm.

     npm install url-loader --save-dev

    Then modify gulpfile.js at the root directory to load the custom loader.

    build.configureWebpack.mergeConfig({ 
      additionalConfiguration: (generatedConfiguration) => { 
        generatedConfiguration.module.loaders.push([ 
          { test: /\.woff2(\?v=[0-9]\.[0-9]\.[0-9])?$/, loader: 'url-loader', query: { limit: 10000, mimetype: 'application/font-woff2'} } 
        ]); 
        return generatedConfiguration; 
      } 
    });
    

    Now gulp serve your solution and it should work fine.

You might still face CSS issues in the solution as the referring CSS doesn’t exactly match the HTML-CSS implementation. To resolve any conflicts, use CSS Override (!important) wherever necessary.

In this post I have shown how we can configure bootstrap and font-awesome third party CSS files to work with SharePoint Framework solutions while leveraging React Framework.