Skip to main content

Accessing External Data Using Business Connectivity Services in SharePoint 2010

Author by John Adali

Business Connectivity Services (BCS) in SharePoint 2010 is a robust and powerful technology that is part of SharePoint 2010. Microsoft has enhanced BCS with new features, services and tools in order to streamline the development of solutions with deep integration of external data. This article will run through the steps (end to end) to use BCS to use data in SharePoint coming from an AS/400 mainframe system. Imagine the scenario where a company has line-of-business data stored in a mainframe (AS/400) system, and they want to utilize this data in their new SharePoint 2010 environment. BCS provides the mechanism for reuse of this data, thereby solving the problem of migrating data from the AS/400 system, maintenance of duplicate sets of data. We are going to use this scenario as our example for demonstrating what BCS can do for businesses. There are a number of steps that will be followed to accomplish this, and are listed here:
  1. Database Configuration
  2. Creation of a Secure Store Application
  3. Setup of an External Content Type
  4. Creation of the External List in SharePoint
For this example, SQL Server 2008 R2 was used for the database server, and SharePoint 2010 Server Enterprise was used for the SharePoint site.

1. Database Configuration

Accessing the AS/400 data can be done through SQL Server via a linked server. Once a linked server is setup, a normal SQL database can be created to access this linked server through views. In order to create a linked server, follow these steps:
  1. Open SQL Server Management Studio and connect to the server you are working with
  2. In the Object Explorer, navigate to the “Server ObjectsàLinked Servers” node
  3. Right mouse click on “Linked Servers” and create a new linked server. You can use whatever name you choose (I am using the GBCUSTOMERDATA name).
  4. Follow the prompts to create the linked server. Make sure you use the correct remote login and password when appropriate.
Once complete, your linked server should look similar to this: After you have setup your linked server, you can now create a separate SQL database and create views that reference the linked server. I have created a database called CustomerData that will be used throughout this article.

2. Creating a Secure Store Target Application

The next step is to create and configure a Secure Store Target Application. A Secure Store Target Application is used to control authentication to the external data system, in order that the end user in SharePoint is not continually prompted for authentication credentials. This component will be used when configuring an External Content Type. Creating a Secure Store Target Application is done in Central Administration, under the Security area. Before a new Target Application can be created, a new encryption key must be created for that application. This is done by navigating to the Application Management section, and clicking on the Manage Service Applications link under Service Applications. This takes you to a list of the service applications. Scroll down to the Secure Store Service and click on it to access the area to create new secure store target applications. To generate a new key, click the Generate New Key icon in the ribbon. A dialog will appear, asking you to enter a passphrase. This passphrase is used if you need to edit the target application, so make sure you save this in a secure spot for reference (it is not saved anywhere in the system). At this point, you are able to create a new target application for the secure store. Clicking on the New icon in the ribbon will begin this process. The first screen allows you to enter information such as the Target Application ID, Display Name, Contact E-mail, Target Application Type, and whether the target application has a page that redirects users when they incorrectly enter credentials. Target Application Type should be set to Group, as this allows configuration of a group of users for the target application. Other selections here are Individual, Individual Ticket, Individual Restricted, Individual, Group Ticket, and Group Restricted. Please note that here is where you name the target application, along with the Target Application Type, which should be selected as a Group type, so that we can map a group of users to this Secure Store Target Application. If you wish, you can select Individual as the Target Application Type, which will only allow individual users to be mapped to this Secure Store Target Application. For the Target Application Page URL, I selected None, as I do not want any screen to prompt the user for credentials. You can select a default or custom page if you wish your users to be prompted for credentials. Clicking on the Next button takes you to the following screen, which allows configuration of authentication fields. Please note that you have a lot of freedom in what you configure here for fields names, but the default field names of Windows User Name and Windows Password should be selected. Clicking on the Next button takes you to the next screen, which allows selection of the administrator accounts for the target application, as well as the members who are mapped to the target application. Enter the administrative user(s) for Target Application Administrators, as these users will be the ones managing the Secure Store Target Application. For Members, you can enter any users or groups defined in your Active Directory environment. I selected All Authenticated Users, as I want any user who is authenticated in the AD domain. Click OK to complete the configuration of the Secure Store Target Application. Now that your Secure Store Target Application is created and configured, you need to set the credentials that will be used. This will be the account that has access to the external data system. Selecting the Target Application and clicking on the Set button in the ribbon (under Credentials) will display the following dialog, allowing entry of the username and password you wish to use with this Secure Store Target Application. Setting the credentials for the target application (which you configured as a group type) is essential, as SharePoint uses these credentials when accessing the external system, instead of prompting the user for credentials each time the external system is accessed.

3. External Content Types

In order to provide access to external data in SharePoint, an entity called an External Content Type must be configured in SharePoint. An External Content Type (ECT) defines the connection information on where to access the external data, as well as the operations to perform when retrieving (or updating) the external data. Creating an ECT is done with the Microsoft SharePoint Designer tool. There is a link on the left-hand side panel called External Content Types, that will display the information about the ECTs defined in the system. Click on the External Content Type button in the ribbon, under the New section. This will create a new External Content Type, as seen below: From here, you can configure the ECT, such as setting the name, the Data Source Type, and creating the operations that the ECT performs. For this example, I set the name as CustomerData. Notice that the new External Content Type does not have any operations defined initially. In order to do this, click on the Click here to discover external data sources and define operations link, under the External Content Type Operations area. This will display the following page, which allows you to define operations for the ECT. Before operations can be created for the External Content Type, you must first create an external data source. Click on the Add Connection button to create a new external data source. You will be asked what type the new external data source is. For this example, I selected SQL Server, as I am connecting the linked server previously created in SQL Server. Once you select the external data source, another dialog will be displayed, so you can enter the details of the data source. For a SQL Server data source, you are prompted to enter the database server name, database name, and how the ECT will connect to the database. We are connecting to this database with impersonated Windows identities, so select the Connect with Impersonated Windows Identity selection. Selecting this option will enable the Secure Store Application ID entry box, which allows you to specify what Secure Store Application is used (for credentials) when accessing the data source. For this example, I entered the NorthwindSecureStore secure store application. When you are finished, pressing the OK button will cause the system to validate the data connection. You will be prompted to enter the administrator credentials for the Secure Store application (NorthwindSecureStore) as part of this validation. Enter the credentials you configured when you configured the secure store application. Once the system is finished, you will be returned to the Operation Designer page for your ECT in SharePoint Designer. You will now notice that there is a data connection in the Data Source Explorer tab that corresponds to the connection you just created. For this example, the SQL connection is to the Northwind database. At this point, you are able to create operations for the external content type. To do this, expand the Northwind data connection and navigate to the table you wish to create operations for. I created Read List operations for the Customers database table by right mouse-clicking the Customers table, and selecting the New Read List Operation selection from the menu. The system will now take you through the steps to create read list operations for the Customers database table. This includes naming the operations name, as well as creating any filters on the data that you need, and any return parameters. Clicking Next progresses you through the different dialog screens, and clicking Finish completes the creation process. When configuring filter parameters, you select the Data Source element that the filter uses to filter the data. Selecting return parameters specifies what columns from the data connection are returned when executing the operation. For this example, I want all the columns in the Customers database to be returned, so I selected all the columns available. I also want the CustomerID column to be mapped to the CustomerID column identifier in the database, so I check the Map to Identifier checkbox and select CustomerID, under the Identifier dropdown. You can also configure columns to be required or read-only, as appropriate. Once you press Finish, you are returned to the Operation Designer, and you will notice that the operations you just created are listed under the External Content Type Operations section. As a final step in creating the ECT, go back into Central Administration, and verify that the external content type has proper permissions configured. Under Service Applications, click on the Business Data Connectivity Service. Right-mouse click the CustomerData external content type and select Set Permissions. The dialog that is displayed shows what accounts have permissions for the external content type. Verify that the administrative account you configured previously has the proper permissions to the external content type. For this example, I gave the WINGTIPAdministrator account full permissions to the CustomerData external content type.

4. SharePoint External List Configuration

The final step in configuring the SharePoint system for external data is to go into the SharePoint site and create SharePoint lists that access the external data. These lists can then be used in SharePoint, and will access the data from the external system (AS/400). To create a list that accesses external data, go to All Site Content and create an External List. When prompted for an External Content Type, select the appropriate ECT that was configured previously. At this point, you will be able to configure a new external list based off of the external content type. When you press the Create button, the system will create the external list.


Business Connectivity Services is a great way to leverage external data in SharePoint, but can be challenging to configure. I hope that this article helps in understanding what steps are needed to configure BCS, and provides a single reference for end-to-end configuration of accessing external data in SharePoint.

John Adali

Senior Software Developer - Modern Applications