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:
- Database Configuration
- Creation of a Secure Store Application
- Setup of an External Content Type
- 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:
- Open SQL Server Management Studio and connect to the server you are working with
- In the Object Explorer, navigate to the “Server ObjectsàLinked Servers” node
- 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).
- 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.
Conclusion
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.