SharePoint Search – Error While Crawling LOB Contents

Recently I was configuring search to access my SQL Server data via an external content type for a demo I was presenting.  Once the content source was configured I ran the full crawl to populate the index with the data from my external source.  After about a minute the crawl was marked complete.  So naturally I am going to want to test it and make sure my data is pulling through.  The external data is a basic asset database so I did a search for one of the manufacturers I knew was in there.  Received the dreaded “Nothing here matches your search” message.

Well I know there is data in there so obviously something went wrong.  I know what is wrong as I have done this before, but I want to go through some of the troubleshooting steps you can do to help determine the problem.

Troubleshooting Steps

First off, check your crawl logs.  If the data is there, the content source exists and you are not getting anything back then chances are things didn’t index properly while crawling.  To check you logs, go to Search Administration in your Central Admin.  Click on Crawl Log from the Quick Nav section (you can get to the logs from the content source as well, but we already know the crawl is done).  From there I could see that one success occurred but also one error.  Well that isn’t right.

Click on the Error Breakdown from the menu above will give us a bit more detail.  One message exists that said Error while crawling LOB contents.  Clicking on that gave us the more detailed error message you see below “Error while crawling LOB contents. ( Error caused by exception: Microsoft.BusinessData.Infrastructure.AccessDeniedException Access denied by Business Data Connectivity.”:

That’s a lot of clicks to find out exactly what happened.  So ok, this looks like a permissions issue.  Now when I am dealing with a potential security issue what I usually do is give the highest level of access first to confirm that is in-fact the case (don’t worry, I don’t leave it that way) and then determine the best access to set things at.  But in this case I didn’t do that because there is only one account that needs to be added, the Search Crawl account.

I accessed the BDC service application in Central Admin (because that’s where the error message said it was getting denied from).  Once there, to check the permissions you actually have to edit the ECT’s object permission.  To do this, select the ECT and then in the ribbon, click on Set Object Permissions:

Taking a look at the access only the group that I granted access to the demo had access to the ECT:

For search to be able to access data through an ECT, the crawl account needs to be granted access to the ECT.  It only needs execute access, but it needs to be there:

Ok, so now that this is updated, I ran the crawl again.  Same result at the higher level.  One error.  But still a success, because we have a new error now.  “Error while crawling LOB contents. ( Error caused by exception: Microsoft.BusinessData.Infrastructure.BdcException The shim execution failed unexpectedly – Access is denied to the Secure Store Service”

Now the Secure Store is blocking us.  I bet you I know why.  To access the security permissions of the Secure Store, access it via the Service Applications.  Select your Application ID and click Edit.  Secure Store is different than other services in that you need go through all the configuration options before you can get to the permissions.  So press OK until you get to the permissions screen.  Sure enough, I was missing the crawl account here too.  Simply add it and click ok.

Now I believe that should be everything.  After running another full crawl there was nothing but successes across the board:

Performing a search gets me the results I am expecting:

Hope this is of some assistance if you run into issues setting up BCS and search in your environment.

 

Thanks for reading!