SharePoint users have long known how powerful it is to manage and report on data in SharePoint. The SharePoint UI provides a flexible interface for editing and creating records. Reporting on this data is what makes SharePoint truly powerful. But what about data that lives outside of SharePoint? Until recently data that resided in other SQL Server databases, Oracle databases, or cloud applications like QuickBooks could not be accessed in SharePoint. Business Connectivity Services (BCS) allows external data to be used in SharePoint just as any other list would be used. BCS functionality allows you to create a SharePoint portal that truly is the hub of all your data.
BCS began in SharePoint 2007 as a feature called the Business Data Catalog. SharePoint 2010 rebranded this functionality as Business Connectivity Services. With SharePoint 2013 BCS has continued to be refined with several key improvements. One of the biggest, being the ability to connect to external APIs using the Open Data Protocol, aka OData. Another important feature is the ability to create alerts and subscriptions based on external data sources.
BCS Data Sources
SharePoint 2013 allows for BCS to connect to several types of data sources. These available data sources should cover a majority of use cases. The current options for data sources in SharePoint 2013 are:
- Native SQL Server – A BCS External Content Type can be configured with a direct connection to SQL Server. This can be another local SQL Server, an external SQL Server accessible via VPN, or SQL Server hosted in a public cloud like Azure or AWS.
- OData Services – Any data accessible via OData can be connected to by BCS and treated like a List. This feature, which is new to SharePoint 2013, is almost at simple connecting to a direct SQL Server database. At tekmortar our OData Integration Services allow BCS to connect to client data residing in applications like QuickBooks and SalesForce.
- WCF and RESTful Services – External Lists can be defined that hook into either .NET WCF Services or using any RESTful Service. Both of these will require a medium level of development to wire up the service connectivity. But it allows for connectivity to a vast amount of heterogeneous data sources.
- Custom .NET Interfaces – Custom .NET Libraries can be written to connect to any form of data available. The downside is this method requires heavy coding and customization and is very brittle.
There are two core security concepts in BCS. The first being the SharePoint Secure Store Service, and the second being External List security.
The Secure Store Service is used throughout SharePoint to provide secure storage of authentication credentials. The service is used by PowerPivot and Excel Services, but it is a critical component of BCS.