CRM 2011 Report Error - rsProcessingAborted

I encountered this error in my project when trying to run CRM reports within CRM (including the default reports). In the reportservice logs it says:

Cannot create a connection to data source 'CRM'. ---> Microsoft.Crm.Reporting.DataExtensionShim.Common.ReportExecutionException: Immediate caller Domain\SqlService has insufficient privilege to run report as user S-1-5-21-1162093662-620106126-315576832-39399.

The CRM is installed on the application server and the SSRS is installed on different server. After futher investigation it turns out that it's caused by double hopping issue regarding the authentication and we just need to set the correct SPN on the machines.

setspn -a http/your-crm-server-name domain\crm-user 
setspn -a http/your-crm-server-name(FQDN) domain\crm-user

Other things to check to make sure your reports work:
1. Make sure your SRS connector has the same RollUp version as the CRM Server RollUp
2. In Reporting Service Configuration Manager, uncheck the 'Specify an Execution Account'
3. Browse to both SharedReports\5.0.xxxx\ and _MSCRM report folder and check the MSCRM_DataSource connection. The connection string should be empty,  DataSource Type should be Microsoft Dynamics CRM SQL, and the connection should be Credentials supplied by the user running the report (uncheck the Windows Credentials box)

---
To run the report from the report server Url, you might find that if you are using normal domain account for username/password you will get the following error:

An error has occurred during report processing (rsProcessingAborted) Cannot create a connection to data source ‘CRM.’ (rsErrorOpeningConnection) Guid should contain 32 digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx).

This is because the Username should be 'systemuserid' and the Password should be 'organizationid'. You can get this by running the query against the CRM database:

SELECT fullname, systemuserid, organizationid FROM FilteredSystemUser

Hope this helps,
Andreas




Comments

  1. I had this problem, all I had to do was add the SQL Reporting Services execution account to the PrivUserGroup and PrivReportingGroup.

    ReplyDelete
    Replies
    1. This is finally what worked for me too...I had the Sql Reporting Services Execution Account in the PrivReportingGroup but not the PrivUserGroup...once that was added we were good (it also needs permissions on the db too). Also, instead of FilteredSystemUser use SystemUserBase table.

      Delete
    2. You are awesome! I had to add the Network Service to those 2 groups and all is well.

      Delete
  2. Thanks dude !!! this really helped me a lot .... I was getting the same problem but after doing the chages it worked fine for me....

    ReplyDelete
  3. When I run that command, I don't have any users listed, I know they are missing, do you know how to add them?

    ReplyDelete
    Replies
    1. Sorry which list? the spn list or the filteredsystemuser list?

      Delete
  4. What is FQDN in the command above?

    ReplyDelete
    Replies
    1. Fully Qualified Domain Name of your server :)

      Delete
  5. Hi Andreas,

    What user should be used for crm-user? I would hope I don't have to set the SPN for every CRM user!

    Also, on which computers are the commands executed? On both the SRS server and CRM server?


    Thanks for your help!

    ReplyDelete
  6. the only i had to do is --> 2. In Reporting Service Configuration Manager, uncheck the 'Specify an Execution Account' WORKS GREAT THNX

    ReplyDelete
  7. Hi Andreas,

    Great article. I followed all your suggestions, however in our case we keep getting below error.

    Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'CRM'. ---> Microsoft.Crm.Reporting.DataExtensionShim.Common.ReportExecutionException: Caller S-1-5-21-812446608-132657156-1731110561-1114 has insufficient privilege to run report as user 704f896c-1c2b-e311-9405-001dd8b71c24

    User "704f896c-1c2b-e311-9405-001dd8b71c24" is the systemuserid I pulled out from the database using your SQL Query to retrieve Username and Password (Organisationid) details to run the report from report server directly.

    What are we missing here?

    Thanks,
    Rafa

    ReplyDelete
    Replies
    1. Hey Rafa,

      Couple of things to check is in this article:
      http://community.dynamics.com/crm/b/crminogic/archive/2012/05/29/solutions-to-issues-with-reports-in-dynamics-crm-2011.aspx

      also check if your report is available to Organization level? (in Report Edit settings)

      Delete

Post a Comment

Popular posts from this blog

SharePoint 2013 anonymous access add attachments to list item

CRM Plugin - Parent and Child Pipeline

Sitecore custom publish agent from specific node and at a specific time