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.

image

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.

image

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

Building the Proxy OData Service

    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.
      image
    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. 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.
      image 
      image
    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. 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 .
      image

 

  1. Once the site has been created, download its publishing profile from the portal.
    image
  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.
    image
  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.
    image

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. In your Office365 admin center, click the Admin dropdown followed by SharePoint. Create a new private site collection with the “Developer Site” template.
      image
    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.
      image
    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.
      image
    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”.
      image
    8. Enter the URL for the custom OData proxy running in Azure website and specify a name. Click Next.
      image
    9. Choose both AccountSet & ContactSet and hit the Finish button. Visual Studio automatically generates the ECTs (External Content Types) for both accounts and contacts.
      image
    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. 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.
    image image

   

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.

 

image