Business Connectivity Services
in SharePoint 2010
WHAT’S IN THIS CHAPTER?
An overview of BCS architecture

Creating a BCS solution

Service applications and BCS

Design considerations for BCS solutions

Upgrading the Business Data Catalog to BCS

Business Connectivity Services (BCS) represent a comprehensive set of capabilities that
integrate and connect SharePointServer 2010 applications with data that resides in exter-
nal systems. BCS, which is based on a set of services and features included in SharePoint
Foundation 2010, provides out-of-the-box support that simplifi es the development of solu-
tions that use external data and services. BCS is a major enhancement to its predecessor in
SharePoint 2007, the Business Data Catalog (BDC). Using BCS, you can design and build
solutions that extend SharePoint collaboration capabilities to include external business data
and the processes that are associated with that data.
BCS uses a new concept in SharePoint 2010 called external content types (ECTs), which should
not be confused with the enterprise content types mentioned in other chapters. ECTs are a content
type that is based on external data. Just as with any content type, they are associated with lists. In
this case, the list is called an external list, and it provides the viewing and storage mechanism for
the external data.
BCS solutions can be created using SharePoint Designer 2010 or Visual Studio 2010. Which tool
is best depends on both the skill set of the creator and the features and complexity requirements
of the solution. For example, you can create a BCS solution that uses Outlook 2010 to manage
24
724


CHAPTER 24 BUsiNess coNNectivity services iN sharePoiNt 2010
customer information that resides in a CRM system. This solution would use an external content type
created with SharePoint Designer 2010, and an external list created using the web browser. The data
in the external list can be taken offline into Outlook as a set of contacts. Updates to the contact list
can be made in Outlook and synchronized with the external data source. Many types of BCS solutions
can be created without writing any .NET code, which fosters the development of such solutions and
encourages those without professional development skills to get involved. BCS also includes a runtime
environment in which solutions that include external data are managed and executed.
This chapter provides a BCS introduction and overview in order to familiarize you with its capabilities.
Because these capabilities are both wide and deep, expect to see entire books dedicated to BCS coming
along soon.
CAPABILITY OVERVIEW
BCS is an enhancement to the functionality that was provided in the SharePoint 2007 Business
Data Catalog. The BDC was a first-generation technology for connecting SharePoint to external
data. Before looking at the specific capabilities of the SharePoint 2010 BCS, let’s review some of
the gaps in the BDC:
The BDC did not provide the capability to truly integrate external data into SharePoint 2007.

BDC data could be displayed using four out-of-the-box Web Parts and by adding a column
to a list with the Business Data data type. This provided a copy of the specific data.
The BDC data was essentially read-only, unless you wanted to write the custom .NET code

necessary to perform the write-back capability. External data was displayed using a number
of different out-of-the-box Web Parts, but there was no direct provision for updating the
back-end data system.
There was no support for integrating external data with Office desktop client applications like

Outlook, Word, or Excel. The BDC provided integration with SharePoint Server 2007. To
provide client integration, you would need to create a web service that would access the BDC

Object Model and then the client would interact with the web service to surface the data. This
approach would work, but it required a lot of custom .NET development, and there was no
capability to manipulate the data once in the client, such as a client object model, which also
had to be developed.
BDC content could not be taken offline or used in a disconnected fashion.

The BDC did not support streaming BLOB content. This caused problems when the BDC

was used to index content that was contained in SQL Server and stored as BLOBs.
The BCS represents a second-generation technology for connecting to external systems, and specifically
addresses all of the aforementioned gaps:
BCS uses content types and external lists to integrate external data in SharePoint 2010.

SharePoint 2010 features and services that utilize list data can utilize the external list data
as well.
Capability Overview

725
BCS provides complete CRUD (create, read, update, and delete) operation capability on data

returned from external systems.
BCS provides direct integration of external data with client applications like Outlook 2010,

Word 2010, and SharePoint Workspace 2010. This integration does not require any custom
programming.
External list data can be taken offl ine in Outlook 2010 and SharePoint Workspace 2010. The

external data source can be updated with changes made in the offl ine environment, and in
general the two environments can remain synchronized.
BCS provides the capability to index and search content stored in BLOBs. Specifi cally, this


requires defi ning a method in the XML access model, defi ning a content source with the ECT,
and having the necessary IFilters installed for the specifi c fi le types of interest.
BCS provides a more extensive set of authentication mechanisms for connecting to the exter-

nal systems and retrieving data. This also includes greater control over access to the external
data once it has been retrieved.
BCS capability is provided by SharePoint Foundation 2010, whose predecessor is Windows SharePoint
Services version 3, and by SharePoint Server 2010. Table 24-1 summarizes which BCS functionality
is available in SharePoint Foundation and SharePoint Server 2010. SharePoint Server 2010 function-
ality also includes that provided by SharePoint Foundation. Details about each of the features are
discussed later in the chapter.
Just as you saw in SharePoint 2007, there are two different types of client access
licenses (CALs) for SharePoint Server 2010: Standard and Enterprise. These
different licenses entitle the user to different types of BCS features. Table 24-1
and this chapter in general do not discuss the licensing requirements for utilizing
these features. Administrators should consult a licensing specialist for details.
TABLE 241: BCS Functionality for SharePoint Foundation and Server 2010
PLATFORM FUNCTIONALITY
SharePoint Foundation 2010 Business Data Connectivity Service — Manages and provides the
storage and retrieval capability.
External lists and External data columns — Enables the display of
external data.
BDC connectors — The actual binary logic that communicates with
the data store.
Custom connectors can be created and made available using the
Connector Framework.
continues
726


CHAPTER 24 BUsiNess coNNectivity services iN sharePoiNt 2010
PLATFORM FUNCTIONALITY
SharePoint Server 2010 Search Capability — To index and access external data.
Secure Store Service — Stores access credentials to the external data.
External Data Web Parts — Display external data.
User Profiles — For managing and displaying external data.
Workflow Capability — For using external data.
Rich Client Integration — Integrates external data with Oce 2010
client applications like Outlook and Word.
ARCHITECTURE
The BCS architecture consists of service applications that manage and provide the connectivity to
external systems; client and server runtime applications, which deliver the capability to the server
and Office desktop clients; external content types, which provide the XML description that governs
how to access the store and what to retrieve; and the built-in connectors, which contain the actual
programming logic that communicates with the data store. Each of these architectural components
is discussed in the following sections.
Service Applications
BCS utilizes two service applications, just as you’ve seen other SharePoint 2010 functionality use
the service application architecture. These services are called the Business Data Connectivity Service
(BDCS) and the Secure Store Service (SSS).
Business Data Connectivity Service
The BDCS provides a means for storing, accessing, and utilizing external data. Specifically, this
includes external content types (ECTs) and related objects. This service provides connectivity
capability to several different types of external systems, and there is out-of-the-box support for
the following data sources:
Databases

Web services

.NET Framework assemblies


Custom connectors

This new service can be compared to the Business Data Catalog in SharePoint Server 2007, but there
are some big differences. One of the differences is that this service is provided by a new service archi-
tecture, which is available in SharePoint Foundation 2010. Previously, in order to use the Business
Data Catalog, you had to have SharePoint Server 2007 installed; this functionality was not available
TABLE 241
(continued)
Architecture

727
in Windows SharePoint Services version 3. From a licensing perspective, your users had to have the
Enterprise client access license.
In SharePoint 2010, BCS functionality is provided by SharePoint Foundation and SharePoint Server
2010. The specific functionality provided by each was discussed earlier in the “Capability Overview”
section. Note that this service also provides support for custom connectors, which the BDC did not.
These connectors are discussed later in the section “Connectors and the Connector Framework.”
BDCS is implemented as a service application in SharePoint Foundation 2010. Like other SharePoint
2010 services discussed in previous chapters, there is an associated service application and proxy called
Business Data Connectivity. You can view this service application and proxy from the Manage Service
Application web page in Central Administration. Multiple instances of the BDCS can exist in the same
farm, each with a unique set of administrators if necessary, and an instance of the BDCS can be shared
across farms. The BDCS uses ECTs to define the connectivity and information to be retrieved.
ECTs define quite a bit of information. They are discussed in their own section a bit later, but the
following is a brief summary of their contents:
A named set of fields in the external data. This could include things like Customer, Products,

and so on.
The specific CRUD operations that will be used for interacting with the external data system.


Connectivity information that BCS solutions will use to connect the external content type to

the external system.
External content types are stored in a dedicated BDCS database, which by default is called Bdc_
Service_DB and can be viewed using SQL Server Management Studio.
Secure Store Service
As you saw in Chapter 20 with PerformancePoint Services, the SSS securely stores credentials for
external systems and then associates those credential sets with identities of individuals or groups.
The SSS replaces the Single Sign-on capability that was available in SharePoint Server 2007. This
service enables the BCS solution to authenticate users and groups on external data sources. It also
enables the same user or group to have different user accounts and credentials for the different
external systems compared to their enterprise log-in credentials.
For example, suppose John Doe logs into SharePoint Server 2010 using his enterprise username and
password, and he uses a different set of credentials for logging into the corporate financial system.
Using SSS, Mr. Doe’s financial system access credentials can be stored with his user profile. The ben-
efit of this is that when Mr. Doe uses a BCS solution from within SharePoint Server 2010 to access
data from the financial system, SharePoint server obtains his credentials from the SSS and provides
a single sign-on mechanism. Thus, this eliminates the need for Mr. Doe to manually log into the
financial application. You can also configure the SSS so that multiple users can access an external
system by using a single set of credentials.
Another potential issue that the SSS can solve when accessing external systems is the double hop
problem. This occurs because a SharePoint web page or Web Part tries to access resources that are
located on a server other than the web server. You can use SSS to map the user’s account with his
728

CHAPTER 24 BUsiNess coNNectivity services iN sharePoiNt 2010
credentials for the external store. In the terminology of the SSS, external data stores are referred to
as target applications. A target application needs to be confi gured in the SSS in order for the BCS
solution to connect to and utilize the data.

SSS credential sets are stored in a dedicated, secure database, which by default is called
Secure_Store_Service_DB. The BDCS data and the SSS data are also cached on client comput-
ers that are using the BCS solution. For more information, see Chapter 20 and the discussion on
PerformancePoint Services and SSS.
For more information about the double hop problem, see http://support
.microsoft.com/default.aspx?scid=kb;en-us;329986
.
Connectors and the Connector Framework
BDCS provides connectivity to the external data. It does this by utilizing ECTs and built-in connec-
tors. The ECTs provide the metadata description of the connectivity information, and the connector
provides the actual binary logic to communicate with the data store. External data that resides in a
database such as SQL Server, Oracle, etc. is accessed by using the appropriate ADO.NET database
provider. Data that doesn’t reside in a database but is accessible via web services is accessed using the
Windows Communication Foundation connector for web services. There is also a .NET Framework
Assembly connector for accessing .NET assemblies. All of these connectors are available out-of-the-
box and don’t require any custom programming.
For those data stores that don’t comply with either an ADO.NET provider or a web service, or access
via the .NET assembly connector, a custom connector can be built. The custom connector would plug
into the connector framework, and then it could be utilized by the BDCS to provide access to the data
source. The process for creating a custom connector is beyond the scope of this chapter and this book,
but interested readers should begin by understanding the differences between the custom connector
and the .NET assembly connector. Learn more about this at
/>library/ee554911(v=office.14).aspx
.
BDC Client and Server Runtime
Different runtimes provide the necessary capability to access and utilize external data. One run-
time exists on the SharePoint server, and the other exists on the client computer. Together, they
deliver the server functionality to the desktop client. These different runtimes are discussed in the
following sections.
Server Runtime

The Business Data Connectivity Server runtime understands how to reach into the back-end store and
connect to data based on the external content types defi ned within the content type store. The server
runtime exists on the SharePoint 2010 web front-end servers, and it utilizes two shared services: the
BDCS and the SSS, discussed previously. It uses information from these services to access external
Architecture

729
systems and execute operations on the external systems for access by web browsers. The server run-
time provides the connectivity to external sources such as SQL Server and other relational databases,
web services, and custom data connectors. SharePoint websites display external data in the web
browser using Business Data Web Parts and SharePoint external lists.
The runtime also contains a data cache called the metadata cache, which provides caching of the
runtime BDCS data. This data can be encrypted for additional security if necessary. The runtime
also provides the mechanism for clients to synchronize with the BDCS data and SSS data.
Client Runtimes
Two different runtimes provide BCS functionality on the client: the Business Data Connectivity Client
Runtime (client runtime), and the Office Integration Client Runtime (integration runtime). These run-
times are installed along with the installation of SharePoint Workspace 2010, Word 2010, and Outlook
2010 desktop client applications. The integration runtime integrates with the client runtime to surface
external data and functionality to the desktop client. The client runtime is a connector between the
server runtime and the integration runtime.
The client runtime provides integration with the server runtime. This connectivity between the client
and the server occurs via the client-side cache, which contains the BDCS data and SSS data to connect
to and execute operations on external systems for access by supported desktop clients. The client-side
cache is periodically refreshed to ensure that data is synchronized with the BDCS and SSS data. This
cache provides the capability to take solutions offline and then update the server when reconnected.
From a technology point of view, the client-side cache leverages the SQL Server Compact database as
its durable store. This should provide a strong sense of reliability to developers because it leverages
proven and widely adopted technologies.
The SSS is also accessible from client applications through the Client Secure Store Service, which

enables end users to configure their client mappings in the credential database. The client runtime
also supports connecting to SQL Server and other relational databases, web services, and custom
data connectors.
External Content Types
External content types (ECTs) are a critical component of the BCS architecture and functionality.
Recall that content types were introduced in SharePoint 2007. Content types provide a mechanism
for standardizing and reusing metadata information for a specific type of content, as well as asso-
ciating such functions as workflows and policies with the content. We will not review content types
in this chapter, but for those who may need a refresher of the types of capabilities available to con-
tent types, you can browse to the Site Content Types web page from the Site Settings page of your
SharePoint website, and select a content type such as Document. On the Document content type
web page, you can review all the different aspects of content types.
What is important to us in this chapter are ECTs, which are an extension of the content type concept to
external data. Specifically, they contain connectivity information for accessing external data, specifica-
tions for the type of data to be accessed, and actions that you want to apply to external data. An ECT
is defined using XML markup. This is very similar to how an entity was defined with the Business Data
Catalog in SharePoint 2007, so you may hear and read the terms ECT and entity used interchangeably,
730

CHAPTER 24 BUsiNess coNNectivity services iN sharePoiNt 2010
but the ECT has additional functionality, as you will see. In general, you can consider an ECT a data
source. Using the ECT as a data source, you can view the external data on the server using an external
list, an external data column, and external data Web Parts. These different approaches to viewing ECT
data in SharePoint 2010 are described in Table 24-2.
TABLE 242: Viewing External Content Type Data in SharePoint 2010
APPROACH DESCRIPTION
External List An external list is a new type of SharePoint list that displays data
using the ECT as the data source. This provides direct integration of
the external data in SharePoint so it can be manipulated using CRUD
operations just like any other SharePoint list data, and the list and

external data can be kept synchronized. External lists can be taken
oine using Outlook 2010 and SharePoint Workspace 2010. Unlike
standard SharePoint lists, external list data is not stored in the con-
tent database, but remains in the external data store. Also, external
lists do not have all the standard features of regular lists, such as ver-
sioning, check-in, check-out, workflows, and content type association.
External Data Column The Business Data list column type introduced in SharePoint 2007
has been renamed the External Data Column in SharePoint 2010.
This adds a single column of information from the ECT to standard
SharePoint lists. Unlike the external list, External Data Columns pro-
vide all the other features of standard SharePoint 2010 lists.
External Data Web Parts Just as SharePoint 2007 provided Web Parts that displayed data
from the BDC, SharePoint 2010 provides five Web Parts that do not
require any custom programming to use and display BCS data on
SharePoint 2010 web pages: External Data List, External Data Item,
External Data Item Builder, External Data Related List, and External
Data Connectivity Filter Web Part. These Web Parts are read-only and
do not provide write-back capability to the external data.
Security
BCS provides extensive authentication and authorization capability for accessing external systems
and consuming data. BCS security will govern access to external data in a wide variety of scenarios,
which can be categorized by the tool or application desiring access to the data:
Web browser

Office client

Custom application

A comprehensive discussion of BCS security is beyond the scope of this one chapter, as it would
involve the overall security architecture, both client and server, which authentication modes are

Creating a BCS Solution

731
available for external content types, the type and options for configuring permissions on objects,
and the specific steps to configuring authentication and authorization. Interested administrators are
encouraged to review the information referenced in Table 24-3. An understanding of this informa-
tion is not required to get starting using BCS, and readers can complete the hands-on exercise in the
section “Creating a BCS Solution” without this knowledge.
TABLE 243: BCS Security Topics and References
TOPIC REFERENCE
Planning SharePoint 2010
Authentication
/>cc262350(office.14).aspx
BCS Security Overview
/>ee661743(office.14).aspx.
BCS Authentication
/>ms566523(office.14).aspx
BCS Authorization
/>ms497953(office.14).aspx
BCS Permissions
/>permissions-in-business-connectivity-services.aspx
CREATING A BCS SOLUTION
In this section, you’ll create an example solution that illustrates the concepts of external content types
and external lists. BCS solutions are created using SharePoint Designer 2010 and Visual Studio 2010.
These solutions can be packaged as a Visual Studio Tools for Office (VSTO) package that is eas-
ily distributed to SharePoint Workspace, Word, and Outlook 2010 client desktops by leveraging the
ClickOnce capabilities in the.NET Framework. BCS also exposes APIs to extend solution packaging
to target additional clients. A BCS solution can be created using SharePoint Designer 2010, an external
data source, and SharePoint Server 2010. The following exercise creates a BCS solution that requires
the following:

The Business Data Connectivity Service application and proxy must be created and started.

This can be checked by browsing to the Manage Service Applications web page in Central
Administration. A service application instance can be created using the wizard as part of the
installation, as shown in Chapter 4. A new service application instance can also be created
manually or by using PowerShell. The manual approach was demonstrated in Chapter 20
and discussed in Chapter 7, so readers are encouraged to review this information if necessary.
SQL Server 2008 must be installed on the same physical server as SharePoint Server 2010,

including an instance of the Northwind database. The Northwind database can be down-
loaded and installed from

If SQL Server is installed on a server other than the SharePoint server, then you need to create