Prerequisites for problem:
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 raising 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
The FetchXML query needs to be able to resolve to a 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 a HTTP SPN that does not exist by default.
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 CRM Application Pool. If there is only one CRM Web server steps 4 and 5 can be skipped.
1. Open an elevated command prompt window. To open an elevated Command Prompt window, click Start, point to All Programs, click Accessories, right-click Command Prompt, and then click 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 -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 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.
1. Open IIS Manager.
2. Expand the server and then selet Sites. Then select the Microsoft 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.
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.
a. 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>)
b. Add SPNs to match what was entered above HTTP/CRMfetch(<CRMSERVERNAME>)
E.g. you could achieve this in command line prompt executing setspn. In this example The CRM Server Name is CRM1 and the Application Pool that is running the CRM Service is MyCRM\CRMServiceAccount
Setspn –A HTTP/CRMFetch(CRM1) MyCRM\CRMServiceAccount
More information regarding the useAppPoolCredentials setting and Kernel Mode Authentication can be found in the articles below.