Fabian Williams SharePoint Blog

Solving problems with SharePoint day and night

How To: Create, Configure, Consume SharePoint 2010 Secure Store in Business Connectivity Services


Synopsis: I have seen quite a bit of confusion out there regarding how to use Secure Store Service for SharePoint 2010.  While MSDN does have interesting articles, there has been no Alpha to Omega process that shows the relationship to the LOB System, Security Groups representive of the BCS Consumers, BCS Access Account representive of the Credential Owner [Impersonated User], and how to wire it up in SharePoint Designer 2010. This blog hopefully will dispel all fears about Secure Store and answer a MSDN Forum question while at it.

UPDATE: – On 10/14/2012 I have added another blog post Series that will extend this post for SharePoint 2013 employing WCF, .Net Assembly and OData with SharePoint Apps see it HERE

 

The Blog is broken up into sections

  • Prep Work
    • Active Directory Users in Play
      • The Service Account I am selecting as the Impersonated User (Credential Owner)
      • The Security Group where all the people that will consume BCS Data will reside
    • SQL Server Security
      • Who has Access to What
  • Setup
    • Creating & Configuring the Secure Store Object
    • Creating & Configuring the External Content Type in SharePoint Designer 2010
      • Creating External Connection with Secure Store
      • Creating the External Content Type
    • Reviewing the External Content Type (ECT)
    • Reviewing the Security on the ECT
  • Test & Validation
    • Creating an External List derived from the ECT
    • Logging on as a User from the Security Group AND Secured in the permission setting of the ECT
    • Logging on as a User from the Security Group NOT Secured in the permission setting of the ECT

Part 1: Setup

clip_image001

Above:  This represents the AD Account [appBCSUser] which I will use as the Impersonated User i.e. the Broker if you will that will connect to the LOB system on behalf of the Group of people who should have access to the data but DOES NOT have access to the database. This is something your DBA will love because he doesn’t have a flurry of people having accounts on his/her DB.

clip_image002

Above: This represents the AD Security Group [SecureStoreBCSUsers] that have access or should have access to LOB Systems. You can of-course have multiple of these for any number of LOB Systems. Note here that Fabian and Hardeep are in this list, we will be the test users later on.

clip_image003

Above:  Lets look into CA now and set up our environment

clip_image004

Above:  Click Applications Management then Manage Service Applications

clip_image005

Above: We are interested in the Secure Store Service so we click it

clip_image006

Above:  We already have some there from previous Labs, but we will create a new one… click New

clip_image007

Above:  We create a Target Application ID [note this cant be changed once committed], Display Name which can be the Same App ID, and so on.

clip_image008

Above:  I populate the fields and choose “Group” as my Target Application Type. MSDN has a good explanation as to why you want to do that over other options. the Long and Short is that it allows me in this example to tie an AD Group FabianLab\SecureStoreBCSUsers to a single set of credentials i.e. the FabianLab\appBCSUser account. Ill show a few other options below

clip_image009

Above:  By default it wants to know how you will collect the credential of the Impersonated User in my case it is a Windows Account so this works.

clip_image010

Above: I change it around a bit for kicks by adding the word Testing infront of the default text

clip_image011

Above:  Here are a few other options that you can use. SSS is a Claims Aware SSO solution and can take in just about any Authentication Mechanism

clip_image012

Above:  So here because I only log on to CA with the Farm Admin Account, I set that as the target App Admin, however here is where we start to make the App Work for our design. In Members, you can see that i have my AD Group Account earlier. This means that I dont have to meddle with the SSS App anymore, just add and subtract from the AD Security Group.

clip_image013

Above:  It processes once i click OK

clip_image014

Above: Now i have a NEW SSS App, but wait you may ask… what about the Impersonated User.. we are coming to that…

clip_image015

Above:  We click on the custom actions available and select SET CREDENTIALS to set the Mapping for the Impersonated Users to the Group that we will Manage of “Allowed Users”…

clip_image016

Above:  Our trusty Silverlight App shows the progress of us opening a Dialog Pane

clip_image017

Above: The default look of the Credential Mapping

clip_image018

Above: I populated the values with my User Account previously mentioned in the AD Step

Part 2: Validation and Testing

 

clip_image001[4]

Above:  So in SQL Sever you can clearly see that the only account that has Access to the Database “FabianPlayPen” is the AD User mentioned above right…

clip_image002[4]

Above:  We create a new External Content Type by defining the name and Selecting External system to define our Connectivity

clip_image003[4]

Above:  We choose SQL from the list of choices

clip_image004[4]

Above: We define our SSO connection. One note here though in full disclosure, I had tried a few times to make this work and did a typo, so I re-did my SSS App and called it FabianLABSSSMSDNForumQ from what i had it last but the steps are the same.

clip_image005[4]

Above:  Here you may or may not get challenged for credentials when you click OK. The credentials you put here are or should be your own; assuming that you are in that Security Group that will be mapped to the Impersonated User. If not, then you need an account in that Security Group List.

clip_image006[4]

Above:  Once completed you will be able to connect to your LOB System, expand it and perform any operation allowable to you

clip_image007[4]

Above: In our instance lets just create a FULL CRUD operation

clip_image008[4]

Above: Validation that it is complete

clip_image009[4]

Above: Click the “Save” button to push the ECT up to the BDC Metadata Store.

clip_image010[4]

Above:  Now we can check a place where alot of Gotchas happen. Now one may assume that because they have access to the LOB system via the impersonated user and Group Mapping you are done… You’d be wrong, now you NEED to have permission to use the ECT and I already have mine set up by default under “Set Store Permission” to add myself, the search account, and my service account by default. You may need to put your security group here to make it seamless, but because i am doing demos and want it to break depending on my use case, i leave it fluid.

clip_image011[4]

Above:  to do that, click the custom actions and select “Set Permissions”

clip_image012[4]

Above: Do your business here by adding the users you want to have access. Here note that Hardeep doesnt have access while he IS a member of the Security Group.

clip_image013[4]

Above:  Once done, now we can create our External List by choosing our ETC recently created.

clip_image014[4]

Above:  Commit to the System and cross your fingers…. Voilla!

 

Part 3: UAT

clip_image015[4]

Above: Logged on as Me…

clip_image016[4]

Above: Logged on as Hardeep

 

Conclusion

Hopefully this helps you understand the mechanism of SSS, alot more can be done in Code using Visual Studio, have full all. Your comments and reposts are welcomed.

April 16, 2010 - Posted by | Business Connectivity Services, Secure Store, SharePoint 2010, SharePoint Administration, SharePoint Designer 2010, SharePoint Development, SharePoint How-To, SQL Server |

68 Comments »

  1. Just want to say what a great blog you got here!
    I’ve been around for quite a lot of time, but finally decided to show my appreciation of your work!

    Thumbs up, and keep it going!

    Cheers
    Christian, iwspo.net

    Comment by Toowhewly | May 16, 2010 | Reply

    • I appreciate the feedback, please spead the word… more blogs to come

      Comment by fabiangwilliams | May 28, 2010 | Reply

  2. Beautiful start to finish on SSS, Fabian. First complete post on the topic I’ve seen to date.

    Comment by Scott Morrison | May 27, 2010 | Reply

    • Thanks Scott, i have been traveling soo much that i havent posted in a while but i will this weekend on Managed Metadata, Term Store etc.. stay tuned.. appreciate the feedback, spread the word

      Comment by fabiangwilliams | May 28, 2010 | Reply

  3. Thank you very much for posting this! Fantastic walkthrough.

    Comment by Asher | June 3, 2010 | Reply

  4. Hi Fabian! how do you do this when modeling with Visual Studio? When I edit the BDC model XML manually and add the SecondarySsoApplicationId and SsoProviderImplementation properties, and then deploy, i keep getting an error in the logs saying “The property with name ‘secondaryssoapplicationid’ is missing on the lobsysteminstance”.

    Comment by Phil Wicklund | June 4, 2010 | Reply

    • Phil, maybe i dont understand what you are saying, when I do my BCS in Visual Studio I use LINQ to SQL to make my data connectivity, that handles my security context to the Data Store, are you saying you want to use another Data Connectivity method ( Secure Store Object Model) to do that? and how?

      Comment by fabiangwilliams | June 4, 2010 | Reply

      • Hi Fabrian, Do you know any solution for Code Based (Visual Studio) access using Secure Store. I have implemented BCS model using Visual Studio with Linq to SQL to access database, but i wanna use Secure Store credentials in my Linq to SQL connection. Thanks

        Comment by Satya Kanithi | October 14, 2010

      • you can certainly code agianst it, I dont have anything i can share with the public on that one mate, but it could be out there.

        Comment by fabiangwilliams | October 16, 2010

  5. Hi fabiam

    Any ideas as to how I can re-create the Secure Store Service Proxy. it seems that in our farm, someone removed it.
    Thank you

    Comment by Tomas | June 8, 2010 | Reply

    • there are powershell commands for that, see Spence Harbar blog.. or Darin Bishop, or Todd Klindt

      Comment by fabiangwilliams | June 9, 2010 | Reply

  6. Great explanation with easy to follow steps.

    Job well done.

    By the way, I linked to this blog from my site.

    Comment by Tony Yin | June 9, 2010 | Reply

    • Thank you Tony, please feel free to spread the word however you see fit… Cheers mate

      Comment by fabiangwilliams | June 9, 2010 | Reply

  7. Followed the instructions, getting an error when trying to view the external list:

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

    Server application event log says:

    Secure Store Service Error

    The Microsoft Secure Store Service application Secure Store Service failed to retrieve credentials. The error returned was ‘Access is denied.’. For more information, see the Microsoft SharePoint Products and Technologies Software Development Kit (SDK).

    Any help would be greatly appreciated.

    Thanks
    Chris

    Comment by Christopher Fitzgerald | June 21, 2010 | Reply

    • Ok, well if you went all the way through and got that error then i would first check the permissions on the ETC in CA. Make sure the user that is logged on has the requisite permission at the ETC level regarless of what he/she has on the LOB system.

      Comment by fabiangwilliams | June 26, 2010 | Reply

  8. Fabian ,You are the best …

    Comment by umar | June 28, 2010 | Reply

    • It take a community, thanks for the accolade.

      Comment by fabiangwilliams | June 30, 2010 | Reply

  9. How do you store a SQL Server Account and Password in the Secure Store (instead of an AD account)?

    Comment by J.P. | July 15, 2010 | Reply

    • Its the same process except it is not a Windows UserName and Password

      Comment by fabiangwilliams | July 16, 2010 | Reply

  10. Is there a way to setup a series of tables, or do they have to be done one by one? I have a database with a lot of lookup tables, and setting this up for each table seems a bit daunting….is there an easier way?

    Comment by S.P. | October 8, 2010 | Reply

    • Im not sure i understand your question, please rephase with more detail if you can.

      Comment by fabiangwilliams | October 16, 2010 | Reply

  11. Hi Fabian,

    Thanks for a great post!

    I went through your steps and created everything.
    One thing I do not completely understand:

    If I understand correctly you have to add users/set permissions on two occasions, right?:
    1. in the user group (AD)
    2. for the External Content Type (Central Admin)

    However, only the latter seems to be of relevance.
    For if I remove the logged in user from the AD group he has still access to the External List?!

    Therefore: what is the relevance of adding the user to the AD group?

    Cheers,

    Waldemar

    Comment by Waldemar | November 2, 2010 | Reply

  12. Can I configure it if I don’t have Active Directory?
    I’m using windows administrator account to connect to database. I added Windows user account which I use to log in to a SharePoint site.
    In SSS, I set Target Application Type to Group. I added All Users (windows) to Members (later I also added All Authenticated Users).
    Finally, I set my Target Application Credentials to windows administrator because this is the account which has access to my database.

    When I try to add connection in SharePoint Designer, I get an error: Access denied by Business Data Connectivity.

    Do you know how to fix it or there’s no way without AD?

    Comment by mateuszorlowski | November 3, 2010 | Reply

  13. Hello,
    Great post – I have been looking at a lot of blogs over the last two days and this is the best one on the subject.

    Question: It appears that when you use Secure Store that users are only granted Read access to the database. Do you know of a way to give them Edit as well? We wold like to have some of our users edit the SQL data by editing the External List items (the way you can if you use User Identity).

    Thanks,
    Matt

    Comment by Matt | December 7, 2011 | Reply

  14. Hello,

    i gone through the video and its having really a great demonstrration !!! Actually i was having one query regarding single sign on in sharepoint 2010.I have done following steps from my side

    1) I created asp.net membership provider database and added users in it

    2) Created a dot net web application where I have specified connection string for asp.net membership provider databaseand custom provider. I am able to get log in to dot net site using user that I created in asp.net membership provider database.

    3) I enabled form based authentication for sharepoint site so that I can be able to get login with users of membership provider database.

    Issue:

    I need a single sign on for both application for instance if I get login first in sharepoint site and then I go to dot net site it should not ask credentials and vice versa.If i get logout from any application it should ask credential for any applications.

    In SharePoint 2010 the new name for SSO is SSS: Secure Store Service.

    SSS is a claims-aware authorization service that includes a secure database for storing crdentials..

    that are associated with application IDs…..

    Application ID : is your token to access the external application, the token might represents one/group of users….

    These application IDs can be used to authorize access to external data sources.

    I tried with SSS and added target application which was membership provider in my case i simply added membership provider group in “Target Application Administration”.however its not working.Its not enable single sign on for both application.Do you have any idea to accomplish this task ?

    Please let me.I am egarly waiting for your reply.

    Thanks,
    Dipti Chhatrapati

    Comment by Dipti Chhatrapati | December 22, 2011 | Reply

  15. Hi Fabian,

    Great Article! Throws more light on the SSS. Can you please explain for the Outlook Web APP Access.

    FabianPlayPen is created automatically while setting up the Target Application?
    I have DMZ SP2010 website where Claims & FBA is congigured. Need Option to employees login from outside to automatically loggedinto OWA Webparts. I am trying SSS for integrating Outlook.
    Which database would I get populated with all the employees from AD of the company to get authenticated to the outlook web app webpart signed on?

    I am newbie and any help is greatly appreciated.

    Comment by SP Newbie | December 27, 2011 | Reply

  16. Hello Fabian,
    Thanks for your such great contribution.
    I have followed each and every step of your blog.Not just your blog but I have tried 100 different ways to create External
    list in BCS,but I am getting following error again and again I really need your help, I have tried almost error thing to create
    External list on BCS form Google and Bing Search but all my efforts are in vain….

    Still I am getting following 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.

    I am just trying to get 100 odd records so, I don’t think this is “throttling” issue.

    I hope you get time to help me. Thanks

    Comment by Dnyanesh | January 24, 2012 | Reply

  17. Fabian,
    Is there a way to only return items from the SQL table where the UserName in SharePoint equals a UserName field in the SQL table. I only want to return values related to the logged in user. Any help would be appreciated.

    Comment by Scott Simonetta | January 25, 2012 | Reply

    • yes, you can use BCS Filters or you can create a GetList method just for that there is no limit on how many Get List you can create

      Comment by fabiangwilliams | April 30, 2013 | Reply

  18. Excellent one! Mind share somethign on how to deploy the TargetAPPID to otehr server by using PWErShell? Thanks.

    Comment by Allan | March 8, 2012 | Reply

  19. I was looking for this information on many blogs but by far this was the most complete one could over get…Excellent….

    Many Thanks,
    Ricky A

    Comment by Ricky | March 11, 2012 | Reply

  20. You should have added the first screen that when you create it for the first time, you must create a key

    Comment by XperT | March 14, 2012 | Reply

    • ahh, ok sorry about that, Ill update the post accordingly

      Comment by fabiangwilliams | April 30, 2013 | Reply

  21. Hi,
    easy to understand… thanks a lot to making my understanding clear..

    Comment by Y.M.P | May 24, 2012 | Reply

  22. Thank you very much! This was very useful.

    Comment by Vicki | June 8, 2012 | Reply

  23. Thanks

    Comment by Tomas Crespo | April 23, 2013 | Reply

  24. Question – does the service account for the Secured Store Target Application must be in AD (on a domain), or can it be something that was created within SQL Server Management Studio (not on a domain)?

    Comment by Samson | May 9, 2013 | Reply

    • it depends on the scope /size of farm, if this is a multi server farm that need IPC the service account should be a domain account, if you have a single server farm, you can get away with a local account, but no, not a sql account

      Comment by fabiangwilliams | May 9, 2013 | Reply

      • Yes, you can use sql account involved with Secure Store ID and it has nothing to do with farm type. Tricky part is you need choose Impersonate Custom Identity for Authentication Mode in ECT creation. Or you may create ID but later it won’t connect it to the data source.

        Comment by allan | May 9, 2013

      • Good to know, I have never used it myself, I wasnt aware, thanks for sharing.

        Comment by fabiangwilliams | May 9, 2013

      • I have a multi-server farm and tried using both AD (on a domain) and SQL (not on a domain) accounts. Both work but only with a username/password upon a login prompt. After clicking “Add Connection” and providing the “SQL Server Connection” info, you mentioned that “Here you may or may not get challenged for credentials when you click OK. The credentials you put here are or should be your own; assuming that you are in that Security Group that will be mapped to the Impersonated User. If not, then you need an account in that Security Group List.”

        I am getting challenged and don’t understand why. In “Set Credentials for Secure Store Target Application (Group)”, “Credential Owners” is set to “All Authenticated Users”, so I should NOT be challenged. What’s more strange is that once I provide my username/password, my SPDeisgner never challenges me again (even after deleting the connection and re-starting the Desinger). Where does my crendential get stored after the initial challenge?

        Comment by Samson | May 14, 2013

  25. Having read this I thought it was rather enlightening.
    I appreciate you taking the time and energy to put this short
    article together. I once again find myself spending a lot of time both reading and posting comments.
    But so what, it was still worthwhile!

    Comment by Bettie | May 29, 2013 | Reply

    • Thank you, I hope you are also enjoying the SharePoint 2013 content as well.

      Comment by fabiangwilliams | May 29, 2013 | Reply

  26. Excellent post. I’m facing some of these issues as well..

    Comment by Catherine | June 1, 2013 | Reply

  27. Fabian. I must commend on you post,but i am having issue doing a wildcard filtering on the list. How do I achieve this?.
    Thank you.

    Comment by Femi | August 15, 2013 | Reply

  28. very good article for those who don’t know ” abcd ” abour secure stroe service in sharepoint

    Comment by Narasimha | August 26, 2013 | Reply

  29. Hi Fabian, wish I had found your blog 9 months ago when I was setting this up for our application as it probably would have saved me some time. I have SSS setup and working with my BCS app since March. Recently I’ve got some complaints about performance so I fired up the developer dashboard and shockingly found that the SSS call to GetRestrictedCredentials is the main culprit (i.e. if my total Page Request is ~16 seconds, 15 of those are attributed to the SSS GetRestrictedCredentials call). Literally just started investigating and found your blog, thought it might be worthwhile to see if you had any similar issues or thoughts around why the SSS call might be so slow. Thanks!

    Comment by CarlosNeerlos | September 5, 2013 | Reply

    • Actually I don’t without more information but you can probably put some traces on both SQL (Profile) and on your Network to see why. also try Browsers like Firefox / Chrome and use extensions to see if anything pops up there.

      Comment by fabiangwilliams | September 5, 2013 | Reply

  30. In SP2010 designer I keep getting “The Buisness Data Connectivity Metadata Store is currently unavailable”. Per other docs I found I made sure my /headerLimits were removed from the ApplicaitonHost.Config file I ALSO made sure the Business Data Connectivity Service and Managed Metadata Web Service were started (which is not listed in this doc).. What gives?.. I also tried from designer running from XP and Win7.. Still get the same error.. ??

    Comment by jonathan | September 5, 2013 | Reply

    • Ive seen this before where folks are targeting a host header URL rather than the NetBIOS Server name, have you tried going directly to the NetBIOS name if that is an option for you? When I had no choice in the matter I changed my Authentication for the Web App to Kerberos and it fixed my issue then.

      Comment by fabiangwilliams | September 5, 2013 | Reply

      • I found I didnt have the BDC service and hence the backend BDC Db setup, so SP Designer2010 had no place to store all those settings. I didnt do an out of the box install so it just wasent there already (ie I used AutoSPInstaller and disabled the install early on).

        I had to go to CA > Manage Service Application > “New” > and make a new “Business Data Connectivity Service” this sets up the backed Db which this document sorta assume I already had.

        Thanks for the postback. Still once of the best recourses on the Inet.

        Comment by jonathan reininger | September 11, 2013

      • Thank you very much, happy to help.

        Comment by fabiangwilliams | September 11, 2013

  31. Fantastic explaination which I had been looking for….. keep doing the usefull work.

    Comment by Kumar Ayyakkannu | September 17, 2013 | Reply

  32. […] How To: Create, Configure, Consume SharePoint 2010 Secure Store in Business Connectivity Services […]

    Pingback by Blog Update on SharePoint BCS with full CRUD Part 1 of 3 « Fabian Williams SharePoint Blog | October 14, 2013 | Reply

  33. […] How To: Create, Configure, Consume SharePoint 2010 Secure Store in Business Connectivity Services […]

    Pingback by Part 2 of 3-Blog Update on SharePoint BCS with full CRUD « Fabian Williams SharePoint Blog | October 14, 2013 | Reply

  34. Hello There. I found your blog using msn.
    This is a really well written article. I’ll make sure to
    bookmark it and come back to read more of your useful info.

    Thanks for the post. I’ll definitely return.

    Comment by Edward | October 25, 2013 | Reply

    • Thank you, ive updated that post you are on, hope you found that useful.

      Comment by fabiangwilliams | October 25, 2013 | Reply

  35. This is really a good blog. I followed all your steps

    But i am facing an issue while connecting to data source from SPD

    “Can not log on with the credentials obtained from the secure store provider”

    I double checked the database rights on AD account which i am using for the impersonation have db_reader and db_writer access on the database.

    “Is it due to i am accessing it using VPN?”

    Thanks in Advance

    Comment by swapril | December 6, 2013 | Reply

    • make sure you are doing domain/user when putting in the creds in secure store, also make sure thisr creds have permission in the metadata store in bcs ect

      Comment by fabiangwilliams | December 6, 2013 | Reply

  36. Hi fabiangwilliams ,

    The issue was with the VPN. We can’t access the database using SSID by using VPN. Because it will not trust you in the domain. So i tried on the machine which is in trust with the domain and it worked.

    Thanks

    Comment by swapril | December 6, 2013 | Reply

    • Glad you sorted it out, my code is solid :-)

      Comment by fabiangwilliams | December 6, 2013 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,963 other followers

%d bloggers like this: