How it Works: SQL Server Reporting Services and Dynamics CRM

Barry Givens Published Friday, July 18, 2008 10:14 AM

---

At Tech-Ed Developer in Orlando a few weeks back I lead an interactive session on CRM and Business Intelligence. The session was open to any CRM and BI topic so I expected a lot of hard questions about data mining and the like but the topic of greatest interest turned out to be the CRM and SQL Server Reporting Services (SSRS) integration. This should be helpful to folks with questions about deployment practices or with an interest in exposing CRM reports to users outside of the CRM application.

First things:

1. With CRM 4 reports are an entity within CRM. They have meta data and CRM security is applied to determine whether or not a user may view the report . Note that that is the report, not the underlying data. Users can have access to CRM data that a report points to and not have access to the report itself. Likewise a user may have access to a report but not the data that it would show (in which case the user could run the report but it would return no data).
2. The SQL Reporting Services Report Viewer is an ASP.Net control which runs on the CRM 4.0 Web server. In CRM 3.0, and when you interact with the SSRS Report Manager, that control is running on the Web server fronting SSRS. When you choose to run a report from CRM 4.0 the ASP.Net control requests the report and data from the remote SSRS box. In practical terms: in CRM 3.0 the URL for a report was the URL for the SSRS Web server; in CRM 4.0 the URL for a report is the CRM Web server.

Because CRM 4.0 reports are always run in a delegated mode the CRM and SSRS integration has to handle security. There are two ways to do this in CRM 4.0. One way to do this is to use integrated authentication where trust for delegation is required between the CRM server, the SSRS server and the SQL server with the CRM db. This was the required configuration on CRM 3.0 and frankly, it was a bit of a headache for folks to manage [see HOW TO: Configure Kerberos authentication for Microsoft CRM 3.0 and Microsoft SQL Server Reporting Services and Microsoft CRM 3.0: Additional Setup Tasks Required if Reporting Services Is Installed on Different Server . ]

The other mechanism is to use the SQL Server Reporting Services MS CRM connector. This connector runs as an SSRS Data Processing Extension and handles all of the delegation for you. The use of the data connector is recommended for Internet facing deployments and anywhere users are not using NT Auth to connect to CRM. When using the Data Connector users of CRM cannot directly access the RDLs in SSRS – all management of reports must be done through the CRM reporting UI; users connecting to the SSRS Report Manager will get an access denied message if they try to browse Reports.

Choosing a deployment type is up to you and of course there are pros and cons either way. The following table describes some of those (if you have others throw them in the comments).

Capability	SQL Server Reporting Services Data Connector	Kerberos Authentication
Works with Internet Facing Deployments	Yes	No
Schedule reports using the Report Scheduling wizard in CRM	Yes	No
Uses NT credentials to connect to SQL Views	No	Yes
Access CRM reports outside of CRM	No	Yes
Use the CRM Report Wizard	Yes	Yes
Keeps CRM data secure	Yes!	Yes!

The table speaks for itself and I think that for most organizations the Connector is probably the right way to go. But let me point out one item that is near and dear to me: “Access CRM reports outside of CRM”. One of the great things about SSRS is its direct URL access to reports; along with that are the ability the embed reports into Microsoft Office SharePoint sites, in Performance Point dashboards, on your own ASPX pages using the ASP.Net control or my favorite: embedded with forms of the CRM application itself. If you use the connector you won’t be able to use URL access for reports; this is so useful though that we made sure to give you a work around.

If you have the “Add Reporting Services Reports” privilege you’ll see a command on the Action menu of the Report form titled “Publish Report for External Use”. This command will publish your report and any child reports to a directory in SSRS that is open to all CRM users. You can embed the URL to that report, along with any arguments on the query string, within CRM or the Report Viewer controls.

You won’t get any feedback that this worked so you’ll just have to trust but verify that it did. Doing this multiple times will also overwrite any existing report with the same name in the target directory so this isn’t the most… elegant… solution but there isn’t a demo environment that I have that doesn’t take advantage of it.

 

Cheers,

Barry Givens