CRM 2011 and SharePoint 2010 Integration - Part 4

Article
07/28/2012

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

1. Accountid

2. accountnumber

3. address1_city

4. address1_country

5. address1_line1

6. address1_line2

7. address1_postalcode

8. address1_telephone1

9. description

10. emailaddress1

11. fax

12. numberofemployees

13. revenue

14. websiteurl

15. name

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.

Click Finish

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 Next

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)

2. accountnumber

3. address1_city

4. address1_country

5. address1_line1

6. address1_line2

7. address1_postalcode

8. address1_telephone1

9. description

10. emailaddress1

11. fax

12. numberofemployees

13. revenue

14. websiteurl

15. name

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 anand.nigam@hotmail.com

Have a fantastic day

Comments

Anonymous
July 30, 2012
Anand has come up with some cool articles on the following topics, which I am sure will be useful to
Anonymous
November 08, 2012
Thanks for the article CRM 2011 and SharePoint 2010 Integration. Regards www.sharepoint.inf4web.com
Anonymous
November 20, 2012
Great post. Thanks for sharing.
Anonymous
July 23, 2013
Hi, in your example, if Dan doesn't have access to those CRM records, then he will still see them in search? Have you looked at doing security trimming of the results?
Anonymous
September 30, 2014
Introduction
In October 2014 I will be giving a talk to the Geneva SharePoint User Group about the
Anonymous
September 30, 2014
Introduction
In October 2014 I will be giving a talk to the Geneva SharePoint User Group about the integration options between Dynamics CRM and everyone’s favourite document management repository...

Share via