CRM 4 Reporting Services Connector Information

This excellent blog was sent to me today by a work colleague.

The CRM 4.0 Reporting Services Connector – how it works

After passing my CRM installation exam I had recently been reading about the Reporting Service Connector and how you need it to use reports.

This is a useful article because this can often be a source of problems in CRM deployments, it also has a detailed example about the installation process.

This part of the blog I found particularly interesting

There are 3 differences between these configurations:

  1. The Connection Type, which specifies the extension
  2. The Connection String is absent with the CRM connector. This is because the connector reads some of the database information from registry values that were created during its installation, and some from data passed to it when the report is run (see below)
  3. The Credentials. With the SQL Server connector, standard Windows Integrated security is used – i.e. the user’s AD credentials are used to connect to SQL Server. With the CRM connector, separate ‘credentials’ are passed to SSRS (again, see below)

What happens when a report is run
If you try to run a CRM report with the CRM connector installed, the connector will require some ‘credentials’, as per point no.3 above. This image shows what happens if you try to run a report from Report Manager:

Running a CRM report from Report Manager when the CRM connector is installed

These ‘credentials’ are not what they seem; rather they are a cunning way for the CRM platform to pass information about the current user to the CRM connector. The CRM connector expects the current user’s systemuserid (a Guid) to be passed into the Log In Name box, and the organizationid (another Guid) to be passed into the Password box. These are not your login name and password.

As the report uses a data source that uses the CRM connector, the RS Report Server code calls the CRM connector code (the SrsExtConnection class in the Microsoft.Crm.Reporting.DataConnector assembly, as per the rsreportserver.config data above). The code will then:

  1. Check that it is permitted to impersonate a CRM user. This checks that the identity the code is running under (which the identity of the ReportServer application pool, or the Reporing Services service, depending on the version of Reporting Services) belongs to the AD group PrivReportingGroup
  2. Connect to the MSCRM_Config database to determine the correct MSCRM organization database, based on the organizationid that was passed in the ‘credentials’
  3. Connect to the relevant MSCRM organization database. Note that this is done (as was the previous step) using integrated security under the AD identity as per step 1 above
  4. Use the SQL statement SET Context_Info to pass the calling CRM user’s systemuserid into the Context_Info
  5. Execute the SQL statement(s) within the report definition. The definition of all filtered views use the fn_FindUserGuid function to read the systemuserid from the Context_Info

What can you do with this information
One use is for troubleshooting. Checking the rsreportserver.config is a quick way to see if the connector is installed, and checking the configuration of the MSCRM Data Source will tell you if the connector is in use. Changing the MSCRM Data Source is a quick way to turn the connector on or off for test purposes.

You can also run the reports directly, rather than from CRM. Again, when troubleshooting I find it useful to run a report directly from Report Manager web interface. To do this with the connector, you need to enter the systemuserid and organizationid when prompted (see image above). These values can be read from the filteredsystemuser and filterorganization views respectively in the MSCRM database.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.