See Database (SQL / WCF) data in Sharepoint - Implement BDC and Search

Scenario - we have an SQL DB with a lot of data, and we want to put it in a sharepoint site, and even more we want to show it to our Anon user (wwwhhooooooo!!!)
*this article was tester in SP 2013 but is valid for 2010 too.
*BDC = Business Data Connectivity
so minimal background:
1. Anon users cannot see anything but pages. we shall see how to overcome this.
2. BDC is a magic tool that with it you can introduce you database to sharepoint and teach them to play toghether.
3. you can search and index the BDC data.

conclusion: we want to index our BDC and with Content Search WP or Rest show the data to the anon users, so lets go:

1. Start the services:
we must make sure that the sharepoint BDC and search service are up: Central Admin -> Application Management -> Manage services on server:

2. Provision service applications:
the fact that the services are now up and running means that there is a platform ready to use, we now need to make instances,  Central Admin -> Application Management -> Manage service applications, click New and provision those :
*if you have instances of those services you may as well use the o.c.

now go and click the BDC service and inside click on the ribbon click Configure, and put there you host web, i.e. your lovely website domain (http://mysite).
in any way, make sure your new services apps. are connected to you web application, Central Admin -> Manage web applications -> mark your web app -> on the ribbon 'Service Connections', if your new services apps. are not there then choose custom and rebind.

3. Create External Content Type:
the way SP see your data is in the only way he knows: lists and columns. so we give him that exactly by using SharePoint Designer, rather easly btw - open the Designer and open your target website (if you didn't have a dedicated one by now, time to create it :D )

*REMINDER - any user that should contact the external data should have permissions there, so for example your SQL DB should give permissions to the search (sp_searchscv) and iis (IUSR). so if u didn't do it until now, its a good time.

there you have on the left nav. click External Content Type, and then on the ribbon top left click External Content Type. give it a nice name. then click on the most bottom link that says 'Click here to discover external data sources and define operations'.

click 'Add Connections' and connect to you DB, choose a table or view, right-click and 'Create All Operations' (unless you cant for some reason).

click Next, and choose the Key columns and check the 'Map To Identifier' checkbox and Finish. you can play with the Display Names of the columns as needed.
Save your work.

if you want to see private items in ur web then click on the ribbon Create Profile Page (u wont be able to browse them in anon, so if its purely for anon u don't have to).

BTW you can do just the same with a WCF, the only difference will be that you will have to set each operation explicitly, it works great.

4. Setting permissions to you External Content Types:
back to Central Admin -> Application Management -> Manage service applications -> you BDC -> you should see your new External Content Type, if you hover it u'll get a little drop down and click set permissions. I think that the real user that will activate it in the end will be IUSR (the IIS user) but I am not sure, I just added All Authenticated Users.

in case you want to see it in action, you can create external lists in your web, activate web feature ''Team Collaboration Lists" (רשימות של שיתוף פעולה בצוות), then create new external lists based on the new content type and see it in action.

5. Indexing all that:
Central Admin -> Application Management -> Manage service applications -> you search-> Content Sources -> New Content Source :

*make a new content source for each table, since there is a problem (bug?) choosing Crawl all, and I think its a 2013 only thing.

but id u'll crawl it  wont have any managed props to retrieve. go to Search Schema, New Managed Property, and create one for each of your colums. a nice trick by Jorge is to make a prop with all the cols for ease of search. might be a bit of a hard work to create a new Managed prop for each prop, but you can always add them to existing managed props.

and...start a full crawl :). check the log that you got no errors, and if you do its usually permissions problems for sp_searchsvc.

6. Put it on a page:
so lets put it in a Content Search WP somewhere. the easiest way for me to get all the results for this specific content is to go to my search service, click search schema, find Content Source, and set is as Searchable, so I can put in my query ContentSource:MyContentSourceName.

i'll set the CSWP like this:

7. See it in anon:
now publish that page and open it in the anon site (and in an anon browser - make sure u don't see the ribbon)

8. Customization:
for customization we have 2 options, 1st is to learn how to query that with the Rest, and then we can use it anywhere (say with angular :D ).
here is a easy tutorial with a PS for it http://blog.mastykarz.nl/configuring-sharepoint-2013-search-rest-api-anonymous-users/.
if you get a 500 than make sure the .xml file is saved in UTF8 format, or check ur ULS logs.

var url = http://yourAnonSite/_api/search/query?querytext='ContentSource:TestTable1_WCF'&selectproperties='TestTable1IntCol'&queryTemplatePropertiesUrl='spfile://webroot/queryparametertemplate.xml';
jQuery.ajax({  url: url,
    headers: { "accept": "application/json;odata=verbose" }
})

as search is, the data is in a path like this usually : "d.query.PrimaryQueryResult.RelevantResults.Table.Rows.results", which will give you and array, with each one "results[0].Cells.results" is another array for each colums (well, managed property)


the 2nd is to use Display Templates:
well, to be continued...


That's All Folks!!


u also might need this PS:

$bcsServiceApp = Get-SPServiceApplication | where {$_ -match "ur bdc SA name"}

$bcsServiceApp.RevertToSelfAllowed = $true;

$bcsServiceApp.Update();