Connecting SharePoint Online with BCS

Business Connectivity Services (BCS) is a brand new way for SharePoint users to seamlessly access and integrate data from any application or databases within SharePoint 2010. In this blog, we explore how to connect BCS in SharePoint Online.

SharePoint Online, lets you create BCS connections to data sources that are outside the SharePoint site, like SQL Azure databases or Windows Communication Foundation (WCF) web services. Once you’ve created these connections, you can manage or edit BCS information in the SharePoint admin center.

Understanding BCS Permissions

SharePoint Online uses BCS along with Secure Store Services to access and retrieve data such as BDC Models from external data systems.
BCS has two types of permissions:

• Object Permissions
• Meta Data Store Permissions

These are shown in the figure below.

1. Understanding Object Permissions

Object Permissions apply only to a specific External System, BDC Model, or External Content Type (ECT).

2. Understanding Metadata Store Permissions

Metadata Store Permissions apply globally to the whole BCS store. This includes BDC Models, external systems, ECTs, methods, and methods instances that are defined for that external data system. You can set permissions on the metadata store to determine who can edit items and set permissions for the store.

FIGURE 3:SET METADATA STORE PERMISSIONS

The next section explains the various options to connect SharePoint Online to SQL Server On-Premises with BCS/SharePoint Apps using Hybrid Connection and WCF Services.

Exploring Ways to Connect SharePoint Online With Online Data Sources

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. Using Azure Hybrid Connection and BCS with Azure Web App hosting WCF endpoint, it is possible to expose on-premises SQL data to SharePoint Online and Cloud by External Content Types (ECTs) or SharePoint Hosted Apps.

Connecting Different Data Sources to SharePoint

This section presents two approaches by which BCS can connect on-premise 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

Connecting Azure Web App hosting WCF Service and External Lists

Using this approach, you can host an Azure Web App hosting WCF Service in Azure which will connect to the SQL Server on-premise using Azure Hybrid connection. After having the data exposed via Azure WCF Service, you can use SharePoint BCS Service to create an External Content Type using SharePoint Designer based on a Web Service.

FIGURE 4: CONNECTING SQL DATABASE (ON-PREMISE) TO MICROSOFT AZURE

Connecting Azure Web App hosting WCF Data Service and Hosted Apps

Using this approach you can 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 an external list. After the external list is created in the SharePoint App, you can use the App Hope page to display the content as needed.

FIGURE 5: CONNECTING AZURE WEB APP HOSTING WCF DATA SERVICE AND HOSTED APPS

Exploring Seven Steps to Connect Office 365/SharePoint Online with External Data

This section explains the seven steps needed to connect Office 365/SharePoint Online with External Data:

1. Selecting an appropriate Microsoft Plan

The first step is to ensure that you have an appropriate Office 365 plan. Microsoft Business Connectivity Services are only available in the Office 365 Enterprise E3 or E4 plans for business or, if you are in the education sector, you can use Education E2 licenses.

2. Considering the Limitations

Compared to the on-premises editions of SharePoint, there are still some limitations vis-a-vis cloud edition. These limitations are:
• No profile pages for external content types
• No offline availability or rich-client integration
• No search integration

Beyond these limitations, you can use BCS to connect to different data sources like WCF web services, O-Data sources and SQL Azure databases.

3. Creating a Security Store Application

The first step is to create a target application in the Secure Store Service. This is needed in order to map a group of users to a single, external data account that can act on their behalf. In SharePoint Online, the only group restricted credentials are possible. In on-premise versions, there are also individual and mappings available. For SQL Azure Databases, the credential type needs to be set to “Windows User Name” and “Windows Password.”

4. Setting Up Connection and External Content Type (ECT)

The easiest way to create a new external content type is to use SharePoint Designer 2013, which can be used to create external content types for Microsoft SQL Azure ta-bles, views or stored procedures. For this you just need to open it and connect it to your site collection using credentials having site collection admin permission. On the left-hand side is a menu item, “External Content Types.” The connection and all CRUD methods such as read list, read item, create, update and delete can be created from here.

5. Understanding the Read / Write Operations

The Read/Write operations are explained in detail below.

Read List Operation: The Read List operation is like a select statement without a where clause. It returns by default all columns and all rows from the selected table. Filters can be created allowing the results to be trimmed.

Read Item: The Read Item operation is created so that a specific row of data can be identified and used. Each table, view or stored procedure will require a column where-by each of its values is unique, such as a primary key.

Create Operation: The Create Operation is used by the external list when data is being created. The Create Operation contains the statement allowing data to be inserted into the table.

Update Operation: The Update operation provides the ability to make and submit changes back to the external data source via the external list and some Office applications such as Microsoft Excel.

Delete Operation: The Delete Operation provides the ability to select a specific row and delete it via the external list.

6. Creating Public Content-Type and Create List

The next step involved in this process is to create an external list that enables users to see and use the data. To do this, click “Create Lists & Forms” in the Ribbon. You can specify a name and the default operation for this list.

7. Granting Permissions

To make the newly created external content type available to business users, it is important to set the permissions in the Business Connectivity Service application. This is done through the SharePoint admin center. To do this,

Select Manage BDC Models and External Content Types.

Check the box next to the name of the content type you just created, and then click the Set Object Permissions button. In the dialog box that appears, select the checkboxes for all the permissions that the SharePoint Online users need.

Conclusion

This blog showcased some key steps to connect to Business Connectivity Services in SharePoint Online.
To know more on this topic, feel free to write to us at reachit@aqltech.com.
To check more on customized solutions for Office 365 do visit our website http://aqltech.com