SQL Server Reporting Services 2005

JohanL — Fri, 25 Jul 2008 17:36:00 GMT

Since the RTM of K2 blackpearl I’ve been assisting various companies and people in setting up SQL Server Reporting Services (SSRS) so that it can work with K2 blackpearl. The setup and configuration of SSRS is not too difficult but it can be a daunting task when you have never worked with it.

In this post I will attempt to guide anyone through the installation and configuration of SSRS. There are a lot of resources available on the Internet on how to do this, so instead of rewriting all the work out there I will simply refer to these resources and then discuss any specific configuration considerations specifically with regards to K2 blackpearl.

Installation

This is probably the easiest part of the whole process. You will install the components required by SSRS by using the Microsoft SQL Server 2005 CD. The following MSDN articles will explain the necessary steps on how to accomplish this task.

http://msdn.microsoft.com/en-us/library/ms143757.aspx - Reporting Services Installation How-to Topics

http://msdn.microsoft.com/en-us/library/ms144290.aspx - How to: Install Reporting Services on a Single Computer

One consideration you should take into account is the platform of SSRS that you are going to install. It is recommended that you stick to 64-bit for all SQL components if your operating system is 64-bit. Trying to install 32-bit SSRS on a server where the rest of the SQL components are on 64-bit can be a very complex process. It is however possible, but keep in mind that the K2 blackpearl Workspace installer will convert IIS to 64-bit mode during installation. If you are installing K2 blackpearl Workspace and SSRS on the same server then rather use the 64-bit of SSRS. Look at the following article if you need to install SSRS in 32-bit mode on a 64-bit server.

http://msdn.microsoft.com/en-us/library/ms143293.aspx - How to: Install 32-bit Reporting Services on a 64-bit Computer

Configuration

This is the part where you set up all the moving parts that make SSRS work. All of this is accomplished through the Reporting Services Configuration Manager utility.

To help you have all the necessary configuration values ready for this utility, fill in the following table with all the values for your environment:

Configuration option	Value
Report Server Virtual Directory Name of the web site where the ReportServer Virtual directory will be installed. (IIS Manager > Web Sites) * The ReportServer virtual directory should not exist in this web site yet. If it does, delete it before running the utility.
Report Manager Virtual Directory Name of the web site where the Reports Virtual directory will be installed. (IIS Manager > Web Sites) * The Reports virtual directory should not exist in this web site yet. If it does, delete it before running the utility.
Windows Service Identity The name of the user account which will be used to run the SSRS Windows Service (Local/Active Directory User account or a built-in account)
Web Service Identity The name(s) of the IIS application pool(s) that will be used for the Reports and ReportServer virtual directories. (IIS Manager > Application Pools) * The application pools should exist with the correct user defined or you should have the username and password ready to configure a new Application Pool from the Configuration Utility
Database Setup The name and instance of your SQL Server which will host the ReportServer database. If you will use credentials other than the Service’s then have this ready as well. (SQL Server Management Studio > SQL Server properties)
Email Settings (Optional) E-mail server address and sender’s e-mail address. * Only required if you will be sending e-mail from SSRS.
Execution Account (Optional) A username and password of an account that will be used to perform unattended operations * Only used to connect to a data source that does not require credentials and when the report server connects to another server to retrieve external resources that are referenced in a report.

When you have the above ready you can start configuring SSRS. Start from the top of the utility and work your way down. When you are complete all the options on the left should have the green status with the exception of the optional settings (Email Settings and Execution Account). When they are all green SSRS is configured properly.

The following articles deal with specifics on how to configure each component:

http://msdn.microsoft.com/en-us/library/ms157412.aspx - Reporting Services Configuration How-to Topics

http://msdn.microsoft.com/en-us/library/ms159624.aspx - Configuring Reporting Services Components

Testing

After you completed all the settings on the Configuration utility, SSRS should be configured. Now you need to verify whether the configuration worked. If you have all the values for the table above the testing should be simple. See the following article on how to properly test the SSRS configuration:

http://msdn.microsoft.com/en-us/library/ms143773.aspx - How to: Verify a Reporting Services Installation

Troubleshooting

If things go wrong it might be difficult to troubleshoot exactly what went wrong. The following articles will help turn on remote errors and logging:

http://msdn.microsoft.com/en-us/library/aa337165.aspx - How to: Enable Remote Errors (Reporting Services Configuration)

http://msdn.microsoft.com/en-us/library/ms157403.aspx - Reporting Services Log Files

Special Ports

When installing SSRS on a web site that does not use the default port (80) or uses host headers, it is sometimes necessary to make some modifications in the SSRS config files. See the following article on how to do this.

http://msdn.microsoft.com/en-us/library/ms155921.aspx - How to: Configure Reporting Services to Use a Non-Default Web Site (Reporting Services Configuration)

If you are running a custom port then add it into your RSWebApplication.config file as the following:

<ReportServerUrl>http://server:90/reportserver</ReportServerUrl>

K2 for Reporting Services Considerations

· Before you install K2 for Reporting Services on your SSRS server please make sure that SSRS is running in Native mode. Currently SharePoint Integrated mode is not supported.

· Authentication between SSRS, K2 Workspace and K2 Server can cause 401 – Unauthorized errors if not configured properly. Refer to the following table to determine how to set up authentication in your environment:

(Refer to KB article 171 (http://kb.k2workflow.com/Articles/KB000171.aspx) and

NTAuthenticationProviders Metabase Setting

Environment Configuration

SSRS Web Site

K2 Workspace Web Site

K2 Server

Single Server*

(K2 Server, K2 Workspace and SSRS installed on the same server)

NTLM

No SPN

NTLM

No SPN

No SPNs

One Component Distributed*

(K2 Server, K2 Workspace on one server SSRS on another)

Negotiate,NTLM

HTTP SPN

Negotiate,NTLM

HTTP SPN

HostServer SPN

K2Server SPN

Two Component Distributed

(K2 Server on one server, K2 Workspace on another and SSRS on yet another)

Negotiate,NTLM

HTTP SPN

Negotiate,NTLM

HTTP SPN

HostServer SPN

K2Server SPN

*It is recommended that the DisableLoopbackCheck registry setting be applied to all servers that contain two or more of these components on the same server. (http://kb.k2workflow.com/Articles/KB000098.aspx)

To configure the above, reference the following resources for more information:

http://kb.k2workflow.com/Articles/KB000171.aspx - Troubleshooting 401 unauthorized errors from the Workspace

http://www.k2underground.com/files/folders/technical_product_documents/entry21001.aspx - Security and Kerberos Authentication with K2 Servers

K2 [blackpearl] Getting Started.chm – K2 blackpearl Getting Started guide. (Part of K2 blackpearl installation files)

· If your SSRS server is on another server, then you still need to run the K2 installer on the SSRS server. Running the Configuration Manager from the K2 Server or K2 Workspace server will not install the K2 Reports on the remote SSRS server. After installing the K2 for Reporting component, run through the Configuration Manager to deploy the reports to SSRS.

· In order to use the K2 blackpearl Reports, the K2 Server Service account needs to be a Content Manager on SSRS. Make sure you add this account and give it this permission before attempting to run any of the reports.

· For users to be able to view the report from SSRS the user will need at least the Browser right to the specific report. Give the relevant users the Browser right to the reports so that they can view them. K2 Server will check the user’s permission on a specific process and will not display the report if the user does not have the View, View Participate or Admin right to the process.

· Tip: After the Configuration Manager has started on the SSRS server open a browser and navigate to the Report Manager site. This will load the JIT of the web site and when you test the connection to SSRS later in the wizard the response will be faster.

I hope this post has been helpful.

Feel free to comment on this blog entry with any specific questions on K2 blackpearl Reporting and SQL Reporting Services

Welcome

JohanL — Sun, 18 May 2008 11:23:00 GMT

Hi Everyone,

Welcome to the Infrastructure Spotlight blog.

From time to time I will share some helpful infrastructure related tips and tricks on this blog.

I will also try to make this a central blog where all users can find information on how to install and configure other products which will support and extend K2's functionality.

Look out for the first post on SQL Server Reporting Services coming soon!

Regards,

Johan Liebenberg