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
- 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!
- 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)
- 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.
- 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.
- 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.
- Set CrmData.svc as the start page and hit F5 to verify that the OData endpoint is working locally. You can browse to http://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.
- 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.
- Once the site has been created, download its publishing profile from the portal.
- 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.
- Once the site has been published, browse to the public URL for the service (say http://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.
- In your Office365 admin center, click the Admin dropdown followed by SharePoint. Create a new private site collection with the “Developer Site” template.
- 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.
- Create a new “App for SharePoint 2013” project.
- 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.
- If Visual Studio prompts you for Office365 credentials, enter them so that the project is connected to your SPO site.
- Right-click on the project –> Add –> “Content Types for an External Data Source”.
- Enter the URL for the custom OData proxy running in Azure website and specify a name. Click Next.
- Choose both AccountSet & ContactSet and hit the Finish button. Visual Studio automatically generates the ECTs (External Content Types) for both accounts and contacts.
- Right-click on the project or solution and click Deploy. Visual Studio automagically installs the app to your SPO developer site.
- 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.