Tags- crm from sharepoint, sharepoint crm integration, sharepoint crm search, SharePoint Enterprise search CRM 2011, CRM 2011 Sharepoint search
Hi SharePoint Folks,
I had too many things to sort out to get time for this. All right then, fasten your seat belts and get ready read further. So this is going to be a fast and furious ride.
Ok just to be on the same page – here is the index
Part 1: Introduction and CRM 2011 – Document management Integration with SharePoint 2010
Part 2: Reporting CRM data in SharePoint using Excel services
Part 3: Publishing CRM entities in SharePoint ( going to be there soon).
Part 4: Search CRM entities from SharePoint Enterprise Search (This post).
Here is what we are going to do, Below is my CRM 2011 with sample data populated.
Our Goal is – “We want to perform search in SharePoint Search Center and I MUST get my CRM 2011 entities as we see above. Below is our lovely SharePoint Search Center” , to be precise we would search CRM entities like Accounts and Opportunities from SharePoint.
To achieve the above goal here is what we need
1. A SharePoint Site.
2. A CRM deployment.
3. This Post
Overall steps are
1. Create a BCS model / External content type ( here we will decide where and how to speak to CRM system , What to fetch from CRM system)
2. Once ECT is done, We configure the SharePoint Search Service application to crawl the data coming via BCS model we created in the above steps
3. Information on Securing the above setup, usually for your production implementation. [updated entry] Sun 07/29/2012
Step 1 –
(1. Create a BCS model / External content type ( here we will decide where and how to speak to CRM system , What to fetch from CRM system))
On SharePoint Server
Here we will create an External content type that will connect to the CRM backend database and fetch us the data we are are interested in, we are looking to search Accounts and Opportunities. If think closely what attributes of Accounts and Opportunities would the end user be searching for? ok no answers now we will look at it when we reach to that step.
Ok Open the SharePoint site in SharePoint designer
Here in box labeled 1 – Enter the name that you want for the ECT (External Content Type), On box 2 – Click on link next to External System.
Once you click there, Click on Add Connection then select SQL Server Click OK
Next you enter the Database server and Enter the CRM database ( NOT THE CRM CONFIG DB). In my case my database server is – DC, the database name is Fabrikam_MSCRM. Click OK to connect.
Once the connection is successful expand the Views section, look for FilteredAccount.
Now Right click on FilteredAccount and Select New Read Item Operation. (why did i chose Filtered Accounts when there is an Accounts view ? here is the reason)
Click next on the wizard like screen, then select accountid and check box next to Map to Identifier, Click Next
Uncheck the box Data Source Elements ( because we don’t want all the info of Accounts, we will select what we want individually)
Now For a minute forget about everything and think what your users are most likely to discover CRM accounts with, for example, i am an end user most probably i should be able to search a CRM account by – Account Name, Account Number, Email address, website address, primary contact name…etc etc etc.
Now imagine in SharePoint search box, you type in an email address and you come up with the CRM account info on SharePoint page, What all information for that specific account should you be able to see ? my guess is the above attributes PLUS Primary contact name, address, city, annual revenue, number of employees, description
below is the information i can get from the views, these are my options, Its huge 🙂
Now Map these information in the CRM Account Entity UI
Now most of the UI data corresponds to the fields i showed you above this. which means, most of the info in the UI is accessible via view, which in turn means i can pull it from BCS (ECT), which means My users can see those info in SharePoint search result or in SharePoint profile page (don’t worry about profile page for now).
Now I have decided that I would fetch the below information from CRM views:-
Come back to reality now, Lets proceed. Now that we know what data we want from CRM accounts we will select those attributes
First task is to select the accountid (which is UNIQUE) and check map to Identifier
Second task Check mark all other columns we decided.
you will see a dialog like this
And a new Read Item Operation is created
Now again right click on FilteredAccount and this time select New Read List Operation
Click Add Filter Parameter
Click in the sequence shown and set the values as showed below.
Now after clicking OK, In the Default value box type 100 ( you can use any number, this number defines the result set returned. you should be careful in selecting this number, if you enter 1 million here BCS will ask CRM to give 1 million records, you can imagine the load CRM DB server would have. 100 appears to be decent amount.)
Click Next and we see the same familiar screen, 1st select accountid and Check Map to Identifier, Read only and Show in Picker.
2nd – select the same fields we selected in previous operation
1. accountid – we already have selected this (skip this field)
Now click Finish, the page should look like this, notice the new operation gets listed
Now Save the ECT
Once Save is done, now its time for testing if ECT can fetch the info from CRM, for this click Create Lists and Forms, give list a name then click OK
Now open the sharepoint site in brower and open the list and verify if you can see the CRM info over there.
And we see the CRM info just fine, Incase you face error here its most probably due to the permissions of the logged in user and the explicit permission on of the farm admin on the BCS Model. You can play with it in central admin.
Even during testing if you face error accessing the site just use the permissions i have set for my lab on service account. My service account of SharePoint ( spfarm) has access to the CRM as well as the database). I have given the following permission
Notice – that spfarm is my SharePoint and CRM service account and spfarm has all the rights on metadata store. (NOTE- you don’t have to explicitly set object permission if you have granted permission here and also Check the box on “Propagate permission to all BCD models…..”)
Anyways now that the CRM info is showing up in SharePoint list, we are confirmed its working. NOW Delete that list, we don’t need it.
Now Open central admin and navigate toe BCS service application and Click Configure
Enter a site url here, This site would be used to host the BCS pages, it would a simple page not a subsite, I just used the root site collection. Click OK
Come back to SharePoint designer and click Create Profile Page
the pages will be created now, This page will be used when a user clicks on the search result,
Now from the list of fields, select name and then click Set as Title
Save the ECT again, What we did just now was to set the CRM account name as the title of the profile page
Now Click on Create Profile Page ( Yes, I forgot to do this before 🙂 ) Click yes on the confirmation box
Step 2 – Still On SharePoint Server
(2. Once ECT is done, We configure the SharePoint Search Service application to crawl the data coming via BCS model we created in the above steps)
Now our goal is simply to Configure the Search service application to search the CRM entities via BCS model we created above.
Open SharePoint Central Admin > Service application > your search service application
Click on Content Sources
Fill the details as shown below
in the bottom check on Start full crawl… and click OK
Notice that the Crawling (reading CRM entities and creating an Index) is taking place in SharePoint). All we have to do is now wait for Crawl to complete.
Also for troubleshooting purpose you can look at the crawl logs ( crawl logs can be accessed by hover mouse on CRM Entities> View Crawl log), Most common issue is insufficient permission on crawl account on CRM DB. Minimum permission required for this solution to work is READ.
here is my Crawl logs showing a successful crawl, More importantly there are 0 errors, even if you have 1 error please investigate what is it.
Time for testing !!
Now I did a search for an CRM account “A Store (Sample)” and SharePoint gave me all the CRM + SharePoint results 🙂 I am a happy guy now. Lets open one of the results
Here we see the CRM details in a clean SharePoint page, BTW this page is the profile page we created/set.
Now keep in mind the above page is a REAL TIME info from CRM System. To prove that keep an eye on the Name attribute which is – A Store (Sample) ( in the bottom of the page)
Now once i update the details in CRM application and refresh this page it fetches the new updated value there is no cache, no old data problem 🙂
[ Start of updated section] Sun 07/29/2012
3. Information on Securing the above setup,
usually for your production implementation.
Alright now that these two guys (CRM and SharePoint) are speaking, you must be aware about the security implications given the setup we used, I just used the most simple technique and it worked. So here are few ways to Ideally do this.
1. This is the screen where we connect to the CRM database, If you notice closely I did not specify any credentials to connect to the DB, Ideally You can setup a Application ID in Secure Store Service (How to setup this? Click here) , And put the Application ID in Box 3 (in below pic), this way you don’t grant SharePoint admin any special privileges on CRM system. Also do remember CRM system knows ( not the SQL Server only) which user has what privileges, so if i grant an account X permission on just sql db of CRM, you will se no results, because the Filtered views we are using are Security Context aware and will only show content if the calling account has permission to it. So best way is to grant user access on the CRM System (most probably as reader) and also on SQL DB (reader).
Step 1- Open Secure Store service and create a new Application ID
Fill in the details, Click Next
This is fine Click Next
here in the 1st box – enter who ever is owner of the target app, can be crm admin and SP admin, in the 2nd box, enter who can use this Application ID- I used all users because, i want all of my company users to be able to search and see the details, if you have restrictions on who can see the crm info, create a security group in AD and enter that here. Click OK
Now dropdown on the newly created Application ID, and Set credentials, Enter the account which has at least READ permission in CRM site and DB.
Now Edit the ECT model in SharePoint Designer
Change the 1 and 2 box as shown,Box1 – here we specified the type of credential, Box 2 is where we specified use this Application ID credential to speak to CRM db. Click OK and Save this config, create an external list of this ECT and verify if information is visible for sharepoint service account also to end user account.
below is the end result, Dan- end user can see the CRM info too,
[End of updated section]
Enhancements – Open the CRM form directly from search results follow this – CRM 2011 and SharePoint 2010 Integration – Part 4 (Enhancements)
Hope I was descriptive enough, Incase you feel you need more info on any of the steps, please let me know and it will be fixed soon.
Finally I want to say big sorry to Brent Tenney , Pam Jensen , Brian L. Tenney , Sanjiv Sharma, Nick Verhangen, Bill Kelly, Erwin Sanders, Colin V, Thomas Binder to keep you waiting.
Important info for Microsoft Partners – If you are an Microsoft Partner and would like to have a workshop on any of the topics included in this blog please send email directly to me at firstname.lastname@example.org
Have a fantastic day