Reports created by report wizard may fail when executing in Microsoft Dynamics CRM 2011

  • Article

This article provides a resolution and workaround for the issue that reports that are created by report wizard or custom FetchXML report may not execute successfully in Microsoft Dynamics CRM 2011.

Applies to:   Microsoft Dynamics CRM 2011
Original KB number:   2590774

Symptoms

You have set up a Microsoft Dynamics CRM 2011 system with dedicated SQL/Reporting server. A domain account is used for CRMAppPool. SRS Data connector is installed successfully on Reporting Server. Microsoft Dynamics CRM Standard Reports run successfully.

However, reports that were created by report wizard or custom FetchXML report may not execute successfully. They may fail to raise a rsProcessingAborted error.

SQL Reporting Logs will contain this error:

Microsoft.Crm.CrmException: An unexpected error occurred.
System.ServiceModel.Security.SecurityNegotiationException: A call to SSPI failed, see inner exception.
System.Security.Authentication.AuthenticationException: A call to SSPI failed, see inner exception.
System.ComponentModel.Win32Exception: The target principal name is incorrect

Cause

The FetchXML query needs to be able to resolve to an HTTP SPN in order to fully communicate between the server. In a scenario where the Microsoft Dynamics CRM application pool is being run by a domain account the query will be looking for an HTTP SPN that does not exist by default.

Resolution

Sections A and B below will resolve the issue. With the introduction of Kernel Mode authentication in IIS 7, there are additional steps required. For more information, see the links in the More information section:

A) Set the Service Principal Names (SPN) value for the service account running the Microsoft Dynamics CRM Application Pool. If there is only one Microsoft Dynamics CRM Web server, steps 4 and 5 can be skipped.

  1. Open an elevated command prompt window. To open an elevated Command Prompt window, select Start, point to All Programs, select Accessories, right-click Command Prompt, and then select Run as administrator.
  2. Type setspn -a HTTP/<ServerName> <ServiceAccountDomain>\<ServiceAccount>, where <ServerName> is the name of the server, <ServiceAccountDomain> is the name of the domain containing the CRMAppPool service account, and <ServiceAccount> is the name of the CRMAppPool service account.
  3. Type setspn -a HTTP/<ServerFQDN> <ServiceAccountDomain>\<ServiceAccount>, where <ServerFQDN> is the fully qualified domain name (FQDN) of the server.
  4. Type setspn setspn -a HTTP/<ClusterName> <ServiceAccountDomain>\<ServiceAccount>, where <ClusterName> is the name of the AD RMS cluster.
  5. Type setspn -a HTTP/<ClusterFQDN> <ServiceAccountDomain>\<ServiceAccount>, where <ClusterFQDN> is the fully qualified domain name (FQDN) of the cluster.

B) Set the IIS useAppPoolCredentials value to True for the Microsoft Dynamics CRM Website:

Note

Installation of the IIS 7 Admin Pack linked below is required. The Admin pack is installed by default in Windows Server 2008 R2.

Administration Pack

  1. Open IIS Manager.
  2. Expand the server and then select Sites. Then select the Microsoft Dynamics CRM website.
  3. Under Management, select Configuration Editor.
  4. In the From: section above the properties, select ApplicationHost.config <location path=...
  5. For the Section: location, select system.webServer > security > authentication > windowsAuthentication.
  6. In the properties page, set useAppPoolCredentials to True, then select Apply.

Workaround

There is a workaround available on this as well that can be used in short-term scenarios where there is a more extensive process in place to get SPNs approved.

  1. On the SRS Server, create a string registry key under the hive of under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM for SandboxClientSpn.<CRMServerName> with <CRMServerName> being the name of the CRM server As a value add the regarding FetchXML SPN: HTTP/CRMfetch(<CRMSERVERNAME>).
  2. Add SPNs to match what was entered above HTTP/CRMfetch(<CRMSERVERNAME>).

For example, you could achieve this in command-line prompt executing setspn. In this example, the CRMServerName is CRM1 and the Application Pool that is running the Microsoft Dynamics CRM Service is MyCRM\CRMServiceAccount.

Setspn -A HTTP/CRMFetch(CRM1) MyCRM\CRMServiceAccount

More information

More information regarding the useAppPoolCredentials setting and Kernel Mode Authentication can be found in the articles below.