Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted May 11, 2009

Initial Configuration of Reporting Services on SQL Server 2005 Express Edition

By Marcin Policht

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 AUTHORITY\LocalService) 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 AUTHORITY\NetworkService account (although due to restrictions inherent to SQL Server 2005 Express Edition Reporting Services, NT AUTHORITY\LocalService 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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM