I try not to write about stuff that is available all over the web but i am still writing this blog because i could not find a solution for this online. I ran into alot of issues with our Reporting Server setup for CRM 2011 on-premises and had to spend a lot of hours before i could figure it out. First of all i really miss my old IT team as they were awesome and because of those guys i never had to deal with IT issues. Anyways so here are the details of the error along with the solution.
Learn more about our Microsoft Dynamics 365 Services
Dreaded rsProcessingAborted was what i got to start with. I checked the error log on the reporting server. I had following error in the log.
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset ‘DSMain’.Microsoft.Crm.Reporting.DataExtensionShim.Common.ReportExecutionException:
Microsoft.Crm.CrmException: An unexpected error occurred.
System.ServiceModel.Security.SecurityNegotiationException: A call to SSPI failed, see inner exception.
All these errors are explained in Conor’s blog so just refer to that if you have these issues.
http://blog.simpletrees.com/2013/04/mscrm-and-dreaded-rsprocessingaborted.html
However once this was fixed i ran into an issue where custom reports were not working. I didn’t have any error in the error log. I opened up the execution log in the reporting DB and i could not see the request.
ISSUE:
– Hardware architecture:
- NLB with 2 CRM 2011 web application servers on SSL. 1 SSRS server and a separate SQL back-end server SQL 2008 R2.
– Reports – Canned reports shipped with MS Dynamics CRM worked. Custom reports created using “Report Wizard” in CRM 2011 worked intermittently. Those that did not work didn’t show any errors in event viewer on CRM 2011 application server, application server trace logs, Report server logs and reports server execution log in SQL show that a request never got to the report server.
Probable CAUSE: ( I am not sure about this so feel free to correct me if i am wrong)
– SSRS server creates a session for the report request. Let’s assume the request was instantiated by Application server A and gets to reporting server. In order to establish the session there will be back and forth message flow between SSRS and application server. However as it is a web farm it could be that response from SSRS gets to the Application server B. Application server B fails to decrypt the message and does not know if the request was sent from Application Server A probably because the machine key is not the same on both the application servers. Application Server B never responds back to the request and application server A is left hanging waiting for the session from SSRS. That is why the report does not work.
RESOLUTION:
– Applied and issue resolved: Generated the machine key on one application server and used it on both application servers. After this change irrespective of which application server receives the request the request should be decrypted and session should be established with the right application server. This fixed the report issue.
– MSFT recommended: (Makes more sense) I opened a ticket with MSFT to confirm if this is the right solution. Here is what they suggested.
Make sure the Affinity or the Sticky Session is set to 1 / True. This will enable the request initiated on one server to be completed on that server.
For more information refer the links
– http://msdn.microsoft.com/en-us/library/dd979314(v=crm.6).aspx
– http://technet.microsoft.com/en-us/library/bb687542.aspx
I hope this will save someone’s time.
Test