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

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

Explanation:
In this walkthrough 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"



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

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 backend 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.



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

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.

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.



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:

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:



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.



Now we end up with a new 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:

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

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 diabled in the ribbon.