Microsoft Dynamics CRM 4.0 Tidbits - Reporting Architecture

Papa's Got A Brand New Bag...

Because CRM 3.0 was an Intranet only application, we could make certain assumption that users would have direct access to SQL server for a number of features including:

• "Reporting" - where users were navigated over to the SQL Server Reporting Services web server.
• "Export To Excel" - where users directly connect to the secured views using an ODBC connection.

With CRM 4.0 we have to make sure the product is deployable and secure in an Internet facing environment such as https://www.crmlive.com/. This required a re-think of our architecture, removing the need for direct database or report server access by end-users. To acheive this we made several high-level architectural changes:

• Report Viewer moved to the CRM server
• New Data Connector proxies requests
• Remove Kerberos trust for delegation requirement
• Report entity is a full CRM entity

In order to understand what this means, I'll explain the steps involved in viewing and running reports in CRM 3.0 and then compare with with the new architecture in CRM 4.0

Viewing The Report List in CRM 3.0

1. The user navigates to the CRM report list page.
2. The CRM server queries the SRS web service for a list of reports. Kerberos Trust For Delegation is used by the CRM server to impersonate the user credentials when connecting to the SRS server.
3. The SRS server returns a list of reports the user is authorised to view.
4. The CRM server displays the list of reports.

Viewing The Report List In CRM 4.0

1. The user navigates to the CRM report list page.
2. The CRM server returns a list of reports the user is authorised to view.

Reports in CRM 4.0 are now just a standard entity and can be secured using the role-based security. The upshot of this is that reports are now shareable, assignable, workflow enabled, advanced find enabled and can also be taken offline.

Another benefit is that when navigating to the list of reports, no web service call is made to the SRS web service, which means that no Kerberos Trust For Delegation is required when viewing a list of reports. Setting up Kerberos Service Principal Name (SPN) & Trust For Delegation (TFD) is one of the most common mis-configuration issues in CRM 3.0.

There is also another advantage in that the new architecture can significantly improve the responsiveness of the system. In CRM 3.0 whenever an entity grid view in CRM is rendered, the page makes a synchronous call to SRS in order to populate the list of contextual reports. If the connection to the SRS is particularly slow, the page can be blocked, which will have a knock-on effect on the overall page-load times. In CRM 4.0 we don't need to query SRS to obtain this information, thus eliminating this bottleneck.

Running A Report In CRM 3.0

1. The User runs a report
2. The CRM server redirects the user's browser to the SRS web site
3. The browser opens a new window and runs the report from the SRS web site
4. The SRS server queries the SQL server using the CRM filtered views. Kerberos Trust For Delegation is used by the SRS server to impersonate the user credentials when connecting to the SQL server.
5. The SQL server returns the list of records the user is authorised to view.
6. The SRS server renders the report in the appropriate format and displays it to the user.

Running A Report In CRM 4.0

1. The user runs a report
2. The CRM server requests the report from the SRS server using the the CRMAppPool credentials (e.g. "NetworkService" account).
3. The SRS server queries the SQL server via the CRM filtered views, using the CRM Data Connector.
4. The SQL server returns the list of records the user is authorised to view.
5. The SRS server generates the report in an intermediate format and returns it to the CRM server.
6. The CRM server renders the report in the appropriate format using the report viewer control and displays it to the user.

In CRM 4.0 we added a new CRM Data Connector to the SRS server. This is a component that is installed on the SRS server itself and substitutes for a direct connection to the database. Think of the component as a proxy that takes requests and sends them to the transactional server. The CRM server requests the report from the SRS, passing in the identity of the user as a parameter. The CRM Data Connector then takes the user ID and executes the query against the filtered views using that user’s ID as a filter on the data. The benefit of this architecture is that no Kerberos Trust For Delegation is required when running reports.

We also moved the report viewer control – an ASP.Net control – from the SRS server to the CRM application server, keeping users in the context of the CRM IIS web site, and eliminating cross web-site authentication issues that arise when using forms authentication in an Internet facing deployment.

This posting is provided "AS IS" with no warranties, and confers no rights.

Laughing Boy