Installing and Configuring SQL Server Reporting Services
- Installation Pathways and Preparation
- Step by Step: Installing Reporting Services on a Server with IIS
- Installing Reporting Services in a Non-Default Website
- After InstallationTuning and Reconfiguring
- Testing the Installation
- Installing Reporting Services on a Web Farm
- Removing Reporting Services
- Reporting Services Documentation Refresh
- Reporting Services Service Pack 1
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
As we discussed in Chapter 1, Reporting Services is dependent on ASP.NET version 1.1 hosted on IIS, and on SQL Server 2000 SP3a Standard Edition and later. Reporting Services won't work, and is not supported, on SQLExpress or MSDE Editions of SQL Server, and there's a different version specifically for Yukon (SQL Server 2005). Sure, you can use SQLExpress, MSDE or a non-SQL Server database as a data source—just not as the Reporting Services Catalog.
In this chapter, we discuss various installation setups you can use to install and configure Reporting Services. For the most part, this process is managed by the Setup.exe installation wizard, so expect to be prompted for a number of configuration options that determine how, where, and whether each segment of the Reporting Services package will be installed. We know that there are a variety of ways to install Reporting Services, so we've tried not only to address the common case, but also provide hints and techniques to be used for some of the more sophisticated installation scenarios. To make this process as painless as possible, we've broken this chapter down into several sections:
-
Installation Pathways and Preparation: This section discusses things you need to consider for each of the configuration scenarios—typical or not. Here we show additional details and considerations regarding SSL configuration and other security issues, separate IIS and SQL Server systems and instances, and using the command-line installation option.
-
Step by Step: Installing Reporting Services on a Server with IIS: This section assumes a typical development machine running IIS, SQL Server, and a Visual Studio .NET tool. It walks through the Setup wizard step by step and discusses how to respond to each dialog.
-
After Installation—Tuning and Reconfiguring: This section discusses the various configuration files and the command-line tools used to manipulate the files and perform other security and maintenance tasks. If you choose to use Secure Sockets Layer (SSL) (as we recommend) and you also decide to force SSL for all communications with the Report Manager and Report Server, you should revisit the Tuning and Reconfiguring section after the installation wizard completes, which allows you to tune the config files and change the SecureConnectionLevel.
-
Testing the Installation: This section shows step by step how to verify that your Reporting Services installation is ready to produce reports. Testing helps you install the sample reports using Visual Studio .NET and verify that you can successfully deploy and retrieve reports with a browser.
-
Installing Reporting Services on a Web Farm: This section walks through the process of setting up Reporting Services on a web farm. This configuration uses several linked IIS servers to share the same Report Server database.
-
Removing Reporting Services: In case you need to un install Reporting Services, this section discusses the mechanisms and side effects of the tear-down process.
Installation Pathways and Preparation
Before we get started, we need to mention the most important prerequisite: the website that hosts the Reporting Services ought to support SSL and therefore needs an SSL web server certificate. In Appendixes A and B, we've included a detailed walkthrough and an article written for MSDN that should prove useful to you if you need further guidance in installing an SSL web server certificate and (if necessary) setting up your own certificate services. Be sure to check out the DVD content as well because we've provided plenty of short media clips on different aspects and issues associated with installation and configuration.
There are two major parts of installation: Server components and client components. The client components are needed by developers to design reports, provide some report programming and database samples, as well as provide a suite of command-line tools to administer the Report Server. These client components are not needed for your users to run reports—all they need is a web browser (HTML 3.2 and later).
Operating System Choices
Reporting Services can be installed on the recent breeds of Microsoft operating systems as shown below. We categorize these in two groups: "server" and "workstation" operating systems. Server operating systems include Windows Server 2003, Windows Small Business Server 2003, and Windows 2000 Server. Workstation operating systems include Windows XP Professional and Windows 2000 Workstation.
For test and development situations, we feel that workstation operating systems are suitable platforms, and Windows XP Professional can be an ideal platform for a single machine where you can safely install all of the Reporting Services components and expect reasonable performance and up-to-date functionality. However, take note that Windows XP has a default maximum limit of 10 concurrent connections to its IIS server. This is usually adequate when you're testing as a single user but can prove to be very limiting and frustrating in some development situations. For example, as we'll see in Chapter 4, you may hit this barrier of 10 connections when you want to test accessing the Report Server and Report Manager concurrently under the context of different users. Consider that each web browser session consumes at least two IIS connections and often as many as four. All too frequently, we've seen that connections can hang and block the IIS server, which reports this stop page by returning a 403.9 error code message (Too many users connected). Sometimes, even after the performance monitor (Perfmon) shows that all connections to IIS have been released, new connections still generate strange errors.
To address this issue, the first and most important thing we recommend for production environments is to use an operating system from the group of server OSs listed above. These server-class operating systems are designed for "serving" and don't have a limit on the possible number of connections to IIS. We recommend that you use either Windows Server 2003 or Windows 2000 Server for your production server. However, Windows Server 2003 is more secure.
If you must use Windows XP and want to address 403.9 errors that have blocked the IIS server, you can take either of two approaches, depending whether or not you want a treatment or cure. The "treatment" approach is to restart IIS, either at the command prompt with the IISRESET command, through the Services MMC snap-in or through the IIS MMC snap-in. Restarting IIS usually alleviates the problem—albeit temporarily. The "cure" is to delve into the Windows XP IIS metabase and increase the maximum connection limit to no greater than 40 connections. No, you can't go beyond 40 connections because IIS is hard-coded to explicitly slam you directly back down to 10 if you try to go above 40. In our development situations, we found it frustrating to work with the 10-connection governor for any extended period of time, but once raised to 40 in normal development scenarios we found it adequate to resolve IIS 403.9 (Too many users connected) errors. See Appendix D for the steps to take to update the IIS metabase. Microsoft also suggests that you make the IIS connection timeout shorter, and we show you how to do that in Appendix D.
Installing Reporting Services on Domain Controllers When installing Reporting Services on a Windows 2003 server that is also a domain controller, no manual configuration is necessary for Reporting Services to install and run properly. However, on a domain controller on Windows 2000 Server, while Reporting Services installs properly it is not automatically activated. In this case, you'll need to perform the following steps, either before or after running Setup, in order to properly configure Reporting Services to run on a domain controller:
-
Grant Impersonate Privilege to the IWAM_<machine>account. For more information, see the Knowledge Base Article "IWAM Account Is Not Granted the Impersonate Privilege for ASP.NET 1.1 on a Windows 2000 Domain Controller with SP4" (KB 824308).
-
Remove the IWAM_<machine>account from the Guest group. Guest users cannot store or maintain encrypted content. For more information, see the Knowledge Base Article "Roaming Profiles Cannot Create Key Containers" (KB 265357). Then reboot the computer.
All on One Machine (Typical Development Scenario)
|
One approach to installation is to simply execute [1] the Reporting Services Setup.exe without using command-line arguments and install everything on a single machine, accepting all the wizard's defaults. (In this case, you need to do this with Local Administrator privileges.) The Setup.exe installation wizard inspects the local machine and performs an initial check, looking for any "missing" components, some of which the wizard might be prepared to update for you—for example, the .NET Framework, although it will only report other missing dependencies in a dialog when it cannot find a qualifying version of SQL Server 2000. These components are "missing" based on the bold assumption that your intention is to install the Reporting Services, Report Designer add-in utilities, documentation, and sample reports all on the same machine. This is the simplest installation approach, assuming you are targeting a development machine that already has the following Setup: |
-
A Visual Studio .NET 2003 Development Tool installed (e.g., Visual Basic .NET 2003)
-
SQL Server 2000 Developer Edition [2] or better with Service Pack 3a
-
Internet Information Server (IIS) with ASP.NET 1.1 [3]
-
An enabled Default website with an SSL web server certificate installed
It's best to make sure the IIS service is running on your system before installing Reporting Services. (IIS is called the "World Wide Web Publishing" service in the Services management console (MMC)). If you already installed Reporting Services (before IIS), you'll need to use a command prompt. Navigate to C:\Windows\Microsoft.NET\ Framework\v1.1.4322 and execute aspnet_regiis –i. to register ASP.NET in the IIS metabase.
Setting up a machine with these prerequisites [4] should be straightforward enough. The only thing that might trip you up is the installation of an SSL web server certificate that we casually slipped in there—especially if you've not had much exposure to IIS and Certificate Services. Please, don't install Reporting Services without an SSL certificate. Yes, the wizard will let you uncheck Require an SSL Certificate, but be prepared to tell your manager why you needlessly (and carelessly) exposed your data and potentially your systems to the world. Keep in mind that there are situations in which user credentials (logon name and password) need to be passed to and from the Report Server and Report Manager—even if you're simply updating data sources with the Report Manager. If you don't have SSL installed, those credentials can be harvested by evil people. Think about how careful you should be when entering credit card details into a web form. The same degree of care should be applied to any user credentials that you enter into web forms such as the Report Manager. Microsoft gives you all the tools and the Setup wizard defaults to remain secure, so there can be no excuse if you choose to be reckless. Sure, Reporting Services works without SSL, and perhaps in a development environment you might be tempted to go without this degree of protection—but we still don't recommend this approach. If you've not used SSL in production before, it would be a good idea to experiment with it in a development environment first
It's possible to separate the installation into several phases and place the Report Designer on machines where Visual Studio .NET 2003 is installed (and licensed), and install the Reporting Services (Report Manager and Report Server) on any machines running IIS. In a web farm environment, you can have several machines hosting the Reporting Services Server components, all accessing the same Report Server database catalog. The present license arrangements (as we understand them) require you to have a SQL Server license (either per seat or per CPU [5] ) for each server that hosts the Reporting Services Server components (Report Manager and Report Server), whether or not you have SQL Server installed on that machine. In the case of per seat licensing, that means each user that accesses the Reporting Services or the Design Tools will need a CAL (Client Access License). No, we're not licensing experts (or lawyers), and of course licensing is a complex subject, so read your licensing conditions very carefully.
I know Bill Vaughn has worn out three pairs of spectacles going over the minutiae of the United States EULA [6] paragraph 12, rereading and rereading it. Beats counting sheep, doesn't it, Bill?
—Peter
If installation doesn't go smoothly, you may need to use some of the command-line administration tools and delicately futz [7] with some configuration files. We explain those tools in brief later in this chapter, but we can't cater to every single caveat here, especially because new caveats can be created by Service Packs. For the latest information, take a look at our website (www.SQLReportingServices.NET) and join our community, as we'll be doing our best to help folks out there stay current on evolving Reporting Services technology.
Well, did you notice in paragraph 54 that the EULA grants rights to your first-born child in servitude to Microsoft?
—Bill
Installing Only the Report Designer Add-In
|
The Report Designer add-in for Visual Studio .NET is used to interactively design reports (as we discussed in Chapter 1). If you want to install just the Report Designer add-in to a target machine, run the Reporting Services Setup.exe file, but ensure beforehand that you already have a Visual Studio. NET 2003 Development Tool like Visual Basic .NET installed. Don't worry when the wizard reports that ASP.NET, IIS, or the Default website are not installed if you deliberately don't have them on the target machine—they aren't needed to install and use the Report Designer add-in. On the wizard's Feature Selection dialog, just choose that you only want the Report Designer. It's probably a good idea to install the Books Online (BOL) help file as well because it can be hosted in the Visual Studio .NET designer. In Chapter 4, we'll show you how to customize your Visual Studio .NET Help search interface to find topics in the Reporting Services help files. |
You may be asked for a 25-character CD key if the wizard can't detect a qualifying version of SQL Server on the target machine—so make sure you have the CD key from your SQL Server handy. (If you've mislaid the hard copy of the CD key—you should be able to locate it in the registry of the SQL Server machine under HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\80\Registration:CD_Key). We'll first use the Design Tools in Chapter 3 when we show how to use Design wizards, and we really go to town with the Report Designer in Chapter 6. After installation, if you have issues deploying reports, and you're getting messages like: "The underlying connection was closed: Could not establish trust relationship with remote server." chances are you need to deploy using HTTPS and include the same URL embedded in the SSL web server certificate.
Installing Only the Sample AdventureWorks2000 Database
You can install the Sample AdventureWorks2000 database on a target SQL Server by running Setup on the server and selecting just the AdventureWorks2000 database from the Feature Selection dialog (see Figure 2.1). After the AdventureWorks2000 database is installed, only administrators will be able to get at the data to produce reports from it, so don't forget to add domain groups to the database and at least assign them to the SQL Server db_datareader role.
)
Figure 2.1 Select the Portions of Reporting Services That You Wish to Install
Installing Reporting Services on a Server with IIS
In a production environment you probably won't want to install the Sample Databases or the Design Tools onto a production SQL Server or IIS server. However, before you run Setup (the installation wizard), step through this checklist:
-
Decide which IIS server is to host the Reporting Services. Initially, the IIS server must have the Default website enabled.
-
Check that the IIS server has an SSL web server certificate. If http://<Server>/Postinfo.html returns a web page, but https://<Server>/Postinfo.html does not, you need to install an SSL certificate. For detailed information on installing SSL, see Appendixes A and B and watch the video clips.
-
Decide where the Reporting Services' own catalog database is going to be installed. This needs to be SQL Server 2000 with at least SP3a—not Yukon, MSDE, or Personal Editions. This SQL Server need not be on the same server as the IIS components, but if it is, you economize on the SQL Server licences that you'll need.
-
Do you have sufficient privileges on your database? Make sure you have an account that is a member of the sysadmins role on the SQL Server to use during installation.
-
If you need to run Setup with command-line options, consider our security caveats, and especially consider encrypting any Setup ini file.
-
Launch the installation wizard from a non-network path.
Which Account Is Running the Install Wizard?
During installation of Reporting Services, the bootstrapper installation wizard logs on to the SQL Server used to host the Report Server catalog databases with the wizard's account credentials, using "Trusted" connection SSPI security. This means the target SQL Server must expose a login account that corresponds to the rights granted to the user running the wizard. As we said earlier, it's easiest if the wizard is run with Administrator credentials as SQL Server automatically creates a login account for all system administrators. If the domain administrator won't let you have access to the Administrator account and you choose to use an ordinary Domain User account, it must belong to the System Administrators (sysadmin) SQL Server Security "Server Role" (at least during the install). The wizard will check up on you to ensure that it is and won't continue if it isn't. [8] (If you're a trainee SQL Server guru, you might think that the Database Creators (dbcreator) SQL Server Security "Server Role" would be sufficient. However, the bootstrapper installation wizard also needs to make a couple of calls to the sp_addrole stored procedure, in addition to creating the Reporting Services databases. Accounts that are only members of the Database Creators (dbcreator) role can't do that.)
Of course, using a trusted connection is by far the most sensible route from a security standpoint, but there may be situations where you have the SQL Server in one Active Directory Domain, the Reporting Services in another Active Directory Domain, and no formal Active Directory Trust between the two Domains. Perhaps you have no Active Directories at all. In this case, it means that you won't be able to add the Domain User Account under which you are running the installation wizard to the SQL Server System Administrators SQL Server Security "Server Role." Thus, the wizard won't be able to create the database catalogs. We told you it would be easier just to steal the Administrator's password. Don't worry, you are not pooched—there is a solution. You can instruct the wizard to use a SQL account that belongs to the sysadmin role instead. To do this, launch the Setup installation wizard and provide a few command-line options: RSSETUPACCOUNT and RSSETUPPASSWORD. We discuss these options in the next section.
Security Bulletin 1
If you put sensitive information into an ini file—things like the credentials of a SQL Server sysadmin account, as when setting the RSSETUPACCOUNT and RSSETUPPASSWORD options, practice safe computing. Setting ACLs on files should be your first line of defense, but you should also be concerned about what happens to those files when they are deleted.
Before you edit and save any values in your copy of the template file, encrypt your copy of the file (right-click it, select Properties, on the General tab click the "Advanced" button, and then select Encrypt contents to secure data). In addition, consider the permissions on this option file. Why? Well, if you don't follow our advice you might as well just write the SA password on a Post-it note and stick it on the monitor for all to see. Consider that when you delete an unencrypted ini file, any credentials or other information it contained could be easily "harvested" from the disk. A wealth of utilities floating around the Internet (for free) are for doing just that, as anyone watching one of the crime shows on TV would know.
Security Bulletin 2
We also hope that if you are going to be using a SQL Server account for the Setup (i.e., setting the RSSETUPACCOUNT and RSSETUPPASSWORD options) that you have assigned an SSL certificate to the SQL Server and have configured the SQL Server to force encryption. If you haven't, you should be aware that SQL Authentication, which is what will by default be used over an unencrypted connection, is fairly insecure. One final thought for the paranoid:
After the installation, you can always change the Password for the SQL Server account that you used during installation—just don't rely on this approach. It only takes someone listening to grab the credentials while on the wire and immediately create their own backdoor sysadmin account. Yes, folks, we've seen this kind of thing happen in some environments when the door was only open for a few seconds. (Sleep well tonight.)
Installing Reporting Services on Web Farms
It's possible to scale out and configure a number of IIS servers to all use the same Report Server database. Such a configuration is called a web farm. Web farm configurations are only available to the Developer/Enterprise/Evaluation versions of SQL Server, but there are licensing restrictions on use of the Developer or Evaluation versions in production. If during the installation you instruct the wizard to use a pre-existing Report Server database, the wizard asks if you want to setup a web farm and leads you through the installation steps. There may be occasions when you want to take a stand-alone Reporting Services installation and create or join it to an existing web farm. With some configuration file editing and some command-line utilities (rsconfig.exe, rsactivate.exe, and possibly rskeymgmt.exe), you'll be able to get it to work. We briefly discuss some these utilities to provide an overview of what they do in "Installing Reporting Services on a Web Farm," later in this chapter.
Licensing Your Reporting Services Installation
As with the installation of the Report Designer, if the wizard cannot find a qualifying [9] installed SQL Server 2000, it asks you for the 25-character product key of a qualifying version. To comply with the license agreement, you need a SQL Server 2000 server license for every machine on which the Reporting Services Server components are installed. If you decide to split the installation so that the Reporting Services SQL Server Catalog is on one machine and the IIS Report Manager and Report Server are on another machine, you'll need two SQL Server 2000 server licenses—but only one if you run them on the same box. As we said earlier, we're not licensing experts, and licensing arrangements change and are different from locale to locale, so please check your license arrangements carefully. For development purposes only, our solution is that we subscribe to MSDN Universal, which includes SQL Server Developer Edition, [10] which has sufficient rights and licenses for our development machines—but the MSDN Universal is not licensed for any production purposes.
Command-Line Options for the Installation Wizard
It's possible to run the installation wizard from a Command prompt window and provide some or all of the options to the wizard—it's even possible to do a completely silent install. The command-line options are typically supplied as parameters to Setup.exe or through an ini file. You'll find a template ini file in your Reporting Services distribution media. The template is well documented and explains all of the options. It's convenient (and a good idea) to copy this template and edit the values you need. Next, launch the Setup.exe wizard from a command prompt with setup.exe /settings myoptions.ini—assuming myoptions.ini is your file based on the template.ini.
Preparing Your System to Run the Setup Wizard
If you've already received an SSL web server certificate from a public Certificate Authority or from an Enterprise Certificate Services Certificate Authority within your Active Directory Domain and created and installed an SSL web server certificate for the web server hosting Reporting Services, you'll have little to do but answer a few simple questions in the following Reporting Services Setup dialogs. If not, then you might need to take a couple of side trips to make sure this SSL infrastructure is installed. We've provided a detailed explanation of the steps you'll need to take to enable SSL security for your website in Appendix A, "Using SSL to Protect Your Data," and Appendix B, "Using Secure Sockets Layer for Reporting Services." Better yet, watch the Guide me! narrated screen capture demonstration that shows how to do this.