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.

I’ve followed the article and successfully created each part. However, when the new list is accessed in the SharePoint site, it displays an error:

Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Microsoft SharePoint Foundation-compatible HTML editor such as Microsoft SharePoint Designer. If the problem persists, contact your Web server administrator.

When I open the page in SharePoint Designer, it shows a different error message:

soap:ServerException of type ‘Microsoft.SharePoint.SoapServer.SoapServerException’ was thrown.An error has occurred.

There could be multiple reasons for this error. Most likely, this is because you have not set a limit filter in your BDC model when you created it. If you query retrieves more than 2000 items, you might see this error in the UI. You can dig into ULS to see what the error is and correct it.

I had the same error as you. My problem turned out to be access to the Secure Store for the account I was logged in with. Also, if you look at the server’s event log, it should point you in the right direction. Mine did.

This is a great walkthrough, but there are some differences if you're using Visual Studio 2010 as far as I can see? I've created some BDC models in VS2010 but can't seem to get the security side of things working 🙁

I have setup the SSS Application but when I try to create ECT, my Windows Credentials are trying to access the SQL Server Database but not the Secure Store Service Application ID. Do you know why this weird behavior? I tried recreating the SSSA with no luck.

I too got the error message mentioned below while trying to set up BCS for the first time in my lab. I had tough time figuring out the reason for the issue.

Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Microsoft SharePoint Foundation-compatible HTML editor such as Microsoft SharePoint Designer. If the problem persists, contact your Web server administrator.

The error above is trying to tell us that the account with which we are logging in is not having the right to go to the LOB database and retrieve the information. It has rights either on the BCS content type and not on the Secure store service application created for accoutn mapping.

I hope you are using "windows identity impersonation" authentication method on your BDC model.

The best bet here is to define an secure store application and then add any AD group here which has your users and then have the same group added to the central administration site –> BCS application –> click on set permissions against the external content type application and thats it! This problem will be resolved.

I'm trying to build a .NET assembly connector for Sharepoint Business Connectivity Services (BCS). I have built the BDC Model in Visual Studio using LINQ to SQL to perform the database queries (Stored Procedures). I want to use Windows Integrated Security but I want the database query to execute as the generic user I have in the Secure Store. When I try to run a SQL profiler trace on it, I find that it is using my own username.

I am using Visual Studio 2012 and Sharepoint 2010. I have started a Secure Store service populated with the credentials of the generic user. The connection information I can retrieve from the custom properties of the LobSystemInstance BDC model.

* Executing the query "using WindowsIdentity" made from the credentials of the account in the store – still uses my login credentials to access the database.

* Doing what I presume you're doing here – building the model in Sharepoint Designer and then exporting to Visual Studio (works for the simple solutions we've tried so far, but we'd like to know why, and how to do it without using Designer).