Installing and Configuring SQL Server Reporting Services

This chapter is from the book

Hitchhiker's Guide to SQL Server 2000 Reporting Services

Learn More Buy

This chapter is from the book

This chapter is from the book 

Hitchhiker's Guide to SQL Server 2000 Reporting Services

Learn More Buy

Step by Step: Installing Reporting Services on a Server with IIS

With SSL in place, you're ready to proceed with the Reporting Services Setup. As we discussed earlier, the default prerequisites for the Report Services installation specify the target machine has IIS, ASP.NET 1.1, and a Default website (with an SSL web server certificate). So, we are working with either Windows 2000 Server, Windows Server 2003 configured as an Application Server, or Windows XP with IIS enabled. You'll also need connection credentials to an instance of SQL Server 2000, which has at least Service Pack 3a applied. Make sure you setup a properly permissioned login ID. To do all of this, you're going to need to use the sysadmin role ^[11] as described below, but only for the installation. This means that if you don't have sufficient permission, you need to get your SQL Server SA to do this for you.

If you are installing Reporting Services on a machine that:

• Belongs to an Active Directory Domain

• The SQL Server hosting the Reporting Services catalog database is in the same Domain

• You haven't messed around with your SQL Server security settings

• You are installing under an account that has Admin privileges

If all of the above are true, the major roadblocks to a successful installation have been addressed. However, if you stepped off that line, encountered some setup issues, or want to perform unattended installs, you might have to return to the earlier section that discusses the issues you're likely to encounter.

Stepping Through the Setup Wizard

The Component Update dialog appears when you first start the Reporting Services Setup wizard. It automatically checks to make sure that the "Microsoft SQL Server Reporting Services Setup Support Files" and other dependencies are installed. This dialog also runs when you restart Setup to uninstall or reconfigure Reporting Services. According to readme_en.htm, you'll have to have ASP.NET 1.1.4322 registered with IIS. You'll also need to ensure that MDAC 2.6 or later has been installed, which should not be a problem as this version of MDAC (or later) is included with all of the recent versions of Windows and is installed with Visual Studio .NET.

This dialog is followed by a Registration Information dialog asking you to personalize your installation. There's no clear use for this information, but we expect it's salted away in the registry somewhere.

Next, you'll see a Feature Selection dialog, which permits you to select how much of Reporting Services should be installed and where to install it. This dialog determines whether the Server components, the client components, or both should be installed. If some prerequisites are not met, the features that depend on them are missing from the component tree. For example, no IIS means no Server features, and no Visual Studio means no Report Designer. If you're installing Reporting Services on a development system, we strongly suggest including the Reporting Services Samples and AdventureWorks database, as we will be using these to illustrate how Reporting Services works. You don't have to install Reporting Services onto the machine hosting the SQL Server Report Server database catalog (but the machine will need a SQL Server license).

Remember, all you need for Reporting Services is a machine running IIS server, ASP.NET 1.1, and a Default website with an SSL web server certificate. This system can be Windows 2000 Server or a Windows Server 2003 configured as an Application Server. It can even be a Windows XP system if you install IIS. The Reporting Services Setup program will check to make sure most of these requirements are in place when first started—all except the SSL certificate. Because you might not have an SSL web server certificate, we'd better tell you what you need to know about Secure Sockets Layer (SSL).

Reporting Services Windows and Web Service Account Installation Options

The next dialog (shown in Figure 2.2) specifies the accounts under which the ReportServer Services will run. This dialog can sometimes seem a little tautologically ^[12] confusing, and it's easy to lose track of which service uses which account, so let's take a deep breath and make a start.

• The ReportServer Windows service, a Windows service, can execute under a number of accounts, depending on which operating system it's running on. On Windows 2000 and Windows XP, you can configure this to be either a Domain Account or the Local System Account (NT AUTHORITY\ SYSTEM). On Windows Server 2003, there is an additional choice of running this with the Network Service Account (NT AUTHORITY\NETWORK SERVICE). We recommend that you use the Local System Account on Windows 2000 and Windows XP and that you choose Network Service Account on Windows Server 2003. This choice will make more sense in a minute.

• The Report Server web service is presently an ASP.NET program, so by default it's run under the account configured for the ASP.NET Worker Process (aspnet_wp.exe). On Windows XP and Windows 2000, this (by default) is a local user account on the machine called ASPNET. On Windows Server 2003, the ASP.NET Worker Process is run under the Network Service Account (NT AUTHORITY\NETWORK SERVICE). If you have changed this account in machine.config, chances are Setup will not be able to run correctly.

Figure 2.2 Selecting the Account for the ReportServer Windows Service

There is also an account that we need to concern ourselves with—this is the account whose credentials the Report Server web service and the ReportServer Windows service both use to access the Report Server database. If you followed our recommendation and are running the Windows Report Service under a Local System or Network Service, you have several choices for the credentials for the Report Server web service:

1. The same account that the Windows Report Service is running under

2. A Domain User Account

3. A SQL Login account

If you are running Windows 2000 but didn't accept the recommendation, and you decided you knew better and used a Domain Account for the ReportServer Windows service, your only option is to access the report database using SQL Login credentials or use the same Domain Account that you specified for the Windows service. In addition, your reports will not be able to use prompted or stored Windows credentials to access remote data sources; they would have to use SQL credentials. If you use a SQL account, Setup creates it if it does not exist and assigns the correct SQL permissions to it. It also assigns the required SQL permissions to any account you use. However, using a SQL Login can be considered a security vulnerability unless you also have enabled Encryption on your SQL Server. Why? We knew you were going to ask that (again). This security vulnerability is mostly due to the fact that SQL Login credentials are not as secure as trusted connections or Domain credentials, which have more security features built in—things like making sure passwords are unique and are replaced regularly, and SQL Authentication is much easier to crack than Windows Domain Credentials from a hacking point of view. Another factor here is the cavalier attitude some folks have when passing around and using the SQL Server SA account password. Ah! You mean, why can't you use a Domain Account? Well, the answer is that it is a limitation of Windows 2000, and also the infrastructure of Reporting Services—so, it is because it is. . unfortunately.

If you are on Windows Server 2003, then party on, but use the Network Service Account (NT AUTHORITY\NETWORK SERVICE) rather than the Local System Account (NT AUTHORITY\SYSTEM) for the Reporting Services Windows service, as it has fewer privileges but is sufficient for Reporting Services and is therefore somewhat more secure.

Auto-Starting the Reporting Services Service

At the bottom of the Service Account dialog, you'll find a checkbox that specifies whether or not to auto-start the service. If you uncheck this box, the Reporting Services service will have to be started on first use. For development systems, this might be a good idea to reduce the amount of overhead the service consumes.

Choosing the Reporting Services Virtual Directories

The next dialog you'll encounter in the Reporting Services Setup wizard asks you to specify the virtual directories on which Reporting Services and Report Manager are accessible (as shown in Figure 2.3). Throughout the book and DVD examples, we'll use the defaults of "ReportServer" for the Reporting Server virtual directory and "Reports" for the Report Manager virtual directory.

Figure 2.3 If You Don't Have SSL Installed, You Won't Get Past This Installation Dialog Without Removing the Check from "Use SSL"

You may have already gathered that the Report Manager and the Report Server are installed in virtual directories on the Default website of an IIS server. By default, Reporting Services Setup assumes that the website that you want to install on has a web server certificate issued to it and supports SSL. This is great if your website supports SSL, but it may possibly send you off in a diversionary spin if it doesn't, and on a substantial time-consuming learning curve if IIS configuration minutiae are outside of your hitherto core experience. This is especially true if you don't have an experienced IIS administrator on hand to bully or cajole into setting up the SSL for you.

Unless you uncheck the Use SSL checkbox in the installation wizard (which is shown checked in Figure 2.3), you won't be able to install with the wizard—that is, unless you have SSL already installed. Take heart, because Appendix A will give you a boost with instructions and a Guide me! video, and if that still isn't enough to get you going we offer correspondence and onsite consultancy services for a modest fee.

Why Is Secure Sockets Layer (SSL) Important?

SSL provides the ability for an IIS server to encrypt network traffic, and makes it possible for a browser to interact with a website (securely) using HTTPS. Web sites should be using HTTPS and SSL when you provide your credit card details. The theory is that if the network traffic is encrypted it makes it more difficult for anyone who intercepts that traffic to decrypt it—assuming that they don't possess the keys. The cost of having SSL (apart from the certificate mentioned in Appendixes A and B) is a slight dent in performance.

In the Reporting Services context, one important role for SSL is effective encryption of any user credentials that might need to be passed to Reporting Services to enable it to gain access to a managed report's underlying data. While there may be other approaches and better practices, which we'll talk about elsewhere, it is possible to pass these credentials in the URL or in an HTTP POST. If you don't have SSL and therefore are not using HTTPS, those credentials will traverse the network in plaintext and be a trivial exercise for evil folks to harvest.

As we said in Chapter 1, you'll discover that in order to access many of the more important SOAP interface web methods, you'll have to have SSL enabled on the IIS server. If you choose to write your own custom applications using the SOAP methods, you'll find you have secure and complete access to the whole SOAP interface, but only if you have SSL enabled on the Reporting Services website.

Appendix A walks you through the process of setting up SSL on your website and provides a great deal of detailed information on the minutiae you might need to consider if you haven't done this before. Appendix B is an MSDE article we wrote on installing SSL and a certificate service that might also prove beneficial. We're going to assume from this point forward that you've created and installed an SSL certificate for your website.

Selecting the Report Server Database and Credentials

The next dialog exposed by the Reporting Services Setup wizard (as shown in Figure 2.4) asks you to specify the SQL Server to host the Reporting Services database. Remember to address the correct instance using the <instance>\<server name> notation. Next, supply (or accept) the name of the Reporting Services database—the default is "ReportServer." If the wizard finds this database on the Report Server, it stops and demands that you change the name or remove the existing database. Otherwise, it creates a new ReportServer (2.31 MB) and a ReportServerTempDB (1.24 MB) database. As we'll discuss later, if you uninstall Reporting Services, the database is not removed (among other things) so you'll have to clean up your system after the uninstall wizard runs.

Figure 2.4 Select the Report Server Database and the Domain Account That Accesses It

SMTP Mail Configuration

The next dialog you'll encounter (shown in Figure 2.5) asks you to specify how Reporting Services should contact you via e-mail. It's important to establish these linkages so that Reporting Services can pass reports via mail and notify you if there are problems with the server. This section discusses how to specify the SMTP and "From" address as well as how to edit elements in the RSReportServer.config file to ensure mail is delivered as expected.

Figure 2.5 SMTP Setup Dialog

As discussed in Chapter 1, Reporting Services supports a number of delivery extensions, including SMTP, ^[13] that can be used to send reports via e-mail. During the Setup process, you'll be asked to configure two parts of the SMTP configuration: the SMTP server and the e-mail address that the Report Server will use as a "From" address. Note that the SMTP server address is either an IP Address or a resolvable network name to an SMTP service.

If you want to enhance your From address, you can provide a free-text name and specify the e-mail address in < > brackets, as shown in the Setup dialog in Figure 2.5. We've provided "smtp.boost.net" as our SMTP server address. We also used "Boost Report Server <Reports@boost.net>" as the From address. However, we suggest that you don't use these addresses for your server as the boost mail server won't guarantee that you'll get your e-mail—use your own SMTP server address.

When e-mail arrives in the destination mailbox, it shows (in this case) as being from "Boost Report Server" and masks the e-mail address (as shown in Figure 2.6). If you have a web farm installation, you could use different names with the same e-mail address in the From address—but if that's the case, be sure to see "Installing Reporting Services on a Web Server" later in this chapter.

Figure 2.6 Reports in an E-Mail Inbox with Enhanced "From" Names

No, you don't really need to create an Exchange mailbox for the From address—that is, unless you want to monitor the "non-delivery" reports sent back to this e-mail address, or read the replies your users might send the Report Server.

Once the SMTP installation phase is over, the only way to change the SMTP server and the e-mail address is to edit elements in the RSReportServer.config ^[14] file under the Report Server Email extension node. Shown in Listing 2.1, on the next page, is the raw default configuration resulting from the SMTP installation wizard. If you want to enhance the e-mail name after having already run the wizard, notice that "<" is represented as &lt; and ">" as &gt;, just like in HTML!

Example 2.1. The RSReportServer.config File

<Extension Name="Report Server Email" Type="Microsoft.ReportingServices.
EmailDeliveryProvider.EmailProvider,ReportingServicesEmailDeliveryProvider">
  <MaxRetries>3</MaxRetries>
  <SecondsBeforeRetry>900</SecondsBeforeRetry>
  <Configuration>
    <RSEmailDPConfiguration>
      <SMTPServer>smtp.boost.net</SMTPServer>
   <SMTPServerPort></SMTPServerPort>
   <SMTPAccountName></SMTPAccountName>
   <SMTPConnectionTimeout></SMTPConnectionTimeout>
   <SMTPServerPickupDirectory></SMTPServerPickupDirectory>
   <SMTPUseSSL></SMTPUseSSL>
   <SendUsing></SendUsing>
   <SMTPAuthenticate></SMTPAuthenticate>
   <From>Boost Report Server&lt;Reports@boost.net&gt;</From>
   <EmbeddedRenderFormats>
   <RenderingExtension>MHTML</RenderingExtension>
   </EmbeddedRenderFormats>
   <PrivilegedUserRenderFormats></PrivilegedUserRenderFormats>
   <ExcludedRenderFormats>
   <RenderingExtension>HTMLOWC</RenderingExtension>
   <RenderingExtension>NULL</RenderingExtension>
   </ExcludedRenderFormats>
   <SendEmailToUserAlias>True</SendEmailToUserAlias>
   <DefaultHostName></DefaultHostName>
   <PermittedHosts></PermittedHosts>
   </RSEmailDPConfiguration>
   </Configuration>
   </Extension>
   

We don't actually like the default installation for e-mail because after <MaxRetries> at set intervals the mail can fail. We prefer using an alternative that makes use of the SMTP Windows service and its pick-up directory, as this can be more robust and takes the pressure off Reporting Services to make delivery. We tell you how you can configure that in Appendix C, "Configuring SMTP."

Report Server Samples Setup

If you checked the samples option in the Feature Selection dialog, the Setup wizard asks you where the 72.63 MB AdventureWorks2000 database should be installed. Normally, this is installed on the Report Server host SQL Server, but it can be sited anywhere there's room and connectivity. As with the earlier steps, the user running Setup must have permission to create databases on the specified SQL Server instance. If this database exists on the target instance, you'll have to delete it before proceeding or point to another target server.

Licensing Mode

The next dialog should be familiar to anyone who has installed SQL Server before—it asks you to specify how you intend to license Reporting Services.

Ready to Install

Up to this point, the wizard has done nothing as far as actually installing anything on the target servers. When you click Install on the Ready to Install dialog, the wizard completes the Setup process. Once the installation wizard completes, you might want to take a few more steps to further improve the security of your Reporting Services installation. If you installed and accepted SSL (by default), any credentials passed back and forth between the server and the browser are protected by SSL and are secure. However, if the reports themselves are of a sensitive nature, you might want to force all communications to be over SSL, including managed report delivery. To achieve this degree of security, there are a number of additional steps you need to take:

• The RSWebApplication.config file <ReportServerURL> element contains the correct URL for your Report Server.

• The RSReportServer.config file has the SecureConnectionLevel set to 3.

• The RSReportServer.config file has the correct URL for your Report Server.

The location of these files and details on how to change them is located in the section "After Installation—Tuning and Reconfiguring," later in this chapter.

As we've said, Reporting Services installation can be a fairly complex operation (especially if you want to protect your data), so there are a few things that could go wrong. We have seen many of these issues as we've worked extensively with the Setup wizard and configuration files, so we should be able to help you build a safe server. To help validate your server installation, we provide an outline of steps to verify that you're ready to start creating, deploying, and publishing reports. See "Testing the Installation" near the end of this chapter.