Sterling International Consulting Group

Business Connectivity Services (BCS) Overview

Email | Print

Step by Step: A BCS Overview

We’re often asked about what SharePoint’s Business Connectivity Service (BCS) in SharePoint 2010 and 2013  can be used for and how difficult it is to set up. While the short answer is pretty simple – BCS enables you to connect to ‘other’ data sources outside of SharePoint and literally treat them as a SharePoint List. For those that worked with the “Business Data Catalog” (BDC) in SharePoint 2007, be aware that BCS’s core is the BDC but with a significant change – instead of a one-way ‘view’ that was available in 2007, 2010/2013 feature ‘operations’ that provide two way usage (read, write, update, delete, etc.).

On a whiteboard it can be pretty easy to explain how to use it but it can be confusing since it requires setup in SQL Server, Central Administration and SharePoint Designer. For more advanced operations it can also include Visual Studio. That’s a lot of moving parts for some folks.

Since we provide a number of customized training programs, during the course of this we’ve created a simple step by step tutorial that has helped many of our clients and students get up to speed quickly. Not overly complex, the tutorial covers creating an external data source, creating an external content type and generating the list to use in a SharePoint site. To keep it simple, no coding of any kind is required.

In addition to the tutorial, we’ve also provided fixes for some of the most common issues and a list of some very useful links to dig into BCS further.

NOTE: THE TUTORIAL USES SHAREPOINT 2010 AS AN EXAMPLE TO ACCOMMODATE THE NUMBER OF INSTALLATIONS CURRENTLY IN PLACE. THIS WILL BE UPDATED FOR 2013 LATER.

 

Step by Step: Business Connectivity Service (BCS) Overview

SharePoint’s Business Connectivity Service (BCS) is intended to provide SharePoint access to a variety of data sources to include:

  • SQL Server

  • .NET

  • WCF Service

  • SOAP

  • REST Service Connection

  • XML File Connection

Key Points about BCS:

  • BCS Definitions are based on ‘operations’, Create, Read Item, Read List, Update and Delete

  • BCS allows you to work directly with data stored in an external system or database

  • Authentication can be done directly using User authentication or the SharePoint Secured Store Service can be used to authenticate with a specific account

  • Information can be one-way (view only) or two-way (add, update & delete)

  • BCS data sources can be ‘associated’ to one another to create ‘relationships’ – One-to-Many, One-to-One, etc.

  • BCS Connections can be made using SharePoint Designer (easiest) or through Visual Studio

  • BCS ‘definitions’ can be exported and imported across sites

  • BCS is based on the “Business Data Catalog” and uses BDC definitions to access “line of business” (LOB) data.

  • WCF provides the most flexibility and control of data and is best when multiple data sources must be combined to create the desired view

 

Step by Step Part 1: Create a data source

For this example, you will be creating a SQL Database and adding a table with items to read/write. This example is based on the use of the DEMO2010A Virtual Server available from Microsoft. This virtual includes all of the components – SQL Server, SharePoint, SharePoint Designer and Visual Studio – and can be used for up to 30 days.

This can be downloaded from here: http://www.microsoft.com/en-us/download/details.aspx?id=27417

Creating the database will depend on your installation – for the purposes of this tutorial, SQL DEMO2010A refers to the Server name (replace this with your SQL Server Instance Name, i.e. server\instance or server if the default):

    1. Open SQL and connect to the DEMO2010A Database:

  1. Right click on Databases and select New Database

  1. Name the database Customers:

  1. Click OK to create the database

  1. Expand the databases by clicking the + next to Databases and locate your Customers database

  1. Click + next to the Customers database to expand it

  1. Right click on the Tables folder and select New Table

  1. Add two columns, Customer ID and Customer Name as shown:

  1. Click the ‘X’ in the right corner to close the window – this window should pop up:

  1. Click Yes – when the next pop up appears showing Table 1, change the name to be Customers and click OK:

  1. Click + next to the Tables folder to expose the table:

  2. Right click on the table name and select Edit Top 200 Rows

  1. Add three new rows to the table:

  2. On the last row, be sure to hit the Return key to commit the last row (if you see the red exclamation on the row as above, it has not been committed) – when done the display should look like this:

  1. Next click the Execute Icon to save the rows to the database:

  1. Leave SQL Server Management Studio open and continue to Part 2

Step by Step Part 2: Creating the External Content Type

  1. Open SharePoint Designer 2010 and connect to your site

  1. When the site is loaded, click on the External Content Types folder (it will take a moment for Designer to contact the site and load them and will show a pop-up as it does)

  1. In the ribbon, click External Content Type:

  1. The external content type will open in the ‘Summary View’ – click the name (New External Content Type) to change it to Customers:

Click Return to save the name.

NOTE: The Office Item Type allows you to ‘map’ an external content type of one of the ‘known’ SharePoint type, for example, Announcements, Tasks, etc. – this allows you to map individual columns from the data source to the standard SharePoint list type. Since this is a ‘Raw’ SQL Server table, Generic List should be used – this is the same as creating a Custom List.

  1. Next we need to add the data source that the type will use – click ‘Click here to discover external data sources and define operations’. This will open the Operations View:

  1. Click the Add Connection button then choose the Type as SQL Server and click OK:

  1. Next, enter the database server name and name of the database and click OK (you can set a special name if you want, if you don’t, it will assume the database name):

NOTE: Connecting with the User’s Identity is the same as “NT Authentication”; Impersonated Windows and Custom Identity allows you to use the Secure Store Service (SharePoint’s SSO).

  1. After the connection is validated, the database should appear in the Data Source Explorer window – click + next to the database name then again next to the table folder – this should display the tables as shown:

  2. Right click on the Customers table to generate operations (read, update, delete, etc.):

  1. For this demonstration, click Create All Operations – this allows read, list, update and delete.

  1. On the Operation Properties page, click Next (if there are any errors, such as a read only table, they would be indicated here)

  1. On the Parameters Configuration page, you will need to define the Identifier for this table (basically the key to the table) – click Customer ID and then click Map to Identifier:

NOTE: Notice the Office Property drop down – this is how you can map, field for field, the a SharePoint standard type (i.e. Tasks)

  1. Click Finish to create the operations (if you click Next, you can specify filters for the data, i.e. a subset of the data in the database) – once these are created, you should see the operations listed under External Content Type Operations (right side of the page):

  1. Next you want to generate the list and forms for the external content type (this creates the actual list in SharePoint) – in the ribbon, click Create Lists & Form:

NOTE: The Create Profile Page provides you a way to ‘test’ the data source – this is a single page in which you can pass an item key (i.e. Customer ID) to display a single record and effectively test it.

  1. Designer will prompt you to save the external type, click Yes:

  1. Next Designer will prompt you to specify the SharePoint list information – set the name and description as shown:

NOTE: You can optionally create an InfoPath form to replace the standard SharePoint list form if desired.

Step by Step Part 3: Settings BDC Permissions

Once this has completed, the list has been created in the site. However, you do not have access yet since the BDC – you can ‘prove’ this by opening your site and open the list:

The reason is that though you have created the External connection, it has just been registered in the BDC service but with no users.

  1. Open the Central Administration site and from the home page under Application Management, click Manage Service Applications

  1. Click on the Business Data Connectivity Service (check – the name may be different) to open it:

  1. When it opens, you will see the Customers external content type shown – click the checkbox to select it then click Set Object Permissions:

  1. Set the account and set the permissions and click OK:

Step by Step Part 4: Working with the List and SQL Data

  1. Navigate to the site and select Site Actions > View All Site Content and locate the Customers list to open it – you should see the matching data from SQL (if you have it open to the Access error, you must refresh the page):

  1. Add a new item to the list (in SharePoint):

NOTE: We did not set the ID to be unique; you can do so and SharePoint will return an error if a duplicate is entered.

  1. Return to SQL Server Management Studio, right click on the Customers table and select ‘Select 1000 Top Rows’ – you’ll see that the update was made:

NOTE: Since the operations defined for this data source are for Add, Edit, Update and Delete, you can make a change or update to either side and see the result.

Common issues with the BCS Setup

Login Failed for IUSR account

Using the Business Connectivity service (BCS) in either SharePoint 2010 or SharePoint 2013 you may come across the message “login failed for <system>\IUsr account” when you try to display the external list in SharePoint. You may wonder why since this account has nothing to do with SharePoint (and for those of you that are not aware of this account, it is the default IIS account – used when a user is not authenticated). This is similar to the old ‘double hop’ issue we’ve had with SharePoint since 2003.

The problem is simple – when accessing data OUTSIDE of SharePoint (and not using Kerberos), it may default to the IUSR account when attempting to access the data.

The best fix is to ensure that all services are properly assigned and that the SharePoint Web Services are not running as Local System. However, a quick fix (or until you find the proper one) is to simply grant the IUSR account access to the data source (or database).

By default, the IUSR account is designated by the system name (as a local account); for example server1\iusr. However, you will find that you cannot add the account that way in SQL Server – instead, use NT AUTHORITY\IUSR and you’ll be able to add it. Be sure to grant the proper permissions in the database in question and also grant public access to the master database.

After adding, return to SharePoint and refresh – all should be good!

 

Access denied by Business Data Connectivity Server

On attempting to view the list, you receive an error message “Access denied by Business Data Connectivity Server”. This simply means that the current user account doesn’t have the proper permissions in BCS. You correct this doing the following:

  1. Open up the Central Administration site
  2. Under the Application Management section, click Manage service applications:
  3. Find the BCS service in use – click next to the name to select it (the whole line will be selected):
  4. Using the Ribbon, check both the Administrators and the Permissions:

  1. Make sure the Farm Account (and any other appropriate accounts that should have access) is included under the Administrators. Under the Permissions, make sure that the Farm Account and the account to use when accessing the data source are listed.
  2. Next click the name of the service (in the example “BDC1”) to open it – this will display a list of all of the external content types defined – click the checkbox next to the external content type you are working with then in the ribbon, click Set Object Permissions:
  3. Add the appropriate users, for example, the Farm Account, the account accessing the data source, your own account, etc. and grant each the appropriate rights (at minimum, you should grant ‘Selectable in Clients’):

  1. Click OK to save the changes – return to the site and verify the error message goes away.
 

NOTE: If you make any changes to the External Content Type be aware saving them MAY cause the permissions to change.

Cannot connect to the LOB System

While attempting to view the list in the SharePoint site, you receive the error “Cannot connect to the LOB System”. This is caused by either using the wrong authentication method or the application pool account used to run the SharePoint site has no access to the SharePoint BCS database or the data source.

To correct, you need to grant the account in question access to the database(s) and to the BCS system if necessary. From the Error page, get the correlation error displayed. Open up the SharePoint ULS Log (by default, c:\program files\common files\microsoft shared\web server extensions\logs though they should be on a different drive that c:). Remember that the SharePoint log usually cycles every 30 minutes so when looking for it, be sure to open up the latest one (sort by date) – check the date of the log and verify the time is ‘around’ when the error occurred:
It is usually easier to simply open the log file with Notepad – you can however, use the ULS Log Viewer which provides a user interface for the log items. You can download the ULS viewer from here: http://archive.msdn.microsoft.com/ULSViewer.
 
Either way, use the Correlation number as a search key (usually just the first part of the number is best before the first dash). Once you find the lines in the log, search again for “login” and you should see the login failure message and account that SharePoint is trying to use.

The query against the database caused an error

When you open the list in the SharePoint site, the display returns an error “The query against the database caused an error”

To correct, you need to grant the account in question access to the database(s). From the Error page, get the correlation error displayed. Open up the SharePoint ULS Log (by default, c:\program files\common files\microsoft shared\web server extensions\logs though they should be on a different drive that c:). Remember that the SharePoint log usually cycles every 30 minutes so when looking for it, be sure to open up the latest one (sort by date) – check the date of the log and verify the time is ‘around’ when the error occurred:
It is usually easier to simply open the log file with Notepad – you can however, use the ULS Log Viewer which provides a user interface for the log items. You can download the ULS viewer from here: http://archive.msdn.microsoft.com/ULSViewer.
 
Either way, use the Correlation number as a search key (usually just the first part of the number is best before the first dash).
 
Once you find the lines in the log, search again for “error” and you should see the error message (for example, ‘The SELECT permission was denied…”). Add the specific user to the DB with Select, Update, Delete rights if needed.

 

BCS How To’s

A quick set of links to get the whole picture of using BCS:

  1. High level overview:

http://msdn.microsoft.com/en-us/magazine/ee819133.aspx

  1. Create external content type based on SQL Server:

http://msdn.microsoft.com/en-us/library/ee557243.aspx

  1. Create using Designer:

http://lightningtools.com/blog/archive/2009/11/17/external-content-type-in-sharepoint-designer-ndash-sql-server.aspx

And a combination of Designer/Visual Studio:

http://www.lightningtools.com/blog/archive/2009/10/30/bcs-shims-ndash-.net-assembly-as-a-data-source-for.aspx

  1. Creating External Content Types using the Secure Store:

http://msdn.microsoft.com/en-us/library/ee556780.aspx

And:

http://blogs.msdn.com/b/sridhara/archive/2010/01/27/setting-up-bcs-with-secure-store-application-impersonation.aspx

  1. Using BCS via Visual Studio:

http://www.lightningtools.com/blog/archive/2009/11/01/business-data-connectivity-model-ndash-finder-method.aspx

  1. Using the BDC Designer in Visual Studio 2010:

http://channel9.msdn.com/blogs/funkyonex/using-the-sharepoint-business-data-connectivity-designer-in-vs-2010

About us:

Sterling International Consulting Group is a specialist in enterprise systems including architecture, governance, business continuity and disaster recovery planning with over 28 years in IT and management consulting. SICG also has a dedicated practice in SharePoint Technologies and Microsoft Technologies. From planning to analysis to development to deployment, SICG has the experience and knowledge to understand the best for your business – find out why: sicg@sterling-consulting – www.sterling-consulting.com.

Keep up with the latest articles and tips around SharePoint from author & CEO David Sterling via http://www.sharepoint-blog.com and his personal blog site: http://davidmsterling.blogspot.com or contact him directly at david_sterling@sterling-consulting.com.

Related Posts



Ask This Expert a Question or Leave a Comment