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