image
Recently I was working with a client to configure the out of the box integration between Dynamics CRM and SharePoint.  In the client’s case, they were using CRM 2013 Online via Office 365, and SharePoint 2010 on-premise. 

Overview of Integration Options

When integrating CRM and SharePoint for document management, out of the box you now have two main options:

  • Server-based SharePoint Integration for Microsoft Dynamics CRM Online and SharePoint Online
    The new and shiny (it’s no iWatch, but hey it’s a step up from what it was)
  • CRM list component sandbox solution feature
    Standard old and rusty run-of-the-mill method that has existed since CRM 2011

Let’s briefly look at each, then we’ll get to our issue.  If you can’t wait then feel free to jump ahead.  Perhaps in another blog we’ll dive deeper into how to configure the integration and the different methods but for now we’ll take a fly by.  There’s a good comparison of the differences between the two here on TechNet:

http://technet.microsoft.com/en-us/library/dn792527.aspx

Server-based Integration

This feature was introduced in the Dynamics CRM Online Spring ‘14 Wave and is only available when using both CRM and SharePoint Online via Office 365.  Unlike the CRM list component that is client-based, this feature is server-based and does not require any installation of any solutions.  Use the following steps to enable server-based integration from TechNet:

http://technet.microsoft.com/en-us/library/dn531154.aspx#BKMK_enable_SP_S2S

It’s documented more here in this video from Microsoft:

http://youtu.be/guBcprvtiSo

CRM List Component

The CRM list component is a free download from Microsoft as a Microsoft sandboxed-solution (client-side).  You upload this to the SharePoint site collection, then configure CRM.  The CRM list component provides more of the standard features of a SharePoint document library from the CRM form like versioning, check in / check out, alerts, etc.  When users attempt to open the Documents section in CRM for an entity, they will be required to login before the content is rendered. 

Per Microsoft, this methodology is being deprecated but it still an option for SharePoint 2010 and SharePoint 2013.  To install and configure the CRM list component, follow these steps from TechNet:

http://technet.microsoft.com/en-us/library/dn531154.aspx

Fixing Our Issue

So this brings us to the issue at hand.  After getting SharePoint available on the public interweb and thus available to CRM, we installed and activated the list component.  For a short while, everything seemed fine.  A little time passed before we were able to configure the CRM entities that we wanted to map, so we did that and finalized the configurations in CRM.  It found SharePoint as a valid URL, all was good (or so I thought).  When we went to an Opportunity or other entity that was configured for document management, it would throw an error along the lines of:

“An error occurred while loading the page.  The URL may not have been mapped in the SharePoint Server.  Ask your system administrator to check the Configure alternate access mappings settings in SharePoint Central Administration.”

image

Back over in SharePoint, I knew that the CRM list component is based on the sandboxed service, I tried to de-activate and re-activate the site collection feature.  I got this:

image

We restarted the SharePoint 2010 User Code Host Windows service, which threw login errors.  After re-typing the password, it stuck.  Everything was ok again for 5 minutes.  Another step was to add just the user who was running the SharePoint 2010 User Code Host service in the local group Performance Log Users group.  This seemed to help the login issue, but granting that user the login as a service right is what ultimately fixed that.  Also make sure that on your Web Application, Browser File Handing is set to Permissive.  Once past that, I was still getting errors.  I watched the ULS, and noticed this:

  • System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object 'proc_GetTimerJobLastRunTime', database 'SharePoint_Config', schema 'dbo'.
  • Insufficient SQL database permissions for user 'Name: DOMAIN\serviceacct SID: S-1-5-21-37471653781-38943232595-3579643925-222709 ImpersonationLevel: None' in database 'SharePoint_Config' on SQL Server instance 'SQL\instance'. Additional error information from SQL Server is included below. The EXECUTE permission was denied on the object 'proc_GetTimerJobLastRunTime', database 'SharePoint_Config', schema 'dbo'.

NOW we have something to work with.  To fix this, we need grant some permissions on the SQL stored procedure. 

  • Login to the SQL server and SQL Management Studio.  Expand the SharePoint Config database, then Security –> Roles –> Database roles –> and double-click the WSS_Content_Application_Pools group.  If the service account account is not listed, click Add and add it.  Click Ok.
    image
  • Still under the SharePoint Config database, expand Programmability –> Stored Procedures –> find your stored proc mentioned in the error from the ULS (in my case 'proc_GetTimerJobLastRunTime').  Double click on the proc, and click Permissions.  Click Search, type “WSS_Content_Application_Pools”, and grant it the Execute permission.  Click Ok.
    image

Restart the SharePoint 2010 User Code Host service for good measure, and make sure that the List Component solution is activated, and the site collection feature is activated.  To be sure, you should be able to de-activate it, and re-activate it. It should work without error now.  Go back to CRM and attempt to load the Documents view.  You should get success! 

One other possible error you might see that I saw from a lot of folks is documented here on MSDN.  Pay attention to the error message, as this relates to a refused error.  Good luck!