How to create an External Content Type in SharePoint Designer 2010 using Business Connectivity Services(BCS) and fix issues that arise on the way


Por Chaitu Madala

In this walk-through I will explain how to use SharePoint Server 2010 Business Connectivity Services(BCS) feature to access external business data (SQL Server 2008 in this example). This simple step-by-step will also help you fix the issues that you might encounter on the way.

Create Model using SharePoint Designer

SPD includes functionality to design the application definition model visually. Based on the options selected on UI, it generates the xml metadata in the background. Using ECT Designer in SPD you can discover database, point to the table, view, or stored procedure that will perform the operations, and then return the required data and use it to create external content type without writing any code or XML. Follow the steps below to create the ECT:

Open up SharePoint Designer 2010 and click on “External Content Types”

External Content Types

To create a new external content type, click on “New External Content Type” in the ribbon

New External Content Type

Click on the link “Click here to discover external data sources and define operations”. This will open up the windows to define the connection to AW database and operations for the ECT.

Click “Add Connection” under External Data Source section and choose Data Source Type as SQL Server. This brings up the SQL connection properties dialog. In this we are connecting using SQL Server provider to get data.

Define Operations on External System

SPD provides option to create the view for all common operations available in BCS or it can create operations for specific operation.

Following two minimum operations are required to fetch data from back-end using BCS:

  • Query Item List method which gets the list of records and work as finder method
  • Read Item method which gets data for specific record and work as SpecificFinder method

Choose the appropriate external data connection and then the database table. Right click on the selected table and create operations as required. In this example, I have created all the operations that are possible through SPD 2010.

External Data Connection

After adding all the operations, we should be able to see something like in the image below:

External Data Operations

Create External List based on External Content Type

You can create an external content type by using Microsoft SharePoint Designer 2010 or the browser. Follow the steps given below to create list using browser.

  1. Open the SharePoint site in which you would like to create the external list in browser.
  2. Go to Site Actions, View All Site Content.
  3. Click the Create button. In the Custom Lists section, click External List.
  4. On the New page, type the list name and description for the new external list.
  5. The Data source configuration section displays a text box and an external content type picker. Use the picker to choose the external content type. Select the newly created external content type and then click OK.
  6. Click Create.

This creates the external list. You can now navigate to the new list in the SharePoint site and view/edit items.

Create External List

External List Details

All good so far. But you can expect to see the below error when we try to access the external list that has been just created.

Access Denied
This is because the BDC service that we just created has not been given permissions yet.

Open Central Admin > Application Management > Manage Service Applications > Business Data Connectivity Service and select the check box next to the service that we just created and then click “Set Object Permissions”. Add the user(s) that need to be given access as in the image below:

Set BDC Permissions

Go back to the external list and refresh the page if required.
Now we see a new error “Login failed for user ‘NT Authority\ANONYMOUS LOGON” as in the image below:

Login Failed

The above error occured because by default, when we create the BDC definition in SPD 2010, the authentication mode is set to “User’s Identity”.

The “Connect with User’s Identity” is the “PassThrough” authentication mode we had in MOSS 2007 BDC. The other 2 relates to SSO. Now that we have Secure Store Service Application, we can use “Connect with Impersonated Windows Identity” OR if we are using claims token we can use “Connect with Impersonated Custom Identity”

Inorder to access the data from the external data connection, one way of fixing the above issue is to change the Authentication Mode from “User’s Identity” to “BDC Identity”.

So open up the external content type in SPD 2010 and change the authentication mode.

Change Authentication Mode

Now we end up with a new error:

Change Authentication Mode Error

Below are steps we need to follow to get this corrected!

We have to first enable BCS model to accept “RevertToSelf” as one of the authentication modes. Yes, it’s disabled by default. We can do this using SharePoint 2010 Management Console.

The “ReverToSelfAllowed” property is set to false by default. We can now change it to true using the below script:

  1. $bdc = Get-SPServiceApplication | where {$_ -match “Business Data Connectivity Service”};
  2. $bdc.RevertToSelfAllowed = $true;
  3. $bdc.Update();
$bdc = Get-SPServiceApplication | where {$_ -match "Business Data Connectivity Service"};

$bdc.RevertToSelfAllowed = $true;


So finally when we hit the list again, we should be able to see the rows from the SQL Server table as items in the external list that we have created. Also notice the highlighted top left corner in the image below.
We are able to see the options “New Item”, “View Item”, “Edit Item” and “Delete Item” because I have created all the operations from SPD 2010 when I created the BDC definition above. If you skip any of the operations for example “Delete Operation”, the “Delete Item” option will be disbled in the ribbon.

BCS Item Operations


Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do

Você está comentando utilizando sua conta Sair /  Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )


Conectando a %s