Setting Up BCS and the Secure Store Service in SharePoint 2010
It’s supposed to be simple to set up an External Content Type with SharePoint Designer. You just create the connection, flick a few settings, and voila, you’ve got external data available in SharePoint. At least every time I’ve seen it demoed, it works really well. The blog posts out there also paint is as a “few clicks and you’re done” thing.
However, it’s not one of those things that’s always simple. In most situations, you have some authentication mumbo-jumbo that you want to work in between the External Content Type and the external data source (in this case it was SQL).
My colleague Roberto Cremonini and I have been working for a client together for a while now. I’ve known Roberto for years and years, since we worked together at Renaissance Solutions together in the mid-1990s. It’s great how relationships one builds earlier in one’s career can continue to bear fruit both professionally and personally over time.
Our goal for this little project was fairly simple. The client has a third-party software package which is used to manage the financial aspects of the business. The package uses SQL Server as its database and there is a table containing information about Partners and another which contains Projects. Think of the classic Client / Project combination you have in a consulting company, and you’ll understand the idea. We don’t want to maintain that data in SharePoint because it already exists in the software package “line of business” (LOB) system.
We need everyone in the organization to be able to read from the list of Partners and Projects because we want to use them in Site Columns which we can add to multiple Content Types. If you think about it, those two pieces of data are relevant for many different types of content; the majority of the work of the organization is done for or with a Partner in the context of a Project.
So off to Central Administration to set things up in the Secure Store Service. Suffice it to say that we set everything up (multiple times) the way we thought were supposed to. What ended up not being clear was what account(s) were required to make things work and how those accounts should be configured.
There are a number of good blog posts giving instructions about how to set this up, including a nice one on Fabian Williams’ (@fabianwilliams) blog How To: Create, Configure, Consume SharePoint 2010 Secure Store in Business Connectivity Services. However, they are all a bit too cookie-cutter and “techie” to be useful, in my opinion. None of them talk about what can go wrong and why, which always ends up sending you down rat holes.
Once you have the Secure Store Service set up (I’m not running through the steps here; see Fabian’s post and quite a few others), setting up the connection in SharePoint Designer is seemingly straightforward. You simply provide the Database Server, Database Name and which Secure Store Application ID (called the Target Application ID in Central Administration, natch) you want to use.
No matter how we set things up in the Secure Store Service, when we tried to connect to the SQL Server Database in SharePoint Designer, we got the error “Cannot logon with credentials obtained from Secure Store Provider.”
We found – in our environment, at least – that a domain account wouldn’t work. Instead, we created a SQL Account with SQL authentication and things worked right away. See this useful post SharePoint 2010 using BCS with SQL Server database by Arjan Fraaij.
Following this post, we created two external lists for Partners and Projects in our SharePoint site and we thought we were done. Instead, we realized that our admin account was the only user who could see the External List content. All other users would get an error that read more or less as “Access Denied by Business Data Connectivity. Correlation ID: …”. After some trial and error, we solved the problem by following a few additional steps:
- In Central Administration, go to Manage Service Applications and select Business Data Connectivity Service.
- From the drop down menu choose “Set Permissions”.
- Add All Authenticated Users and set permission to Execute
- In Central Administration, go to Manage Service Applications and select the Secure Store Service used to logon and from the drop down menu choose edit.
- Click next twice until you get to the page where you see Administrators on top and Members below.
- Add All Authenticated Users to Members.
- Voilà
These settings may be too open for your environment, but for this client we wanted everyone to be able to read from these lists. The majority of them have access to the LOB system in some way, and the Partner and Project information is known to everyone in the organization.
IT security would cringe when you allowed SQL Server login account in leu of a Windows domain account. Maybe Kerberos wasn’t working. Maybe FIM wasn’t configured correctly. Is the secure service store account able to read the master database for the LOB sql server? Since this was impersonation, maybe token impersonation was malfunctioning.
Brian:
Very good questions. This is a very small organization so “IT Security” is basically us. There’s a small company that takes care of infrastructure and they set up SharePoint 2010 a few years ago. We’re building on top of that install, and we don’t know exactly how they have done things. We’re learning as we go.
M.
Hey Marc,
Another common error I have run into when creating the external list in SharePoint is the ever so helpful “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. Correlation ID…..” I’ve found that this is most often caused by not having some sort of filter in the ECT, thus having over 2,000 items. The best ways I’ve found to fix this is to create a view in SQL that limits the number of items to less than that or to create a filter/view in the ECT before creating the external list in SharePoint.
Beth:
Thanks for the reminder about this. To start, we’ll be under the 2000 item limits, but we also will want to set up some filters if only to make using the entity picker a bit more palatable.
M.
The first thing that came to my mind is that you’re dealing with a dual-hop authentication using Kerberos via active Directory. If you are passing authentication fro one system onto another system (two hops) then you need to set up the Service Principal Name (SPN) of the account correctly. See for example http://blogs.technet.com/b/askds/archive/2008/06/13/understanding-kerberos-double-hop.aspx and http://blogs.technet.com/b/taraj/archive/2009/01/29/checklist-for-double-hop-issues-iis-and-sql-server.aspx
Jens:
Thanks for the thoughts. As noted in one of my prior comments, the fact that we don’t have full clarity on how the environment was initially set up complicates things. I’ll definitely check out the links.
M.
Hi, Iv also found the “Cannot connect to LOB system” error. and I get the logs:
Error while executing web part: Microsoft.SharePoint.SPException: Cannot connect to the LobSystem (External System). —>
(Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.)
Stack Trace: at Microsoft.SharePoint.BusinessData.SystemSpecific.Db.DbConnectionManager.Execute(Object[] args)
at Microsoft.SharePoint.BusinessData.Infrastructure.WindowsAuthenticator.ExecuteAfterLogonUser(Object[] args, ISecureStoreProvider ssoProvider, String ssoApplicationId, Boolean useSensitiveSsoCreds)
at Microsoft.SharePoint.BusinessData.Infrastructure.WindowsAuthenticator.ExecuteAfterLogonUser(Object[] args, ISecureStoreProvider ssoProvider, String ssoApplicationId)
at Microsoft.SharePoint.Busine… 1b4725a1-c556-47d0-8e70-98913f05b7c0
I set up the SSS service and the BDC service, and grant all my permissions. I also think the credentials I set in the SSS has the DB_admin rights on my database…I really dunt know why this happens….
cookiewyx:
I arrived at the conclusion that there is a lot of variablility in all of this and it just isn’t as simple as setting things up from the recipies. Unfortunately, I think it becomes a joint effort with the developers, network managers, and potentially power users (we do this in SharePoint Designer, after all) at the very least. Where those roles fit into one person, it’s so much easier.
M.
I actually came across this post because WordPress told me you linked to mine. So, do you want me to do a blog post about the Perils of BCS, I can do that :-) but Id probably run in the trap of not knowing what someone else will do since I know the topic, but I can do a “Gotchas to look out for in BCS” but that has changed now since 2013, nonetheless its a good Idea, Ill take the challenge.
Fabian:
My little experience definitely told me that the recipies work about as well as baking at high altitude. You’ve got to figure out your own environment’s foibles.
Aren’t you glad you’re back on WordPress so that you know when I mention you?
M.
Hi Marc, I’m looking for a way to use an external content type to populate a drop down menu for a SharePoint custom list. Infopath is not an option at this time. We have the BCS connection up and running, and can add the column to the list, but the control only ever acts like a managed metadata selector. I was hoping that SPServices might have a path for me to follow. I’ve looked through the documentation, but I was unable to find anything that addressed this. Do you have any suggestions? Your time and contributions to the community are very valuable, keep up the good work!
Like many things in SharePoint, BCS columns use their own unique controls. However, you should be able to query the external list using GetListItems and create your own “control” client side, perhaps for a Single line of text column.
M.
Hello Marc,
I am getting following error when I am setting up the ECT Type – Creating “Read List” operation
The Remote server returned an error: (401) Unauthorized.
I have dbo permission o the SQL database/table
BCS Service App
Administrator -FControl
BCS SQ – Permission with FControl
Not sure where the authorization is failing. Please guide me.
Thank you
Husain
Hi Marc,
I have created external content type on SharePoint Online (Office 365) site. Initially, the connection is made with the default port of SQL Server i.e. 1433 and the External List working fine however we are getting too many anonymous hit on SQL Server default port as this is production server due to security reason, we have disabled the default port and configured the custom port.
After disabling the default port and configuring the custom port, I can able to create ECT from SharePoint Designer and once ECT is created, I have successfully created the required operations. When I created list from this ECT at that I’m getting an error message that Unable to render the data. If the problem persists, contact your web server administrator.
Is there any one who face the same problem and find any solution for this issue? Any help will be appreciated.
•Add All Authenticated Users to Members.
this solved our issue with BCS & SSS. thanks a ton :)