Recently for a project requirement I had to deploy MSCRM 4.0 on an IFD server. Everything else seems to work properly except the Reports. The reports were running good on on-premise configurations for the same organization. This issue was same for Custom reports as well as the out-of-box reports by MS CRM.
Its then I read that this is a common issue with IFD deployment. To resolve this, the deployment must be running the Microsoft Dynamics CRM Connector for Microsoft SQL Server Reporting Services.
The SRSDataConnector for MS CRM 4.0 comes with the installation files for MS CRM 4.0 and is located on the installation CD: “drive:\Server\i386 \srsDataConnector” folder. Run the “setupsrsdataconnector.exe” executable in the mentioned folder and you are done with it!
How does it work? - When a Microsoft Dynamics CRM report is run, Microsoft SQL Server Reporting Services Viewer requests the report and data from the remote Microsoft SQL Server Reporting Services computer. To access the report, the Microsoft Dynamics CRM server URL is used. Microsoft Dynamics CRM Connector for Microsoft SQL Server Reporting Services runs as a Microsoft SQL Server Reporting Services data processing extension and handles the authentication in the delegated mode used for reports.
However, there is a limitation with the connector too. The MS CRM connector doesn't work with the Microsoft SQL Server 2005 Workgroup Edition. This is because the Workgroup edition does not support custom data extensions used in the Microsoft Dynamics CRM Connector for Microsoft SQL Server Reporting Services. So, to further resolve this, one should use Standard Edition OR Enterprise Edition of SQL Server. This is applicable for both SQL Server 2005 OR SQL Server 2008 Installations.