Recently we have been struggling with configuration and installation of SQL Server Reporting Services 2008 when there was a co-existing 2005 Reporting Services instance. The following attempts to describe how to make sure your reports work between Microsoft Dynamics CRM and SQL Server Reporting Services.  This is a follow up to my previous post on optimising performance for CRM applications.

The obvious major change between the Reporting Services versions: IIS was required in SQL Server Reporting Services (SSRS) 2005, but no longer since SSRS has its own built-in Web Server to run reports on port 80. However, if you wish to have SSRS 2005 and 2008 co-exist on the same server (for perhaps masochistic reasons?), one has to decide which of the two Reporting Services versions will have to use a port OTHER than port 80.

Prerequisites for the data connector install
Configure your Data source so that the Report Servers Computer_name$ has DBO rights on the OrganisationName_MSCRM, then test that Reports actually work in Report Manager.
Make sure the NetworkServices account runs Reporting Services.

Before installing the Microsoft Dynamics CRM Connector, you most likely run into the Error:

Unable to validate SQL Server Reporting Services Report Server installation.  Please check that it is correctly installed on the local machine.
This will involve editing your install-config.xml file:

Make sure to modify this file to the name of your specific instance, on the following line:

<reportserverurl>http://ServerName/reportserver$SSRS08instanceName</reportserverurl>--> 

(where the Instance name should be written after the dollar sign) 

Run the following from command line afterwards:

SetupSrsDataConnector.exe /CONFIG "D:\Downloads\SrsDataConnector\install-config.xml"

You may have to uninstall and reinstall the Data Connector if you had the ‘Unable to validate…’ error.

If you require support for your configuration, please beware that Microsoft’s Customer Relationship Management team, or Microsoft Dynamics Team, vis-à-vis the Reporting Services Support do not overlap responsibilities  most likely if you report server responds http://ReportingServicesServerName/Reports

If you are able to see the above link, but form the CRM side when you look at a Report and you receive: ‘Reporting error

The Report cannot be displayed; check the Microsoft Dynamics CRM Community for solutions’ then you should try what I mention in this post before contact CRM support.

The Data Connector Optimisation

Data Source configuration should be done to get the most out of Reporting Services running with CRM Dynamics. To do so:

Click on the organisation, then right-hand side, on the toolbar, click Show Details, scroll down to the bottom and click on MS Data Source, and set up your Connection type as below.

Verify that Connection Type is set to "Microsoft CRM Data Extension" and Connect using: is set to "Credentials supplied by the user running the report"

and finally, click Apply.

Connection String: MSCRM Data Connector Connection String

Note, that if the CRM SRS Data Connector is not installed on the SRS Server, please install it and verify it is properly configured before choosing the Data Source Type above.

If the Report Server login dialogue box shows up: "Type or enter a user name and password to access the data source: ", you are going to need to identify input the user's SystemUserID GUID and their Organization GUID.  To help us determine this, please run the following statement against your organization database:

SELECT SystemUserId,OrganizationId, FullName FROM SystemUserBase WHERE Domain Name = 'domain\userid'

For "Log In Name:", please copy the SystemUserId GUID from the query and paste it in the text box.

For "Password:", please copy the OrganizationId GUID from the query and paste it in the text box.

At this point if the reports do not run then transfer your case to the SQL Server Reporting Services team ( BIG THANKS TO THEM for their help! ) to continue troubleshooting.

Troubleshooting CRM/SSRS should also include a look at the Event Viewer, although at times, the ‘Please contact Microsoft CRM Dynamics Team for support’ or Reporting Services error explains little - thus check your SRS log files on the Reporting server in the following folder:

Drive:\Program Files\Microsoft SQL Server\MSSQL.*\Reporting Services\LogFiles