Business Connectivity Services in SharePoint Online

In the last couple of years, organizations have started using Business Connectivity Services (BCS) to integrate data from external data sources into SharePoint. It was available initially with Microsoft Office SharePoint Server (MOSS) 2007 and was called Business Data Catalog (BDC). With SharePoint 2013, it is not only possible to read information, but also to create, delete, or update content.

Today, companies are moving their SharePoint content from on premises to Office 365 and SharePoint Online with the help of third-party tools. In most of these projects, a common question arises: How do you handle SharePoint external lists connected to data using BDC?

SHAREPOINT ONLINE]The good news is that SharePoint Online also supports BCS – but there are still some limitations compared to the on-premises version, and you will need an Office 365 Enterprise E3 or E4 plan to use this feature.

Limitations include:
• There are no profile pages for external content types
• There is no offline availability or rich client integration
• There is no search integration.

Despite these limitations, you can use BCS to connect to different data sources like Windows Communication Foundation (WCF) web services, Open Data Protocol (O-Data) sources and Microsoft SQL Azure databases. As long as the data is provided by one of the supported data sources, SharePoint Online will be able to obtain the needed information. The process of creating an external content type follows the same method of how it is done on-premises. Follow the steps below to properly implement BCS in your SharePoint Online environment.

Create a Secure Store Mapping

The first step is creating a target application in the Secure Store Service. This is needed to be able to map a group of users to a single, external data account that can act on their behalf. In SharePoint Online, only group-restricted credentials are possible – and in an on-premises environment, there are additional individual and mappings available.

For SQL Azure databases, the credential type needs to be set to “Windows User Name” and “Windows Password.” After creating the target application, you can enter the credentials for the SQL Azure account that you want to use for connecting.

Create a Connection, an External Content Type, and the CRUD Operations

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 SQL Azure tables, views, or stored procedures. You simply need to open it and connect to your site collection using credentials that have administrator permissions.

Within the external content types menu item, you can create the connection and utilize all Create, Read, Update, Delete (CRUD) model methods.

CONTENT TYPE

Next, you need to specify server name, database name, and an optional display name. As you authenticate, use “connect with impersonated custom identity” and enter the Application ID created before. This passes the credentials we specified to SQL Azure each time when a SharePoint user accesses this content. Now when the connection has been created the database appears, it´s possible to drill down to the tables and views. By right-clicking a table or view, you can create a single operation such as “read list” or perform all operations at once.

Save the Content Type and Create an External List

By now, most of the work is done. By clicking “save”, the external content type is saved back to SharePoint. The last step involved in this process is creating an external list that enables users to see and use the data. This is done by clicking “create lists and forms” in the ribbon.
Now the business users are able to create, read, update or delete items – depending on the permissions and created actions – in the SQL Azure database without leaving SharePoint. Instead of using an external list, it is also possible to publish the data using a business data web part.
After migrating SharePoint from an on-premises system to SharePoint Online, all external content types and lists are not working anymore because of missing BCS definitions. But if these connections are still needed, there are two possible ways to get this done:

AZURE 1. Migrate data to SQL Azure: SharePoint Online can easily connect to a SQL Azure Database, and therefore use this as an external content type to publish the information in an external list or a BDC web part.

2. Publish the line of business data as WCF web service: Because of organizational and legal issues, it is not always possible to migrate all data to SQL Azure. To connect this data, you can publish it using a WCF Web Service. SharePoint Online BCS is also able to connect to these content sources. This enables companies to continue using line of business data after moving their on-premises environments to Office 365. Because there is some amount of manual work and planning involved, customers should include consulting expenses into the project budget.

By utilizing BCS on its own, it’s still possible to access external content from within SharePoint without duplicating content like it’s done with replication. This gives the user the option to access content from databases, Web services, or O-Data sources in real-time. There is no need to log on to additional systems and remember large amounts of credentials, and thus SharePoint becomes a single point of truth.

AvePoint offers integrated solutions to migrate enterprise content from legacy platforms to Office 365 – SharePoint Online. Organizations can take the content they want, and leave the content they don’t and accelerate the adoption of Office 365 with the robust tools of the AvePoint DocAve Platform.

Starting Kit To Share Point In Microsoft Azure

About the author AvePoint: 

Mario Spies, Senior Strategic Consultant, AvePoint

Ave Point Resized LogoAvePoint is the established leader in enterprise-class big data management, governance, and compliance software solutions for next-generation social collaboration platforms. Founded in 2001 and headquartered in Jersey City, NJ, AvePoint serves over 13,000 organizations worldwide. AvePoint is privately held and backed by Goldman Sachs and Summit Partners.www.avepoint.com. 

Check out last years European SharePoint Conference video:

European SharePoint Conference 2015 takes places in Stockholm Sweeden from 9-12 November 2015. View Programme>>

Share this on...

Rate this Post:

Share:

Topics:

General