SharePoint 2010 BCS and Search over anonymous access

Recently a colleague and I labored through an entire day working with the Business Data Connectivity Service (BCS) and getting it to search properly. Our mission? Take a line of business (LOB) data source in SQL Server 2008, encapsulate it as an External Content Type (ECT) in SharePoint 2010, and create a list from that ECT that is searchable via anonymous access over the internet - a combination of scenarios rarely encountered. That list would then have a custom profile page built to view the data. The list (and ECT) should be searchable from the typical search components of SharePoint 2010 such that, when an appropriate result was found, they could click and get our custom view page - over anonymous and authenticated acces.

Sounds easy, right? Been done a thousand times with classic content types in SharePoint, right? Easy to configure the search meta data you say, right?

Brrrrrrrrnnnnggggt.

The BDSC creates an interesting search paradigm. You aren't working with a normal content type. It's simple enough to take a traditional content type and map it's site columns to managed properties in SharePoint search. However, with an ECT you don't get the benefit of a Site Column.

Furthermore, you have to index the BDCS, not a SharePoint list by url filter. So, the content source becomes an issue.

At first, we thought applying a content source for all BCS entities would work. Seemed logical. Just have one and then search by property value from there. However we soon found that this was not practically possible. We found that any time we modified the ECT, we needed to completely delete and recreate the ECT, not just update it and propagate those changes to the list via SharePoint Designer (SPD).

When we needed to add a field, we needed to delete the ECT and completely recreate the External List fielding the ECT. Otherwise the search wouldn't work.

Furthermore, we found that a Content Source in SharePoint search only worked with one ECT assigned to it. The first ECT added worked. Any subsequent ones did not. So, we ended up with a one to one ratio of ECTs to Content Sources in SharePoint search.

In short, to make LOB data searchable via BDCS, we had to do the following:

1. Create data structure (table or view worked fine) using Sql Server management tools
2. Create External Content Type using SharePoint Designer 2010 and save it.
   
   

   Make sure your data access is set appropriately if you need to access it anonymously. The BCS data source has to be configured as Revert To Self to be available anonymously, which is different than the way you must first set it up to initially configure the ECT.

   $apps = Get-SPServiceApplication$bcs = #Do something appropriate here to get the app that is#BCS. If you’re doing this by hand, just type $apps and look#for the Business Data one, then index into it like $apps[i].#If you’re doing it for automation, filter by
   #$_.GetType.FullName (not $_.TypeName, which is localized).$bcs.RevertToSelfAllowed = $true

   Then, alter your data source to select "BCS Identity" - this will cause SharePoint to revert to the application pool's identity for accessing the BCS data source.
   
   
   
   Make sure the app pool account has access to read your BCS entity in the service manager for the BCS. This is configured in Central Administration, the BCS service application. Make sure your app pool account has access to the Sql Server.

3. Click "Create Lists & Forms" button in SPD to propagate ECT to a SharePoint list.
4. Make sure the new External List is showing data. Do not proceed until you've validated it has access anonymously and authenticated.
5. Log into Central Administration and go to Search Administration. Under Content Sources, create a new Content Source. St it to "Line of Business Data", but DO NOT select the "All BCS" entities option. Rather, select ONLY the ECT you just created.
6. Save the Content Source, and run a full crawl of the Content Source. The number of successes should be close to the number of records in your data source (table or view).
7. Search for an item in your data source, and you should be presented with a result that links you to a profile page.

The BCS is much improved over SharePoint 2007's version. There are still some things to work around in difficult scenarios but it can be done.