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 Sep 28, 2009

Securing Reporting Services

By Marcin Policht

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 Files\Microsoft SQL Server\90\Tools\Binn 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:\Backup\SQLExpressRS.snk -p 1@mT0ug#2Gu3$$
RSKeyMgmt -a -i SQLEXPRESS -f D:\Backup\SQLExpressRS.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):


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

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