FileHold ’08.5/09/12 Reporting with Windows Authentication

Overview

This document provides a basic example of how to configure SQL Server 2005/2008 Reporting Services and the FileHold ’08.5/09/12 Reporting system. This document is provided for informational purposes only; FileHold does not provide free support for this optional integration with SQL Server Reporting Services. You will need to be familiar with SQL Server, SQL Server Reporting Services, IIS and the principles of Windows Authentication.

You will be using Windows Authentication to integrate SQL Reporting services with the FileHold Server and databases. This document assumes that IIS and SQL Standard or Enterprise are on the same Windows Server. You can also use this as a guide if you are setting this up for a 2 server system where SQL and WWW are on separate servers, however, this configuration is not tested by FileHold nor do we have information on how to set this up.

This document expects you to have strong SQL development skills with a competency in SQL Reporting services.

This document outlines one way to implement this. There are other ways but this document does not cover all possible scenarios nor does FileHold provide free technical support on these issues. You will want to ensure that Windows Integrated security for report data source connections is set in SQL 2005 Surface Area Configuration > For Features or in the relevant portions of SQL 2008 server.

This document uses SQL 2005 as an example, but it is a very similar process with SQL 2008.

What is needed to complete this document:

• Windows 2003 R2 with Service Pack 2 or Windows 2008 R2 x64.

• SQL 2005 Standard/Enterprise Edition with SP4 or SQL 2008 Standard R2 or SQL 2008 Enterprise R2 with reporting services installed.

• We recommend that you install an SSL certificate for the web server before beginning this so that reporting services can be run under SSL. We do not provide instructions on how to perform this task within this document with how to setup SSL and SQL Reporting services, but SQL documentation online with Microsoft should be helpful for this. The SSL certificate should exactly match the fully qualified name of the server (Servername.domain.com).

• FileHold 8.5 or FileHold 09 or FileHold 12 version is required.

You will need to download the correct package for the version of FileHold you are running from the FileHold download server. This file will contain the two reports that come with FileHold, and you can download the correct sample report file here:

FileHold 8.5 with SQL 2005 http://download-filehold.com/DEMO/FileHold085/FileHold8.5-SQL2005-Report...

FileHold 09 or FileHold 12 with SQL 2005 or SQL 2008 http://download-filehold.com/FileHold09-SQL2008-Report.zip

Once downloaded, please unzip this folder and place it somewhere accessible to the FileHold server where the SQL Reporting Services Manager web site is available. This package contains two report definition files:

• LibraryListReport.rdl
• SystemActivityReport.rdl

Installing the Reporting Services

Reporting Services are installed along with SQL Server 2005/2008.

If for some reason, SQL Reporting Services is not installed on your SQL Server, you can always run the SQL Server installation program again and select to install additional components.

Once complete, SQL Reporting Services will be configured automatically but requires additional setup, perhaps 10 minutes worth of work.

To install Reporting Services for SQL Server 2005/2008 you must have Internet Information Services installed and running on your computer (or the server on which you will install Reporting Services).

After installation, open the SQL Server folder in All Programs, then select\ the Configuration Tools folder under it, locate the Reporting Services Configuration Manager application and run it.

This application allows you to configure Reporting Services on your computer and it consists of a single form with 8 tabs, as shown in Figure 1. You shouldn't have to change the default settings, just go to the tab Web Service URL and make a note of the URL of the Report Server's Web Service. You will need to specify this URL in deploying new reports.

Once you setup the Reporting Services administration site, confirm that you can login and use the site. You can now proceed with Step 1, and work through the rest of the guide. If you cannot get SQL reporting services working correctly, or the administration site to work, please contact your Microsoft support team. The information below is provided as a courtesy, FileHold does not do SQL support, but instead give advice and best practices to people with working knowledge of SQL Server. 

Step 1

Open up SQL Server 2005’s Reporting Services Configuration Manager and work through all the steps below until you see the green checkmarks as below. Please note that this server was a SQL 2005 Express machine that was upgraded to SQL 2005 Standard using Microsoft’s upgrade feature within SQL 2005 Standard. The instance in the screenshot is still named SQLEXPRESS even though this is actually SQL 2005 Standard. Please note, this SQL Reporting configuration area can be frustrating at times to work through, while sometimes it is very easy.

 

Step 2

Once you have completed the Database setup as below, you can then open up SQL 2005 Management Studio to assign the proper access permissions and login credentials for the FH_Service account.

 

Since the FH_Service account already has access to all 5 databases in FileHold you just need to give it data reader access to the two Reporting databases (Report Server and ReportServer Temp DB).

Please note - use the FH_Service account here, the screenshots below show an FHReporting - this is not needed. They are to show that other configurations are possible, although out of scope for this guide. If you are experienced with SQL Reporting services - you are welcome to try your own configuration. Please note that FileHold does not provide free technical support with SQL Reporting.

 

Step 3

Browse to the home.aspx within the Reports site using IIS Manager. Right-click Home.aspx and select Browse.

Step 4

Once the reporting services page loads in, click the New Folder button. Create a folder called FileHoldReports and click OK.

Once complete you should then ensure you are in the FileHoldReports area as per below.

Step 5

Upload the two FileHold report definition files (LibraryListReport.rdl and SystemActivityReport.rdl) by clicking Upload File.

Once you have uploaded both report definition files, you will then see the two reports appear.

Step 6

Click New Data Source to add a data source for Library Manager database. Keep in mind that the Datasource referenced below should be adjusted to the server or server\instance used by the database server running FileHold.

 

Step 7

Add another data source for User Role Manager data source. Keep in mind that the Datasource referenced below should be adjusted to the server or server\instance used by the database server running FileHold.

You will then see the 2 report files and the 2 data sources appear in the FileHoldReports folder.

Step 8

1. Add the data source to corresponding Report. Click on the LibraryListReport and go to the Properties tab and the Data Sources section. Select the Library Manager data source and click OK and then Apply.

2. Click on the SystemActivityReport and go to the Properties tab and the Data Sources section. Select the User Role Manager data source and click OK and then Apply.

Step 9

Set permissions for Browser Role for FH Service.

1. Go to the SQL Server Reporting Services > Home and then click Properties.
2. Click New Role Assignment.
3. Enter in the FH_Service account information as being able to access reporting services using the browser role. Below the FH_Service has been added.

In the two screenshots below, the security properties for each data source is displayed.

Step 10

Test the LibraryList Report. You should see the Cabinets, Drawers and Folder selections within FileHold if all is setup properly.

Step 11

Test the SystemActivity Report. You should see the Client Type, Last Name, Login Time, User ID, First Name and Logout Time selections within your FileHold server if all is setup properly.

Step 12

Now you will continue with integrating this within the FileHold Web Client > Library Admin > Reporting Services > Reports.

1. Setup the name of the Report Folder in the Web.config in FH\FileHold\LibraryManager\Web.config file.
   In Windows 2008 Server, you will need to look in the Program Files\FileHold Systems\Application Server\LibraryManager folder for the web.config file.
2. This uses Windows Authentication so all you need to do is provide the exact name of the ReportServerFolder you created in SQL Reporting Services. See the example below.
3. Once you have edited the web.config file, restart IIS.

Step 13

1. Test within FileHold. Login as a Library Administrator or System Administrator to the FileHold Web Client and then click on Library Admin in top right corner of web browser.
2. Click on Reporting Services > Reports. In FileHold 09, you can setup Report security and distribute the reports to various users/groups in both the Web Client and Desktop Client.
3. You should then see this appear for LibraryListReport.

 

4. And this report will appear when you select SystemActivityReport.

 

See Also:

Technical Troubleshooting Guides