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.

clip_image001

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.

clip_image002

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

clip_image003

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.

clip_image004

5. Click Generate New key from the ribbon option.

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

clip_image005

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.

clip_image006

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.

clip_image007

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.

clip_image008

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.

clip_image009

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

clip_image010

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

clip_image011

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

clip_image012

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.

clip_image013

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.

clip_image014

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

clip_image015

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.

clip_image016

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.

clip_image017

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.

clip_image018

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.

clip_image019

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

Comments (16)

  1. Rob says:

    Thanks for the article.  Very helpful.

    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.

    Others seem to be having similar problems.

    Any ideas on the cause?

    Thanks in advance.

  2. sridhara says:

    Hi Rob,

    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.

    Cheers,

    Sridhar

  3. Rob says:

    Thanks for the response, Sridhar.

    I setup a small test database with only 2 rows of data for the BDC model, so it can’t be the filter problem.  Also, I tried adding a filter to the BDC model, and it didn’t alter the error.

    I’ll look further into the ULS logs. If I find a cause, I’ll post back here.

  4. Alex says:

    Rob is there any solution for your problem since January because I have the same problem.

    Thanks

  5. Darrin Dyson says:

    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.

  6. Jamie Plenderleith says:

    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 :(

    social.msdn.microsoft.com/…/e33c1c9c-898d-4d6c-ac83-c9c40f5ce035

  7. Maja says:

    Hi,

    I created a new instance of Secure Service Store and then when I click Manage system gives the following error message:

    "Cannot complete this action as the Secure Store Shared Service is not responding. Please contact your administrator."

    I check under Services on Server and Secure Store Service is started.

    Any help is appreciated.

    Thanks.

  8. Pedro says:

    Rob, did you ever find out how to solve this? I have the same problem and I can't figure out how to solve it. I've tried "everything"

  9. Sandra says:

    tengo el mismo error, soy total mente nueva en esto de Sahrepoint tengo 3 semana empezando y me salio el mismo error , ya lo trate de solucionar y tampoko keda

  10. Sudeep says:

    Hello Sridhar,

    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.

  11. Ambarish says:

    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.

  12. It's a great and powerfull service, You can export so much things with no-code from SQL! thx SharePoint ! ..

    With a little more work we can export data from MySQL and oracle to ..

    I am working on it ..

    Cheers, Gokan

  13. Jhonson says:

    Hi,

    I have no experience in sharepoint.

    I need just to to follow your steps but i don't know how to do from step 15.

    How to configure data base connexion and how to create an external content Type.

    I have followed these steps msdn.microsoft.com/…/ee231515.aspx

    and the results gave me wsp.

    What is the next step?

    Thanks you for any help.

  14. Ivan says:

    Hi Sridhar

    Thanks for your informative walkthrough.

    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.  

    I have tried several different approaches to this problem:

    * Accessing the database using LINQ bypassing BCS – works fine but obviously uses my login credentials.  

    * 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).  

    Cheers

    Ivan

  15. John Paul C J- MSFT says:

    Funtastic Dude, this is an excellent Blog.

  16. SharePoint 2013 Developer Certification Training Online says:

    Information was good, I like your post.

    Looking forward for more on this topic.

    <a href="staygreenacademy.com/…/"> SharePoint 2013 Developer Certification Training Online</a>