Microsoft Dynamics CRM 4.0 : SQL Server Reporting Services Integration (part 1) - Install the Reporting Server on the Same Server as the Microsoft Dynamics CRM Server

6/15/2012 11:33:17 AM

SQL Server Reporting Services is a crucial integration component that often causes Microsoft Dynamics CRM administrators frustration. No other aspect of Microsoft Dynamics CRM caused more problems related to Kerberos than the Reporting Services integration. Lucky for us, the main problem (the double-hop authentication problem) was largely fixed in Microsoft Dynamics CRM 4.0 with the aid of MS CRM SSRS Connector, and now only incorrect configuration causes SQL Server Reporting Services to fail.

By default, some basic integration enables you to run some reports stored in SQL Server. In addition, users can create custom RDL files and display the reports through the integrated view in CRM. To ensure that the IIS authentication is configured properly, you should run the IIS Authentication and Access Control Diagnostics (as shown in Figure 1).

Figure 1. Authenticated Diagnostics

Additional integration points are available when using CRM, SharePoint, and SQL Reporting Services.

You may consider three possible deployment scenarios. Choosing the appropriate deployment depends on the number of requests for reporting.

Install the Reporting Server on the Same Server as the Microsoft Dynamics CRM Server

Pro
- Simple deployment.
- The reporting server is the same server as CRM server (see Figure 2).
  
  Figure 2. Reporting server on the same server as the CRM server.
- You can use NTLM or Kerberos authentication to get user-specific data.
Con
- Puts extra load on the CRM web servers.

The installation steps are as follows:

1.	Log in to the CRM front-end web server.
2.	Navigate to the Microsoft SQL installation setup.
3.	Launch the setup.
4.	Click Next to install the prerequisites.
5.	Click Next on the Welcome screen.
6.	Click Next on the Prerequisite Check. If IIS and ASP.NET are not installed/configured on the server, cancel the setup and configure IIS and ASP.NET using the application server role, and then restart the setup.
7.	Enter the product key and click Next.
8.	On the Components to Install screen, select the following components: Reporting Services Workstation components, Books Online, and development tools (recommended)
9.	Click Advanced.
10.	In the Documentation, Samples, and Sample Databases screen, deselect Sample Databases and Sample Code and Applications (recommended).
11.	Click Next.
12.	On the Instance Name screen, you can specify an instance name for Reporting Services and database engine. If you want to install it on the default instance instead, select Default Instance (MSSQLSERVER).
13.	On the Service Account screen, do the following: Specify a domain user account.
14.	Click Next.
15.	Select the appropriate collation. If unsure, use default values.
16.	Click Next.
17.	On the Report Server Installation Options screen, select Install the Default Configuration.
18.	Click Next to begin installation.

The following configuration steps are required only if you did not configure at the time of installation:

1.	Click Start.
2.	Click Microsoft SQL Server 2005.
3.	Click Configuration Tools.
4.	Click Reporting Services Configuration (see Figure 3). Figure 3. Reporting server status.
5.	Start the Reporting Service if not started already (see Figure 3).
6.	Create a virtual directory for report storage (by default, it is ReportServer) (see Figure 4). Figure 4. Report server virtual directory settings.
7.	Create a virtual directory (by default, it is Reports) (see Figure 5). Figure 5. Report Manager virtual directory.
8.	Select the appropriate identity (as chosen in the earlier steps) (see Figure 6). Figure 6. Windows service identity. This has to be either NTLM authentication or Kerberos authentication needs to be enabled.
9.	Confirm the web service identity and the service accounts running the application (see Figure 7). Figure 7. Web service identity.
10.	Select the database location of the report server. This is a required configuration for running the service in a load-balanced environment (see Figure 8). Figure 8. Database setup.
11.	On the Initialization screen, select Initialize the Reporting Server.

The verification steps are as follows:

1.	Before beginning the configuration, verify whether Reporting Services is running.
2.	Navigate to http://localhost/reports or https://localhost/reports (if you enabled SSL on your server). You should see the Report Manager home page.

Note

It is possible to use SharePoint Integrated Report Manager to manage the SQL reports inside SharePoint.