Connecting SharePoint Online and CRM Online using BCS - 2013 Edition

Update 2nd February 2015: For using this article with CRM 2015, please follow steps mentioned by Andrew Robinson in the comments section.

This blog post is a simple step-by-step walkthrough on connecting SharePoint Online (SPO) to CRM Online using the Business Connectivity Services (BCS). This can be useful in a number of situations including self service portals, executive dashboards, etc. The end goal for this post is to have CRM account information displayed within SharePoint as illustrated below (nothing fancy) but you can easily extend this sample to include additional scenarios such as pulling additional entities/tables, create/update CRM data, charts/dashboard, mashups with ERP and much more.

Note: SharePoint Online in this example uses SharePoint 2013 and CRM Online is the December 2012 release. Steps are subject to change for future releases. This walkthrough uses the App model for SharePoint instead of the classical BCS settings managed in the admin center.

Architecture of the solution

Below is a quick diagram showing the architecture of the solution we’re going to walkthrough. As you can see from the diagram below, we do have a shim sitting between CRMOnline & SPO. The reason for us to have a shim/proxy web service is because, external authentication is not enabled on CRM Online’s REST endpoint today.  CRMOnline’s REST endpoint today is only available for use by JScript and web resources within the CRM application however you can expect that limitation to change in the future.

Enough with the talk, let’s get to the code.

Building the Proxy OData Service

1. 1. Download and extract the latest CRM 2011 SDK if you don’t have it already. If you're using CRM 2013, please ensure that you're following this article with CRM 2013 SDK. While you could build the proxy web service in several ways, we’re going to use an approach that creates a REST/OData service that involves very minimal custom code!
   2. Once you have extracted the SDK, goto the folder “\sdk\walkthroughs\portal\webappwalkthrough\” and open the webappwalkthrough.csproj project using Visual Studio (2012 in this case)
   3. Open the web.config file and change the connection string to your CRMOnline credentials as shown below. In this post, we’re using the same identity for all CRMOnline calls and we are not impersonating the SharePoint user.
      
   4. Open the CrmData.svc.cs file and modify it as shown in the snippet below. Essentially we’re modifying the code to make the OData endpoint just pull accounts and contacts from CRM instead of pulling everything.

1. 1. Right-click on the project and “Add Reference” to the Microsoft.IdentityModel.dll (v3.5). Make sure you set it’s copy local property to true.
       
      
   2. Set CrmData.svc as the start page and hit F5 to verify that the OData endpoint is working locally. You can browse to https://localhost:<devport#>/CrmData.svc/ContactSet to verify that the proxy OData is indeed pulling data from CRM Online.
      Note: For the purposes of quick illustration, the code in this blog post doesn’t apply any security on the proxy OData service. Please refer to Marc Schweigert’s blog post and Steve Fox’s BCS security post for details on implementing security for proxy services.

1. 1. The service now needs to be deployed publically in order for SharePoint Online to consume it. Here, we’re going to deploy the OData service to the *free* Windows Azure websites. Once you have signed up for Windows Azure, login to your Azure account and hit the “New” button followed by the steps illustrated below to quick create a website. Hint: Use the region closest to your CRMOnline datacenter. The unofficial way of finding this out is documented here .
      

1. Once the site has been created, download its publishing profile from the portal.
   
2. Back to Visual Studio, right-click on the project and click “Publish”. In the publish dialog, choose Import –> “Import from a publish profile file” –> browse to the publishing profile you downloaded earlier –> OK. You can run through the next few steps or hit publish button directly.
   
3. Once the site has been published, browse to the public URL for the service (say https://yoururl.azurewebsites.net/CrmData.svc/AccountSet) and ensure that the endpoint is able to pull data from CRM Online.
   

Creating the SharePoint Online App

Now that we have the proxy OData service up and running, it is time to create the SharePoint App.

1. 1. In your Office365 admin center, click the Admin dropdown followed by SharePoint. Create a new private site collection with the “Developer Site” template.
      
   2.          
   3. Open Visual Studio as admin or better yet follow this hack to always run Visual Studio 2012 as admin. If you’re going to spend a lot of time building SharePoint apps, the hack will save you a lot of time and pain.
   4. Create a new “App for SharePoint 2013” project.
      
   5. Enter a name for the app, the URL of the site collection you created earlier and choose “SharePoint-hosted” followed by clicking the Finish button.
      
   6. If Visual Studio prompts you for Office365 credentials, enter them so that the project is connected to your SPO site.
   7. Right-click on the project –> Add –> “Content Types for an External Data Source”.
      
   8. Enter the URL for the custom OData proxy running in Azure website and specify a name. Click Next.
      
   9. Choose both AccountSet & ContactSet and hit the Finish button. Visual Studio automatically generates the ECTs (External Content Types) for both accounts and contacts.
      
   10. Within Default.aspx (under Pages), change the code within the head and body sections as listed in the below snippet. Essentially we’re creating the links for account and contact ECTs along with a placeholder for some custom javascript. Make sure that the version of jQuery referenced in the code matches the one within the Scripts folder of your app.

1. 1. Next up, let’s write some jQuery to select the AccountNumber & Name and display it in a simple HTML table (sorry web designers)! Open the App.js file (under Scripts folder) and replace it with the code below. If you’re familiar with javascript and jQuery, the code should be pretty easy to follow.

1. Right-click on the project or solution and click Deploy. Visual Studio automagically installs the app to your SPO developer site.
2. Open the SPO site and you should see your app under “Apps in Testing”. Click on the app and make sure it works as expected.
    

Now that you have the base connectivity between CRMOnline and SPO through BCS, the possibilities are endless. You can bring data from other CRM entities, apply security trimming, have users update CRM data directly from SharePoint, use highcharts.com for pretty charts and dashboards, mashup CRM data with other OData data sources and much more depending upon your needs. If you end up building interesting SharePoint-CRM apps, please do tell us through comments below.

Comments

• Anonymous
  May 13, 2013
  Hi Girish, Thank you for the detailed walk through of setting this up. It will come in handy and I plan to check it out in the next few days. The unfortunate part is the number of steps and the need to write and compile code to do this. It really should be as simple as create a SP list, enter URL and authentication credentials for connection to the service, select the entities/fields and be done.

• Anonymous
  May 13, 2013
  Hi Girish, Thanks for this wonderful post. We recently implemented the same in one of our projects. For CRUD operation we had to implement IUpdatable Interface. nishantrana.wordpress.com/.../integrating-crm-2011-online-and-sharepoint-2013-online-using-bcs-odata-proxy Regards, Nishant Rana

• Anonymous
  May 14, 2013
  Nishant, many thanks for sharing your article. It is great to hear that such scenarios being implemented out there. Would love to hear more and perhaps even create a case study.

• Anonymous
  May 14, 2013
  The comment has been removed

• Anonymous
  May 23, 2013
  Girish, one note on building the OData proxy: If you don't specify the EntitySet, and leave it as *, the proxy service will display properly in the browser, but Visual Studio will not accept it when trying to use it as an Odata source. There is apparently a size limit on the OData source that Visual Studio can read, as you can include half of the EntitySets in the service, and it will still work, so the limitation is somewhere more than half the size of the OData endpoint.

• Anonymous
  May 23, 2013
  Dan, That is correct. Looks like the size restriction is around 1MB. I'm working with the SharePoint and Visual Studio tooling team to verify this and also perhaps influence them to increase this limit to make our lives easier!

• Anonymous
  May 24, 2013
  Girish, I gave this a try & it worked but came across a caching issue. If the data is updated in CRM the OData source is not refreshed. I believe this is something to do with having to set the MergeOption to NoCaching but my attempts to set this value had no affect. Can you try this on your system and verify & then let me know how to resolve this issue. I see that DataServiceContext has a MergeOption and so does Dynamics in OrganizationServiceContext. I guess this needs to be set in the Xrm.cs somewhere or as an override?

• Anonymous
  June 12, 2013
  Hi Girish, Hope you are doing well. Recently implemented the below in one POC.  Thought of sharing the same. nishantrana.wordpress.com/.../integrating-crm-2011-and-sharepoint-2013-using-bcs-wcf-service-crud-operation Regards, Nishant Rana

• Anonymous
  June 13, 2013
  Thanks for sharing Nishant. Much appreciated.

• Anonymous
  September 18, 2013
  Greetings Girish, Thank you for your blog on "Connecting SharePoint Online and CRM Online using BCS - 2013 Edition". My organization and I are new to MS CRM Dynamics.  We are using it to connect our MS Dynamics CRM Online Data with MS SharePoint Online (Office 365).  Through your instructions, we have been able to connect to built-in entitties like "Accounts" and "Contacts", however, we have not been able to connect to "custom entities". For instance, we have built an entity to store contract data called "bpt_contract".   We thought that we would be able to simply modify the code in CrmData.svc.cs from: config.SetEntitySetAccessRule("AccountSet", EntitySetRights.AllRead); to the following: config.SetEntitySetAccessRule("bpt_contractSet", EntitySetRights.AllRead); however, we are getting an error: An exception of type 'System.ArgumentException' occurred in the System.Data.Services.dll but was not handled in user code.   Additional information:  The given name "bpt_contractset" was not found in the entity sets. Does this "call" work for custom entities? Could the error be due to the state of the entity which is currently "Unmanaged"? Do we need to publish these custom entities first? Any insight that you can provide is appreciated. Best Regards, Lawrence Hibbert

• Anonymous
  November 10, 2013
  Thank you Girish! This was exactly what we needed. We followed your steps successfully to connect SharePoint 2013 Online and CRM Dynamics 2011 Online. It worked great!!! What would have to be modified for this to work with the brand new CRM Dynamics 2013 Online? Our client just switched to 2013 and the solution that you provided to connect to 2011 does not work. Thank you! Larry

• Anonymous
  January 14, 2014
  Thank you Girish! This was exactly what we needed. We followed your steps successfully to connect SharePoint 2013 Online and CRM Dynamics 2011 Online. It worked great!!! What would have to be modified for this to work with the brand new CRM Dynamics 2013 Online? Our client just switched to 2013 and the solution that you provided to connect to 2011 does not work. Thank you! Larry

• Anonymous
  May 13, 2014
  The comment has been removed

• Anonymous
  June 10, 2014
  I've done this against CRM 2013. Be sure to download the 2013 SDK/WalkThrough instead of the 2011 linked in the post. www.microsoft.com/.../details.aspx

• Anonymous
  June 11, 2014
  Thanks Joakim! Great to know that using 2013 SDK makes this sample relevant again with CRM 2013.

• Anonymous
  July 29, 2014
  The comment has been removed

• Anonymous
  September 02, 2014
  Girish, Any update from the Visual Studio team on the OData endpoint size limit, or from the CRM Online team regarding allowing external authentication for REST?

• Anonymous
  September 03, 2014
  Hi Dan, Regarding external Auth for REST, yes, you can authenticate to REST endpoint now using OAuth: blogs.msdn.com/.../use-oauth-to-authenticate-with-the-crm-service.aspx. Regarding SharePoint/VS, unfortunately I haven't had a chance to revisit it as my role at Microsoft has significantly changed.

• Anonymous
  February 02, 2015
  Hi Girish Thanks for your post, I found it really helpful, being new to CRM dev. I'm following your post using the 2015 SDK and there were a couple of things I hit that might help others:

1. Need to ensure you have .net 4.5.2 installed and CRM 2015 SDK.
2. After opening the WebAppWalkthrough project, edit the project properties and set the target framework to 4.5.2 (otherwise the build will throw endless complaints)
3. Make sure all the references have the property Copy To Local set to true in Solution Explorer (browsing to /crmdata.svc was just crashing with an error about being unable to process the request without this). Also, I think it needs a reference to System.IdentityModel. Like I said, I'm new to this so I'm not sure if all those steps are required but those are the steps I took and it's now working for me, so I hope it helps someone else. if anyone is trying to update the xrm.cs file to access custom entities, you need to run the CrmSvcUtil.exe with the codeCustomization argument, details are in the sdk documentation at the following link: msdn.microsoft.com/.../gg695790.aspx Andrew

• Anonymous
  February 02, 2015
  Andrew, Thanks for the detailed comment making this post relevant for 2015! I really appreciate it.

• Anonymous
  April 24, 2015
  hi Girish, This is an excellent post.  Do you also have the instructions for an on-premise model/solution? thanks,