New book: Microsoft SharePoint Foundation 2010 Inside Out

We’re excited to announce that Errin O’Connor, Penelope Coventry, Troy Lanphier, Johnathan Lightfoot, Thomas Resing, and Michael Doyle’s Microsoft SharePoint Foundation 2010 Inside Out (ISBN 9780735627246; 752 pages) is now available for purchase!  

You can find the book’s introduction in this previous post.

In today’s post, please enjoy reading an excerpt from Chapter 9, “Working with External Content.”

Chapter 9 - Working with External Content

• Using SharePoint Designer with External Content
• Using the BCS
• Presenting External Content
• Using Visual Studio 2010 with the BCS

Traditionally with Microsoft SharePoint, data is stored in lists and libraries; however, most organizations do not want to move all their data into SharePoint—nor should they. Most organizations have spent time and money to build or purchase specialized systems, such as Siebel, CRM, and SAP to assist with key business processes. Understandably, it makes sense to integrate the data from those external systems into SharePoint sites and applications, such as Microsoft Outlook 2010, Microsoft Access 2010 and Microsoft Workspace 2010 with an easy-to-use interface.

Using SharePoint Designer 2007 and Windows SharePoint Services 3.0, it was possible to connect to external content by using the Data Source Library task pane. This method is still available with SharePoint Designer 2010 and SharePoint Foundation 2010, but it is now known as the Data Sources gallery. However, in addition to this method of connecting to external content, in SharePoint Foundation, you can now use an enhanced version of functionality that was originally available only with the Enterprise Edition of Microsoft Office SharePoint Server 2007. It was then called the Business Data Catalog; now it is exposed as a service application called the Business Connectivity Service (BCS).

The BCS centrally stores the definition of the external content—both its location, the type of data it is, and the behavior of the data when it is integrated into SharePoint and Microsoft Office client applications—in the Business Data Connectivity (BDC) metadata store. The definition of the external content is known as the External Content Type (ECT). The definition of the location of the external system together with the ECT is known as the BDC Model. Once an ECT is defined, then by using the browser or SharePoint Designer, you can manipulate the data from the external system similarly to other SharePoint objects, such as lists and Web Parts via a new list type to SharePoint Foundation, called the External List.

In this chapter, you will learn how to use the Data Source gallery and the BCS. You will also look at the differences between the two methods. You will look at the architecture of the BCS, including the security options. Then, you will look at managing the data connections and how to expose the data from the external systems on webpages and in lists and libraries. Finally, you will learn how use Microsoft Visual Studio 2010 with BCS.

Using SharePoint Designer with External Content

Using Microsoft FrontPage 2003, and then later SharePoint Designer, it has always been possible to connect and present data from several types of external data sources. For each site, you can specify the location and connection query to external data sources. For example, in SharePoint Designer, open the site in which you want to present the data, and then in the Navigation pane, click Data Sources. The Data Sources gallery appears in the SharePoint Designer workspace, as shown in Figure 9-1, where each data source is grouped by data source type.

Figure 9-1 Data Source connections displayed in the Data Sources gallery.

Table 9-1 describes the variety of data sources that can be used by SharePoint Designer to connect to external content.

Table 9-1 Data Source Types

To create a new data source definition, either click in the Data Source gallery or click the icon to the left of an existing data source to activate the commands on the Data Sources ribbon tab. Then click the appropriate data source type in the New group to display the Data Source Properties dialog box, as shown in Figure 9-2.

Figure 9-2 Use the Data Source Properties dialog box to define the location and data that you want to be returned from the external system.

This dialog box can contain up to three tabs:

· General Use this tab to give the data source definition a meaningful name. SharePoint Designer creates a name for the data source definition if you do not provide one.

· Source This tab contains different options, depending on the data source type. For the database connections data source type, the Source tab contains one button, the Configure Database Connection, which when clicked opens the Configure Database Connection Wizard, in which you specify the server where the database is located, the provider name, and credentials that will be used to access the database. Alternatively, you can provide your own custom connection string. If you are not connecting to an SQL server, then the second page of the wizard allows you to select the table or view or specify custom Select, Update, Insert, and Delete commands using SQL or stored procedures.

· Login Use this tab to define the authentication method by which you connect to the external system. The Login tab on the Data Source Properties dialog box for the Database Connection data source is not displayed; however, you are asked which authentication method you want to use once you click the Configure Database Connection button. You need to consider carefully the authentication method used to connect to the external systems, because this has security and infrastructure implications. The three methods that you can use with most data source definitions are:

· Don’t attempt to authenticateThis is equivalent to anonymous access.

· Save this username and password in the data connectionThe user name and password is transmitted over the network in clear text. An attacker can possibly compromise this authentication option. You should ensure that your infrastructure is configured appropriately for the sensitivity of the external content, for example, by using Kerberos, Secure Sockets Layer (SSL), or Internet Protocol Security (IPsec).

· Use Windows authentication The user’s authentication information is passed by SharePoint to the external system. This mode always incurs a double hop unless SharePoint Foundation 2010 and the external system are installed on the same server, which usually only occurs in small organizations or in larger organizations in a development, prototyping, or demonstration environment. Then, the user’s identity just has to make the one hop from the user’s computer to the SharePoint server. In most installations, when the external system and SharePoint Foundation are installed on different servers, SharePoint cannot pass the user’s identity to the external system (the second hop). The workaround is to use Kerberos or SSL with this authentication method.

See Also more information on the double hop issue can be found at: https://blogs.msdn.com/b/knowledgecast/archive/2007/01/31/the-double-hop-problem.aspx.

INSIDE OUT The details of the data source definitions that you create are stored in Universal Data Connection (UDC) version 1 file format in XML files in a hidden library, fpdatasources, in the _catalog folder. Users who can see the hidden URL site structure when using SharePoint Designer can view this library and the _catalog folder. SharePoint Designer creates the fpdatasources library when the first data source definition for a site is created. For the data source definitions that are dynamically created for lists, libraries, and XML files that are in the root of the site or in the Site Assets library, no XML file will exist. However if you copy one of these automatic data source definitions, then an XML file will be created. You can copy a data source definition by using the Copy And Modify command in the Actions group of the Data Sources ribbon tab.

Once the external content connection is defined in the Data Source gallery, then these definitions can be used with the Data Form Web Parts to display the content. When a Data Form Web Part is used to display the external data, a copy of the data source definitions are placed into the Data Form Web Part’s XSLT code. If you update the definition in the Data Source gallery, the definition stored in the Data Form Web Part is not updated.

See Also For more information about the Data Form Web Part, see Chapter 7, “Add, Edit and Connect Web Parts on the Page.” You can find more information about how to use the Data Source gallery in the book, Microsoft SharePoint Designer 2010 Step by Step, by Penelope Coventry, (2010, Microsoft Press).

Using the BCS

The BCS is implemented as a service application. This allows you to create external system definitions once, and not only share those definitions to many sites within the same site collection or SharePoint web application, but to share those definitions with more than one web application. In addition, a SharePoint farm—a SharePoint installation that is installed on one or more servers that share the same SharePoint configuration database—can host more than one BCS; each one can be configured independently by different sets of administrators. Also, a BCS on one SharePoint farm, and therefore the external system definitions stored in that farm, can be referenced from another SharePoint farm so that access to external systems can be managed centrally but are consumed from SharePoint installations hosted in other locations. A BCS can also be partitioned in a multi-tenancy configuration, which is the term commonly used to describe the isolation of web sites in a hosting environment.

INSIDE OUT In SharePoint Foundation, you can create only one type of service application. In SharePoint Server 2010, many service applications are provided such as the Managed Metadata Service (MMS), Access Services, Visio Graphics Service, and Secure Store Service (SSS).

The BCS can be divided into four areas:

· The External System This is where the external content resides. It can be maintained by one of your organization’s business critical applications often known as line-of-business (LOB) applications. They might have a custom user interface or a programmable interface, such as a Windows Communication Framework (WCF) service, web 2.0 source or as a database. Before using BCS, you should explore the external system to which you want to connect and evaluate the best method of connecting to that system. Check with creators of the external system as to the methods available to access the content. If there is more than one method, then ask which is the best option for you. In many cases, the web services method is the best; for example, if you can obtain the external content from the external system by either directly interrogating the database or by using web services, choose web services. If you have an external system that does not have a compatible interface, then you could develop your own BCS connectors or expose the content as a web service. The Microsoft BCS team has a two-part blog called “Making Web Services BCS Friendly.” The first part of the series can be found at https://blogs.msdn.com/b/bcs/archive/2009/11/18/making-web-services-bcs-friendly-part-1.aspx.

· Connectivity Before SharePoint can access the content from an external system, the definition on how to connect to it and the authentication method used must be created. This is known as the Business Data Connectivity (BDC) model. The BDC model consists of declaration XML that describes the external system that you want to access as well as the operations you might like to perform on this external content; for example, read a list of data, or read one item (row) of data, or update one item (row) of data. The BDC model can be created on a development or test SharePoint installation, from which it can be downloaded and imported into the SharePoint production farm where it resides in the BDC metadata store, or it can be used by Microsoft Office applications. The BDC model can be used in a SharePoint installation to create ECTs, also known as entities. However, in a SharePoint 2010 installation, before you can create or upload a BDC model, you must first create the BDC service application. Office 2010 applications only contain the components that allow you to upload a BDC model, and thus, there is no management or configuration interface provided.

· Presentation This is the client-side consumer of the external content, such as an Office 2010 application, or if you are using SharePoint, it could be an External List created from the ECT.

· Tools Microsoft provides two tools to create the BDC model, to interact with the BCS program interfaces, and manipulate the BDC objects. These are Microsoft SharePoint Designer 2010 and Visual Studio 2010. There are other third-party tools that can help ECT designers such as BCS Meta Man, which can be found at: https://lightningtools.com. You can also use an XML editor such as XML Notepad 2007 or Notepad to create a BDC Model.

Figure 9-3 shows the high-level interaction between these four areas. Notice the symmetry; the BCS architect is the same for Office 2010 applications as it is in a SharePoint installation. However, the Office 2010 applications do not have a BDC metadata store. In its place they have a BDC client-side cache so that when content in an external list is taken offline, the BDC model is taken from the BDC metadata store on the server and stored in the BDC client-side cache. The offline content from the external list is also stored in the client-side cache, which uses a SQL Compact Edition client database so that the offline external content and the BDC model is persisted when the user’s computer is shutdown.

Figure 9-3 The components of the Business Connectivity Services.

Also, note in Figure 9-3 that the Office 2010 applications also have their own connectors; therefore, when a user switches to online mode, the Office application connects directly to the external content without connecting through SharePoint. Other Office 2010 applications such as Access 2010 can import a client-side version of the BDC model, and therefore in this scenario, Access 2010 does not need to connect to SharePoint at all; it would connect directly to the external system.

INSIDE OUT To take the external data offline, additional logic is required. This logic is provided by a Visual Studio Tools for Office (VSTO) ClickOnce deployment package, which is only provided with the Enterprise Edition of SharePoint Server 2010. Therefore, with SharePoint Foundation you cannot take external list data offline in Outlook 2010 or SharePoint Workspace 2010. You can find more information about deploying Office solutions at https://msdn.microsoft.com/en-us/library/bb386179.aspx.

The advantage of using BCS as opposed to the Data Source gallery in SharePoint Designer is that you only need to define the external system and ECT once; you can then use that ECT on many sites across all web applications that are associated with the BDC service application. The disadvantage is that ECT designers must be given edit permissions to the metadata store, which is a high level of security, whereas with the Data Source gallery you only need to be, for example, a site owner. In addition, other BCS security settings are needed to allow users to access the external content that can only be set by using the SharePoint 2010 Central Administration website or Windows PowerShell. This results in a level of collaboration between the ECT designers and the SharePoint farm administrators, which in large organizations is typically two different people.

To connect and retrieve data from an external system with SharePoint Foundation, complete the following tasks.

1. Create a BDC service application and set permissions on the BDC metadata store to allow for the creation of the BDC model, external system definitions, and ECTs.

2. Define the external system connection.

3. Define the operations to create, read, update, and display (CRUD) content stored in that external system as appropriate to your business requirements.

4. Create an ECT based on an external system definition.

5. Configure the permissions on the ECT so that users can see content from the External system.

6. Use the ECT to present the data from the external data source as External Lists, an External Data Column, Web Parts, or from within an Office application.