Setting up BCS with Secure Store Application impersonation




We used to perform SSO impersonation in BDC in MOSS 2007.  We now have a secure store service application that allows us to specify types of target applications to use for impersonating specific services including BCS.  Here’s a walk-through I wrote for one of my customer to setup secure store application for impersonating BCS calls.

1. Start the Secure Store Service by navigating to Central Administration site > Manage Service on Server.


2. Provision the Secure Store Service Application by navigating to Central Administration > Manage Service Application > New (drop-down from the ribbon) > Secure Store Service. Provide a name for this service application, choose a database and choose an application pool or create a new one.


3. The secure store service application and proxy should now be created.


4. Click on the secure store service application created to configure it. The first time you do this, a message will be displayed that asks you to configure the secure store application as shown below.


5. Click Generate New key from the ribbon option.

6. Provide the pass phrase in the dialog that pops up.


7. Now the secure store application is configure. We need to create a secure store application that will help in impersonating. To do this, click New from the ribbon in the secure store application as shown below.


8. Provide the needed values for the target application settings. Ensure that the target application type is “Group”. This is because we should be able to assign members who’s account will be impersonated by another account we specify.


9. Add additional fields in the next page if needed. Otherwise, just use the default Windows username and password fields that is provided by default.


10. Set the administrators for this target application in the next page. Also setup some members for this target application. In my case, I setup 1 local user “user1” as a member of this target application. We’ll touch base on what this is later in this walk-through.


11. The target application once created should look like below.


12. After this, use the ECB menu against the target application to set the application impersonation credentials.


13. Provide a credential owner, the windows username and password(s) that should be used for impersonation by this secure store application target.


14. Hit OK when done.

15. Now, when creating an application model for BCS we can select this target application to be used for impersonation. Typically, we provide the target application name BCS at the time of creating a connection to the backend. There might be a prompt to confirm the windows credential when you hit OK in the below screen.


16. Once you created your BCS model file and saved it to the site’s external content type store, you can download the application model file to take a look at the definitions of entities and the various methods.


17. Here’s how the LOBi system instance settings look like.


18. As you can see the target application we created in our Secure Store Application is used as the SSO application ID for this LOBi instance.

19. Now, we can create an external list in our SharePoint 2010 site and point it to the customer external content type we created.


20. I have another local user created in my site called “user1” that has contributor rights on this site. If I visit this external list as this user, I should still be able to see the data if the impersonation by secure store application is at work. That’s a fair expectation, but before seeing that in action we need to add this user as a member of our BCS application first. This is because BCS/BDC will first check permissions for metadata objects using the incoming user account first, then do the SSO impersonation and then go to the back-end as the SSO-impersonated user to pull the data. The key thing to remember to not get confused here is that the impersonation we do is for the BDC application to talk to the back-end data store. However, users that need to access the external list need to have appropriate permissions on the external content type objects.

21. To set permissions on BDC objects for a user account, navigate to Central Administration site > Manage service applications > select the BCS service application you created > Set Permissions on the ECB menu option of the external content type as show below.


22. Or set object permissions from the ribbon both should do. For my case, I setup “user1” with Edit, Execute permissions on the customers external content type object as shown below.


23. Once “user1” is setup with appropriate permissions on the BDC objects, we are good to go and see SSO impersonation in action. Now, if I login to the site as user1 and browse to this external list, I should be able to see the data.


Hope this was useful and helps in understanding the secure store and BCS layers to some extent.

BCS Connectivity Errors


Now that you have configured BCS, you can create external content types and external lists. You can refer to my blog post – SharPoint 2010: BCS Walkthrough – to quickly learn how to create an external content type and an external list.


BDC Access Denied Error

Now that your external list is created, you will certainly like to view the list in SharePoint. When you visit the external list, don’t be surprised if you see the error below:




This is a very common error that many of you might face. This error is due to the current user not having enough permissions to access the BDC entity.

So, browse to your BDC Service Application page: Central Administration | Application Management | Manage service applications | Business Data Connectivity

You should be able to locate your BDC model. For our example, its the External Customers. In the dropdown ECB menu, select on Set Permissions:




In the Set Permissions dialog window, you can now choose your user(s)/group(s) who need access to this BDC entity. For our example, I am choosing Administrator user.




You can also set the type of permission you want to grant. For our example, I have granted all of the permissions available:

1) Edit

2) Execute

3) Selectable In Clients

4) Set Permissions

Query against the database error

Now if you refresh your external list page, you might get this error or similar error:






This means that the user has access to the BDC entity, but there is something wrong when the model is trying to fetch the external data. In this case, it is from the database. Our External Customers entity model connects to SQL Server database to retrieve the customers. So, this error clearly tells that there is some problem while fetching that data.

One other useful thing to do when you get this error is to check the Windows Event Viewer logs. BCS logs errors to Windows Event Viewer logs. Here is the cause for our error:




It is very clear from the logs that the user Administrator is not having access to the Customers database. So, its an easy fix – After granting the user Administrator rights to Customers database, here we are with all the customers!



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