In the previous installment of our series dedicated to the most prominent features available in the SQL Server 2005 Express Edition, we started exploring its reporting capabilities. In particular, we have described steps necessary to perform setup of Reporting Services, as either part of a new installation or an upgrade applied to an existing database engine instance. In this article, we will focus on basic configuration tasks, which are not only essential in the second of these two scenarios, but also helpful whenever current parameters of reporting components need to be adjusted.
The starting point of our discussion is a successful completion of the SQL
Server 2005 Express Edition Setup Wizard delivered via
executable available from the Microsoft
Download Center. At this point (assuming the upgrade path), basic Report
Server software is present on the local computer and associated with the
database engine instance you designated (
in our example), but not yet configured. To produce a working outcome, you need
to make additional modifications using the Windows Management
Instrumentation-based Reporting Services Configuration Tool (available via a
shortcut in the Configuration Tools subfolder of Microsoft SQL Server 2005
program group menu).
After launching this utility in the security context of an account that is a
member of the local Administrators group (this is required to interact with the
Reporting Services WMI Provider), you will be presented with the Instance
Selection dialog box, prompting you for machine name (by default, pointing to
the local computer) and instance name (
Once connection to the target Report Server is established, the resulting
interface gives you access to the following options (each accompanied by an
icon indicating its current status, which can take one of five values - configured,
not configured, optional configuration, recommended configuration, and not
supported in the current mode):
- Server Status - the node selected by default; displays the current SQL Server instance name, instance ID, as well as initialization and the corresponding SQL Server Reporting Services Windows service status (providing the ability to change it with Start and Stop command buttons).
Report Server Virtual Directory - allows you to designate a
dedicated virtual IIS directory intended for hosting Report Server files on the
local Web site. In the case of a new installation, such directory is
automatically created (with the name set to the
InstanceNamerepresents the name of the local database engine instance where reporting databases reside) and populated with the default content, which constitutes the Report Server .NET 2.0-based application (operating as a Web Service), configured with Integrated Windows and ASP.NET Impersonation authentication settings. In upgrade scenarios, you can specify an arbitrary name (which subsequently triggers the automatic creation of virtual directory and corresponding ASP.NET application files). however, you might want to consider using the same naming convention for consistency sake. You can also re-apply default settings to the current virtual directory (although keep in mind that such action will overwrite any previous customization). Another option available from the same interface allows you to designate a certificate that will be used to protect browser-based communication by employing Secure Socket Layer encryption (we will discuss this subject in more detail in an upcoming article of this series).
Report Manager Virtual Directory - provides configuration options
for Report Manager, equivalent to those for Report Server described above,
allowing you to designate target web site, virtual directory, and corresponding
ASP.NET application (and trigger their creation), as well as re-apply default
settings, if desired. In this case, it might also be beneficial to ensure that
manually assigned settings match those allocated automatically during new
installations (setup uses
Reports$InstanceNamefor this purpose), since your decision will determine how Report Server will be identified by Web clients (its target URL contains the path assigned to its virtual directory). Note that, just as in the previous case, Report Manager requires a dedicated virtual directory.
Windows Service Identity - identifies the Service Name
(configured automatically during setup and set to
ReportServer$InstanceNameby default) and its corresponding Windows account (
NT AUTHORITY\LocalService) that determines its security context. This account is automatically added to the
SQLServer2005ReportServerUser$InstanceNamelocal group (created during Reporting Services setup) that grants the appropriate level of privileges to the Reporting Server instance. It is important to ensure that any changes to the service account are done via this interface (since such an approach automatically triggers updates to a number of internal configuration settings dependent on the identity of service account).
Web Service Identity - determines the security context in which
the Report Server Web Service operates. Available options depend primarily on
the version of Internet Information Services. IIS 5 and 6.0 running in
isolation mode uses the local ASP.NET Service Account for this purpose (this
option is not configurable). In the case of IIS 6.0 or later, the resulting
configuration depends on the selection of the application pool assigned to the
Report Server and Report Manager Web services (effectively matching its
Identity setting). If you intend to change this assignment (and choose a
different application pool with another security identity), you should apply
your selection from within this interface (rather than relying on IIS Manager
console), since there are corresponding updates to the Report Server
configuration that will be automatically performed as long as the Reporting
Services Configuration Tool is used (for example, the new account gets added to
SQLServer2005ReportingServicesWebServiceUser$InstanceNamelocal group). In addition, make sure that your designated application pool is based on Classic Managed Pipeline Mode. Note that, by default, the
ReportServer$SQLExpressapplication pool leverages
NT AUTHORITY\NetworkServiceaccount (although due to restrictions inherent to SQL Server 2005 Express Edition Reporting Services,
NT AUTHORITY\LocalServiceis also a viable choice).
Database Setup - specifies the name of local Report Server
database and connection credentials required to create it (or simply connect to
hosting it SQL Server 2005 Express Edition instance). During a new
installation, the setup program automatically generates new Reporting Services
ReportServer$InstanceNameTempDB. Interestingly, the manual process following the upgrade yields databases named
ReportServerTempDBby default (after verifying the database engine edition and version, granting appropriate rights to both Windows and Web Service accounts that you designated earlier, and setting connection information for Reporting Server database). The same interface also allows you to determine the current database version, trigger its upgrade, or generate T-SQL scripts that can be used to perform the same group of tasks (creating a new or upgrading an existing Report Server database, as well as granting rights to Reporting Services accounts) without relying directly on the Reporting Services Configuration Tool.
- SharePoint integration - as an informational message displayed on this page states, this functionality is not available in Reporting Services running on SQL Server 2005 Express Edition platform (one of its full-fledged counterparts, such as Standard, Developer, or Enterprise is required instead).
- Encryption Key - facilitates maintenance of symmetric encryption key used by the Report Server to protect the content of its database (note that such key is generated automatically when the Report Server database is created). The set of operations available from this interface includes key backup, restore, and change. You also have an option to delete encrypted content, along with the corresponding symmetric key, as the last resort in situations where the Report Server no longer has access to it (e.g. following password reset of Report Server Windows account). To address such issues proactively, you should back up the encryption key to a file stored in a secure location. Key recoverability will allow you to properly handle such events as changes to the Report Server service credentials (password resets or account replacement) or migrating the local installation to a different hardware (potential exposure results from the fact that Report Server database symmetric key gets encrypted with the private key of the Report Server Windows service).
- Initialization - in the case of SQL Server 2005 Express Edition, this is strictly informational node indicating whether Report Server has been initialized (i.e. it is capable of storing and retrieving encrypted data). We will cover this subject in more detail in a future article of this series.
- Email Settings - intended for email based delivery of reports generated by Report Server (through a subscription mechanism). This functionality is not available in SQL Server 2005 Express Edition.
- Execution Account - an optional setting that might be needed when performing unattended operations (for example, in cases where external data is referenced during report generation but matching credentials are not readily available). Applicability of this feature is limited in SQL Server 2005 Express Edition, which is not capable of accessing remote data source connections (but could potentially need it to reference secured, external image files).
In the next article of our series, we will start exploring basic report generation functionality available in SQL Server 2005 Express Edition Reporting Services.