Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Troubleshooting and Optimising SQL 2008 Reporting Services Configuration for Microsoft Dynamics CRM 4.0

 

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

Click on OrgName_MSCRM, On the right-hand side toolbar, click Show Details. Click 4.0 then click on MSCRM_DataSource

 

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

 

 

Comments

Posted by clive on 9 May 2011

Scenario 1:

If both CRM and SQL 2008 R2 Reporting Services are on same server then the SQL report CRM security works. (i.e. if a user runs SQL report and user is in Marketing Business Unit then he cannot view Finance Business Unit info).

Scenario 2:

If you use 2 servers, CRM 4.0 installed on server 1 and SQL 2008 R2 Reporting Services installed on server 2, then CRM security does not work. (i.e. users in Marketing Business Unit can view Finance Business Unit info). If i run the report when logged onto server 2 (report server with SSRS2008 R2),  I can view report parameters, but when I run the report from my desktop (report manager), the report parameters are blank. It seems as if issue is IIS related (since CRM is installed on server 1 and SSRS2008 R2 on server 2 - server 2 therefore has no IIS installed, so somehow cannot open a connection to database when not logged on locally to the server).

I ran the DB CRM Data connector a few times on server 2 (report server with SSRS2008 R2), still does not work.

What I need to know is if CRM supports 2 separate servers as in scenario 2. If it does, how do you configure it to work ?

Posted by Hugo Shebbeare on 26 July 2012

Sorry for the super late reply - Yes, it does, and we even scale out with Reporting services - do not put all on the same server, unless it is very resource happy.

Remember that if you reports do not work, give the server some time to re-establish connections to the CRM app server, Sometimes we have a problem late one evening, flip a node, wait several hours and it goes away - go figure, eh?

IIS is not involved in SQL 2008, nor R2, it has it's own HTTP.sys  - keep restarting report services, that seemed to be our other fix - dumb, but good fix.

Leave a Comment

Please register or log in to leave a comment.