Installing Microsoft SQL Server 2005 Reporting Services on a non-default web site.

Reporting Services will be installed on a web server that is not running the SQL 2005 database engine. We will use a host header and a DNS name to connect to the web site.

Update; August 2, 2006: Microsoft has recently fixed their documentation for this. Their documentation now matches mine and is quite well written. I highly recommend working from Microsoft’s documentation. However, I’m going to leave this more detailed web page up for a while just incase you get stuck. Here is a link to the Microsoft document. How to: Configure Reporting Services to Use a Non-Default Web Site (Reporting Services Configuration)

Written: March 4, 2006

Last Updated: August 2, 2006

Author: Greg Van Mullem

This web page is part of a collection of web pages containing difficult to find technical information about the initial release of Microsoft SQL Server 2005 Standard Edition. Click here to go to the main index.

This document contains several fixes! You can skip ahead to step 7 (near the bottom) to see these fixes. These fixes are now fully documented in this recently updated Microsoft document. How to: Configure Reporting Services to Use a Non-Default Web Site (Reporting Services Configuration)

This installation will be used on our corporate intranet. None of the servers are accessible via the Internet. Users will connect to Reporting Services using IE and the following URL’s “http://Reporting/Reports” and “http://Reporting/ReportService”. Where “Reporting” is a DNS entry not the server name. Our production intranet web server has lots of web sites each with its own DNS name. Look for documentation on host headers in IIS for more information.

This document assumes a high level of technical skills. In this document I will be describing how I got it to work on 2 testing servers using MSDN licensed versions of the software. There are a lot of other possible configurations that might work as well. However this is the only one I have tested. This testing was done with the initial release of SQL Server 2005 before any service packs have been released. I highly suspect that some of the problems described here will be fixed in a future service pack.

Yes, this is a 50 page document! I though about just documenting the fixes (see step 7 near the bottom) but there is a good chance that these fixes will not work if other configuration items are different. What can I say, it’s a complex install and I wanted to make sure that EVERYTHING is completely documented.

Our database engine test server is configured as follows:

- Server Name: SQLLASTTEST (we did a lot of testing and ran out of good names).

- Operating System: Windows 2003 Standard Edition Service Pack 1. We ran a full Windows update on this server on approximately February 1, 2006. We ran this update before installing SQL Server.

- Server is not running IIS.

- SQL Server 2005 Standard Edition initial release before any service packs have been released.

- All options except for Reporting Services are installed on this server.

- Mixed mode security.

- All services are running under a domain login account.

Our web site test server is configured as follows:

- Server Name: SQLTEST5

- Operating System: Windows 2003 Enterprise Edition Service Pack 1. We also tested with Windows 2003 Standard Edition SP1 and it works the same. We ran a full Windows update on this server on approximately February 1, 2006. We ran this update before installing SQL Server Reporting Services.

- IIS is installed with a mostly default configuration (see screen shot below).

-  Our web server has only one IP address.

- The .NET 1.1 and 2.0 frameworks are both installed on this server. See the IIS Manager screen shot below.

Here is the add/remove programs on SQLTEST5 (the web server) before installing SQL 2005 Reporting services:

Here are the IIS configuration changes I made on SQLTEST5 (the web server) before installing. I’m pretty sure that Reporting Services requires an “Allowed” setting only for “ASP.NET v1.1”. However, our intranet web server has other unrelated sites that require all 3 types of ASP extensions.

Install and configuration steps:

Step 1. Create a login account in the SQL Server that Reporting Services will use to login. I’m using a SQL authentication login named “ReportingLogin”. For the duration of the install I will give this user the “sysadmin” server role. This will guarantee that Reporting Services has all the security rights that it needs to create and configure its databases. After the install you have to take these security rights away and replace them with something much lower! It’s a major security hole! Also, I’m pretty sure that the install can be done with less security rights but I don’t know what they are.

IMPORTANT TRICK: The default database for this login has to be master.

Here are a couple of screen shots of the login account:

This security setting is temporary for the install only:

Step 2. Use the CDs or DVDs to install Reporting Services on the web server. Here are some screen shots from the install.

Notice that I’m installing only “Reporting Services” on the web server.

Here is the advanced dialog that opens when you click the advanced button. I didn’t change anything in here.

Fill out the service account information. For security reasons I didn’t use my networks real values in this screen shot.

This screen will be grayed out because you are not installing the SQL Engine. Microsoft’s documentation calls this a “files only install”.

Here is the screen you get if you click the details button on the previous screen.

This is the final screen before the install begins.

This screen shows that the components were set up successfully. Make sure that you have nothing but little green check marks.

Step 3. Call your local system administrator and have him/her create a DSN entry named “Reporting” that points to your web server. I think my local system admin is using a something called a CNAME for this. I’m a programmer I don’t know all the details.

My web site will be named “Reporting” but any name that is not the server name will work. If you use the server name, then you are going to be installing under the default web site which works just fine but it’s not what I’m covering in this document. This document covers installing Reporting Services on a web site that is not the default web site (it’s a much more complicated install).

Also, for testing purposes you can modify your testing computers HOSTS file instead of creating a DNS entry. I’m using this approach for my testing.

Step 4. Create the non-default web site that Reporting Services will run under.

Step 4.1. I created the folders “C:\Websites\Reporting”. There are a lot of options for how to structure this. We run a variety of different web sites all under the common “Websites” folder. The “Reporting” folder is where the new Reporting Services web site and virtual directories will be located.

Step 4.2. Use “Internet Information Services (IIS) Manager” to create a new web site.

Here’s the new web site wizard welcome screen.

Enter “Reporting” for the Description.

Enter “Reporting” as the host header. Also, I saw some documentation that said that Reporting Services requires the IP address to be set to “All Unassigned”. That does not make any cense but, I’m going to leave the default “All Unassigned” setting just in case.

Yes, I know that I could have created the directories with this screen but it was easier to document by splitting it into separate steps. Also, turn Anonymous access off.

I’m not sure which of the top 3 check boxes need to be checked. However, I’m ok with checking all 3 just to make sure everything works. I have not seen any documentation on this part of the setup. You can always change this later. I also used to think that the Browse check box was required. After further testing it does not appear to be required. However, If you have problems you might want to try enabling directory browing for the web site and all virtual directories below it.

It’s done!

Here are the results.

Here are the relevant properties pages for the new Reporting web site.

I’m running the web site under ASP.NET 2.0. It should work under 1.1 as well but I have not tested it. If you run into problems then you might try changing this.

Step 5. From the Start menu run the Reporting Services Configuration Manager.

The Machine Name it is asking for is the web server not the database server (SQLTEST5 is my poorly named web server). It should default to the correct values so just click Connect.

Here is the initial screen. Make sure the Service Status is Running.

Step 5.1. Report Server Virtual Directory

Click the New button.

Change the Website to Reporting.

Here is the successful result.

Step 5.2. Report Manager Virtual Directory

Click the New button.

Change the Website to Reporting.

Here is the successful result.

At this point you can launch (or refresh) IIS Manager and see the 2 new virtual directories.

Step 5.3. Don’t change anything in Windows Service Identity.

Note: I edited the Service Account and Account fields in this screen shot with Windows “Paint” to hide my company’s internal network settings.

Step 5.4. Web Service Identity

Here is the initial screen. I’m confused as to exactly what the “ASP.NET Service Account” setting does so I’m leaving it at the default. Please research this and make you own decision. However, I will be creating a new application pool for the virtual directories.

Here is how I filled out the New button popup.

Then I selected the new application pool for the second Report Manager drop down. Here is the result page after I clicked the Apply button.

Here is a screen shot of these changes in IIS Manager. I also copied the aspnet_client folder from the default web site to Reporting. Some ASP.NET web sites need this. I don’t know about Reporting Services.

Step 5.5. Database Setup

Here is the initial screen.

When I click the New button this screen opens. Here is how I filled it out.

After I click OK the main screen updates to this. After this is done running it’s a good idea to verify that the databases were created on the SQL Server.

I finished filling out the screen as follows. I then clicked Apply.

It then prompted me for some information I already entered. I might have types the password wrong. Who knows?

Here is the final success screen.

Step 5.6. Encryption Keys

You have to backup the Encryption Key! It’s extremely important! When I first entered this screen the backup button was disabled (probably a small bug). I fixed it by clicking on Execution Account and then reselecting Encryption Keys. Here is the success screen.

Step 5.7. I’m not going to configure the Email Setting or Execution Account right now so I’m simply going to click the Exit button in the lower right corner!

Step 5.8. Initialization: This option is always disabled (greyed out) for me. I don’t know why or even what this screen is supposed to do. Looks like a bug? However, it is showing the success check mark icon.

Step 6. At this point if you try to access the Report Manager web site you will get the following error web page. In previous tests I have seen other error messages as well. Also, the ReportService directory will give a standard “The page cannot be found” error.

The attempt to connect to the report server failed.  Check your connection information and that the report server is a compatible version.

Step 7. Apply the fixes! All of these fixes are done on the web server.

Step 7.1. Go to the following directory:

C:\Program Files\Microsoft SQL Server\MSSQL.1\Reporting Services\ReportServer\

Use Notepad to open the file: rsreportserver.config Do a text search for your web server name. In my case it’s sqltest5. You should find only one occurrence of this text.

Replace the web server name with your web site name. In my case it’s Reporting. Save the changes and close Notepad.

Step 7.2. Go to the following directory:

C:\Program Files\Microsoft SQL Server\MSSQL.1\Reporting Services\ReportManager\

Use Notepad to open the file: RSWebApplication.config This is a very small file. Make sure word wrap is turned off in Notepad. Here it is before I make the change:

You need to edit lines 3 and 4 as follows. Also confirm line 5 is set to FullTrust.

Line #3 <ReportServerUrl>http://Reporting/ReportServer</ReportServerUrl>

Line #4 <ReportServerVirtualDirectory></ReportServerVirtualDirectory>

Line #5 <ReportBuilderTrustLevel>FullTrust</ReportBuilderTrustLevel>

Here is a screen shot after the changes are made. Save the changes and close Notepad.

Step 7.3. Disable loop back checking. I’m pretty sure that this step is only necessary with Windows 2003 SP1. In regedit add/change the value DisableLoopbackCheck. The type is dword and the value is 1.

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa]

"DisableLoopbackCheck"=dword:00000001

Step 7.4. At this point, if you try to access the web sites URL’s with Internet Explorer it might work and it might not. If it doesn’t work then you will find the following error in the NT Event log. There is probably a variety of ways to solve this. The system admin here used the Component Services administrative tool to give the Network Service account the proper permissions which stopped the error. This appears to be a pretty common error and there are a lot of other web sites that document solutions for it.

Step 8. Reboot the web server to make these changes effective. I’m not sure if a reboot is required here or not. I had to reboot during 1 of my many test installs. Other times I did not have to reboot.

Step 9. Try the web sites and watch them work! Here is what they look like.

The URL http://Reporting/Reports will redirect you to here.

Here is the ReportServer web site.

Step 10. Reduce the security privileges for the SQL Server ReportingLogin account. Do not leave this account with sysadmin privileges. I have not been able to find any documentation on what rights this account requires. So I’m not going to risk giving you bad information!

The End…