CRM 2013 Fetch XML reports not working

Here’s a scenario:

You install a fresh deployment of Dynamics CRM 2013 on one server using a domain account as the CRMApppool in IIS and Microsoft SQL with SSRS on a second server – Installation goes smoothly and everything works just fine including out-of-the-box reports – except that when you try to create and run Fetch XML reports you get the following error:

“the report cannot be displayed. (rsprocessingaborted)”

Image


Here is what you need to do (The changes that we’ll make might cause CRM to be unavailable for short periods of time):

1. Check the identity of the CRMAppPool. To do this simply click Start -> Run -> type inetmgr -> Press Enter. Expand the server name and under application pools check the CRMAppPool identity.

Image


2. You need to register an SPN for this identity – Let’s assume that the identity is domain\crmservice and the CRM server name is just crmserver.

Open CMD with “run as administrator” and type the following commands one by one:

setspn -A HTTP/crmserver domain\crmservice

setspn -A HTTP/crmserver.domain.com domain\crmservice

Important notes:

  • In the second line you need to use your domain’s FQDN – domain.com is just an example.
  • I’m assuming that you installed CRM on port 80, if not or if you are using host headers you will need to create the proper SPN’s along with the port number – for example:  setspn -A HTTP/crmserver:5555 domain\crmservice

You should get a confirmation that the SPN’s were registered successfully.


3. Browse to your SSRS URL (Probably: http://sqlserver/reports) and click ‘Details View’ on the right, then browse to:

YourOrganization_MSCRM > CustomReports > MSCRM_FetchDataSource – Inside make sure that the data type is set to ‘Microsoft Dynamics CRM Fetch’, the connection string has your Organization Name and connect using is set to ‘Credentials supplied by the user running the report’

Image

Save & Close.


4. Do an IIS reset (should work without but just in case…) Open CRM and check your FetchXML reports – they should be working now – It’s very important to check everything locally on the CRM server & remotely from clients or other server.


VERY IMPORTANT:

As we did changes in SPN’s that may mess up Kerberos authentication – you should check that CRM is available locally and from remote clients – If you are able to browse CRM locally (or using I.P) but you are getting 401 authentication error (after being asked for credentials 3-4 times) when trying to access from a different server or client using the CRM URL – do the following to resolve this:

  • On the CRM server open IIS and go to the Microsoft Dynamics CRM website -> Open the Configuration Editor

Image

  • Under Section browse to the following location – system.webServer -> Security -> Authentication -> Windows Authentication

Image

  • Inside make sure that UseAppPoolCredentials is set to True:

Image

  • Do an IIS reset on the CRM server. This procedure should resolve the 401 authentication issue and your CRM should be accessible from all clients along with the working FetchXML reports.

If from some reason your CRM started behaving “weird” or this process caused other issues you can revert all the changes we did by simply deleting the SPN’s and changing back UseAppPoolCredentials to False.

To delete the SPN’s use -D instead of -A:

setspn -D HTTP/crmserver domain\crmservice

setspn -D HTTP/crmserver.domain.com domain\crmservice

Remember that it’s best to do all those changes after working hours, as some of them might cause CRM to be unavailable. Also if you have a testing environment – it’s always better to test those things there before we do them on the production servers.

Good Luck!

CRM async service high CPU usage

There are many scenarios that can cause a high CPU utilization by the CRM asynchronous service, here are some
of the most common ones i encountered:

1. Check if the AsyncOperationBase table doesn’t have too many records in it. Once this table has millions of records – things may start working very slow. Use the following article to solve this issue and to create a proper maintenance plan to prevent this from happening:

http://support.microsoft.com/kb/968520

2. Use the CRM Advanced Find to search for locked system jobs – this is a very common issue that can cause the
async service to go havoc and utilize 100% of the CPU on the server ALL THE TIME – even when there is
absolutely zero activity in the system.

If you find any locked system jobs with the advanced find – delete them and restart the async service.

Image

3. Check the email router and the emails in the system:

Use the Advanced find to see if there are any emails stuck in sending status, if so – try deleting them and investigate why they got stuck in the first place. You should also try stopping the email router service to eliminate it as the cause and check the event log to see if there are any “suspicious” emails in the queue. Sometimes emails with special characters or some sort of corruption can cause issues with the async service (although they are not supposed to, i did encounter it).

4. RESTART! try restarting the server/asynchronous service. Sometimes restarting the system is the simplest
solution, so before you start investigating the problem and wasting hours on hours of your time – make sure you do system reboot first.

5. If none of the above helps you should try investigating further and farther.

  • Check your SQL server logs, performance & event log for any suspicious entries.
  •  Try tracing the CRM and analyze the trace file for any errors or exceptions. Use this article to enable tracing for CRM: http://support.microsoft.com/kb/907490
  • Try to see if maybe a 3rd party entity is causing the problem: Antivirus, Firewall etc.

Good Luck 🙂

Michael.