Securing Reporting Services

In the recent installments of our series covering the most important
features of SQL Server 2005 Express Edition, we have been discussing its
implementation of Reporting Services, focusing in particular on
security-related topics. So far, we have presented the role-based management
model (greatly simplifying the delegation of administration), parameterized
reports (facilitating the need for granular data access control), and SSL-based
encryption (preventing eavesdropping of network traffic targeting Web sites
hosting Report Manager and Report Server applications). In this article, we
will continue this subject by describing other technologies that assist with
data protection, their corresponding configuration settings, and a few
authentication and authorization caveats.

The primary role of Reporting Services is to provide a convenient method of
presenting data residing in SQL Server databases. However, reaching this goal
should not compromise its confidentiality, by accidentally exposing information
not intended for target viewers. One way to accomplish this is to restrict its
scope based on values assigned to report parameters (as we have demonstrated in
our
earlier article). Another approach involves specifying the security context
in which a database connection will be established. This option is available
from the Report Manager interface (accessible by directing Internet Explorer to
http://localhost/Reports$Instance_Name
URL, where Instance_Name
designates the name of the target SQL Server 2005 Express Edition instance). Once
the Reporting Services home page is displayed, locate the data source you want
to configure, switch to the Details view, and click on the Edit icon appearing next to its name.
You will be presented with the content of the Properties tab, including the Connect using: section, which gives you
the ability to choose one of the following options:

• Credentials supplied by the
user running the report – requested via a customizable prompt
  (set by default to Type or enter a user
name and password to access the data source. In addition, there
  is a checkbox labeled Use as Windows
credentials when connecting to the data soure, which allows you
  to designate the desired server authentication method as either Windows
  Authentication or SQL Server and Windows Authentication. Obviously if you
  intend to use the latter, you should adjust the server settings accordingly (SQL
  Server 2005 Express Edition uses the former by default). This is typically done
  via SQL Server Management Studio Express (Security
  node of Server Properties
  dialog box).
• Credentials stored securely
in the report server – that you type in directly within
  designated textboxes on the same page. In this case, there is also an option to
  Use as Windows credentials when connecting
to the data source (to dictate whether you want to rely on SQL or
  Windows authentication). Furthermore, it is possible to Impersonate the authenticated user after a connection
has been made to the data source, which activates the SETUSER function, applying stored credentials
  to database level authentication, effectively impersonating an arbitrary
  database user. Stored credentials offer significant advantages in the case of
  full-featured versions of SQL Server 2005 Reporting Services, where they are
  required for subscriptions or scheduled report-related activities, but even in
  the case of Express Edition, they might provide an extra value, ensuring a consistent
  end-user experience, regardless of the logged-on account.
• Windows integrated security
  – relies on credentials of the interactive user activating the report
  generation to authenticate and authorize access to the SQL Server 2005 Express
  Edition instance hosting a target database. This is a common choice, since it
  does not require management of local server logins and their corresponding
  database users, but still leverages server and database level security. In
  addition, as long as the Web site and the corresponding data source reside on
  the same computer, there are no delegation issues that have to be addressed in
  distributed environments (by implementing Kerberos-based delegation).
• Credentials are not required
  – designates an anonymous connection to a target data store. This option, while
  significant when configuring unattended execution accounts (applicable to
  full-featured editions of Reporting Services), is not relevant in the context
  of our discussion, since it is ignored when connecting to databases hosted by a
  SQL Server 2005 Express Edition instance.

If you decide to take advantage of stored credentials, you should be aware
that they reside in the Report Server database (typically named ReportServer$Instance_Name, where
Instance_Name
designates the name of the hosting SQL Server 2005 Express Edition instance),
protected using reversible encryption, leveraging a symmetric key (this key is
required when the local system’s NetBIOS name changes, as the result of
renaming the computer account or the database move). Its management interface
is accessible via the Encryption Keys
section of Reporting Services Configuration Manager, allowing you to perform
common key management tasks (namely backups, restores, changes, and deletions).
If you decide to incorporate them into your standard operating procedures, you
should consider taking advantage of the RSKeyMgmt
command line utility (residing by default in the Program FilesMicrosoft SQL Server90ToolsBinn folder)
to automate their execution. For example, to back up the key of a SQLEXPRESS instance to an arbitrary
location (D:Backup in our
example) and subsequently restore it, you would run the following:

RSKeyMgmt -e -i SQLEXPRESS -f D:BackupSQLExpressRS.snk -p 1@mT0ug#2Gu3$$

RSKeyMgmt -a -i SQLEXPRESS -f D:BackupSQLExpressRS.snk -p 1@mT0ug#2Gu3$$

The -i switch designates
the instance name and -p is
used to assign a password with which the key is encrypted and decrypted. You
need to use an account that is a member of local Administrators group in order
to carry out both of these actions. After the restore, you need to restart the
Internet Information Services service for the change to take effect. In case
you lose the key or it becomes compromised, you have an option of deleting
encrypted content (obviously, as a result, any stored credentials will be
removed from the Reporting Services database and would need to be recreated
using the method described above). This can be accomplished by executing (note
that the same functionality is exposed via the Delete command button in the Encryption Keys section of Report Server
Configuration Manager graphical interface):

RSKeyMgmt -d -i SQLEXPRESS

Remote access to Reporting Services is subject to firewall restrictions.
Specifics regarding necessary configuration steps depend on the version of the
operating system hosting the SQL Server 2005 Express Edition instance, but, in
general, they involve allowing inbound traffic targeting TCP ports 80 and 443
(the latter applies if Secure Sockets Layer encryption has been enabled). The
most straightforward way to accomplish this is to use the Windows Firewall Control
Panel applet, although in larger environments, it might make sense to leverage
domain-based Group Policy for this purpose (it is also possible to script the
configuration by taking advantage of the netsh
command line utility). It is not recommended to rely on the predefined rule
groups (such as World Wide Web Services
(HTTP) and World Wide Web
Services (HTTPS), associated with IIS) to avoid any potential
dependencies issues (where built-in rule modifications affect connectivity to Reporting
Services applications).

There are also additional provisions you need to take into account when
setting up SQL Server 2005 Express Edition-based Reporting Services on Vista
and Windows Server 2008 computers (in order to address changes introduced by
User Account Control and Internet Explorer Enhanced Security Configuration). In
particular, to facilitate management of a Reporting Services Web site, you
should add both Report Server (http://computer_name/Reports$SQLExpress)
and Report Manager (http://computer_name/ReportServer$SQLExpress)
URLs to either Trusted or Local intranet zones via the Security tab of the Internet Properties dialog box
(accessible via the Internet Options Control Panel applet). When performing
administrative tasks, make sure to launch Internet Explorer in the elevated
mode, using the security context of an account with System Administrator or
Content Manager role. In addition, keep in mind that local Administrators group
is no longer automatically assigned to the SysAdmin fixed server role. Instead,
the successful completion of installation of a SQL Server instance triggers the
User Provisioning Tool, which allows you to specify arbitrary accounts that
will be granted this privilege. When running Reporting Services on Windows Server
2008 platform (with IIS 7.0), you should also designate a domain-based account
that will provide the security context for an application pool utilized by
Report Manager and Report Server Web sites. This requires changes to their
configuration (via IIS Manager), modifications to the RSReportServer.config file, as well as
addition of that account to the SQLServer2005ReportingServicesWebServiceUser$ComputerName$InstanceName
local group (details of this setup are described in Microsoft Knowledge Base article
938245).

In the next article of our series, we will turn our attention to troubleshooting issues affecting SQL Server 2005 Express Edition-based Reporting Services.

»


See All Articles by Columnist Marcin Policht