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