Initial Configuration of Reporting Services on SQL Server 2005 Express Edition

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 SQLEXPR_ADV.EXE self-extracting
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 (SQLExpress
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 (SQLEXPRESS).
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 ReportServer$InstanceName value, where InstanceName represents 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$InstanceName
    for 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$InstanceName by default) and its
    corresponding Windows account (NT
    AUTHORITYLocalService
    ) that determines its security context.
    This account is automatically added to the SQLServer2005ReportServerUser$InstanceName
    local 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
    the SQLServer2005ReportingServicesWebServiceUser$InstanceName
    local group). In addition, make sure that your designated application pool is
    based on Classic Managed Pipeline Mode. Note that, by default, the ReportServer$SQLExpress
    application pool leverages NT AUTHORITYNetworkService
    account (although due to restrictions inherent to SQL Server 2005 Express
    Edition Reporting Services, NT AUTHORITYLocalService
    is 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
    databases named ReportServer$InstanceName
    and ReportServer$InstanceNameTempDB.
    Interestingly, the manual process following the upgrade yields databases named ReportServer and ReportServerTempDB by 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.

»


See All Articles by Columnist
Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles