Chapter 13 Securing Report Server In This Chapter

Introducing the Authorization Model Reporting Services provides an authorization model, but it doesn t include an authentication component. In order for authorization to work, the underlying network security must be able to authenticate the users and groups who access the report server. Authentication is performed by the Windows operating system. Note: You can also use custom authentication if you create a security extension to support it. The security model consists of the following components: One user account or group that can be authenticated by Windows security or another authentication mechanism. Securable object such as an item object, like a report, or a system object, like a shared schedule. Role definitions that specify the set of permissible item or system tasks. Examples of role definitions include System Administrator, Content Manager, and Publisher. The combination of all these elements is characterized as a role assignment. In Reporting Services, role assignments provide the security context for items and the report server itself. The role assignment is a security policy that defines the tasks that users or groups can perform on specific items or branches of the Report Server folder hierarchy. Creating role assignments A role-based security model grants end-user access to specific operations through role membership. All users who are members of a role can perform the operations that are defined for the role. Role-based security is flexible and scalable, particularly when you use it with group accounts. You can map group accounts to role definitions, and then allow the changing membership of those groups to automatically adjust for new report users coming into the organization or moving to different positions in the organization, and other report users exiting the organization. Use role-based security to control access to folders, reports, and resources. Security settings follow an inheritance pattern through the folder structure. You can vary security at any branch to redefine user access at the item level. Role-based security works with Windows authentication. A default security model provides initial security. Security is in place when the product installs. 246 Part IV: Maintaining Your Reports

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

Chapter 13 Securing Report Server In This Chapter

Chapter 13 Securing Report Server In This Chapter Examining security features of Reporting Services Understanding authorization model and role assignments Configuring item-level and site-level security Granting rights and privileges Protecting your Report Server from attack Understanding data security When you re safeguarding corporate assets through the secure distribution of information to people on a need-to-know basis (which business intelligence can get into), it becomes a federal case when secure information gets into the wrong hands. Security considerations alone are enough to eliminate a reporting tool from the list of permitted tools in any organization. Secure information access and delivery are key to safeguarding the assets of your company. Without secure information management capabilities, we would still be in the Dark Ages of paper reports distributed on a need-toknow basis within the bowels of the company. Therefore, knowing how to secure a reporting platform to ensure that information is made available based on a permission system is key to the successful deployment of reporting and analysis capabilities. This chapter covers the security considerations you need to know. Understanding Security Fundamentals Reporting Services uses a role-based security model to control access to reports, folders, and other items that are managed by a Report Server. The role-based security model is similar to role-based security models offered by other applications. Reporting Services enables you to categorize users into groups or roles base on how they interact with the system and its resources. You can map specific user groups to specific roles that can perform specific tasks. I talk about the Security role model in the sections that follow.

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

Understanding database storage A Reporting Services installation uses

Understanding database storage A Reporting Services installation uses two databases to separate persistent data storage from temporary storage requirements. The databases are created together and bound by name. By default, the database names are ReportServer and ReportsServerTempDB, respectively. The Report Server database stores reports, folders, shared data sources and metadata, resources, snapshots, and report history. The ReportServerTempDB database stores session cache and cached instances. It is the snapshots, report history, session cache, and cached instances that require the most consideration from a storage perspective. The ChunkData table in the Report Server database contains the snapshots and report history. This same table in the ReportServerTempDB contains the session cache and the cached instances. To determine the disk space requirements for your databases, you need to estimate the number of reports and look at the size of the intermediate reports. For the Report Server database, you need to factor in the persistence of the intermediate report, whether it s a snapshot (only one allowed per report at any point in time) or report history (how long it is maintained). For the ReportServer TempDB, you need to factor in the session cache size. Cached instances exist for each combination of report parameters and persist until they expire; the number of users affects the size of the session cache. Chapter 12: Managing and Administering Your Reports 243

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Clan Web Hosting services

1. Verify that Data Transformation Services 2000 Runtime

4. Click OK. 5. Run the query by clicking Execute on the toolbar. This creates the tables you need in your RSExecutionLog table. 6. If the Report Server database that you re using as the source of your execution log data isn t named Report Server, specify the correct report server database name in the Source section of this file. If the report execution log database that you are using as the destination is not named RSExecutionLog, specify the correct report execution log database name in the Database section of this file. Save changes and close the file. 7. Extract the Report Execution Log data. Run a command prompt on the computer running SQL Server, and change directories to the directory containing the Dtsrun.exe utility by issuing this command: cd Program FilesMicrosoft SQL Server90ToolsBinn 8. Run the package by typing the following at the command prompt: Dtsrun /f C:Program FilesMicrosoft SQL Server90ToolsReporting ServicesExecution LogRSExecutionLog_Update.dts When this has completed running, you will see the message Package execution complete. 9. Refresh the Execution Log data. To update RSExecutionLog with information from the report execution log, run the RSExecutionLog_Update.dts package periodically. The DTS package appends new log entries to the existing entries. It does not remove old entries or historical data. If you don t want to save historical execution log data, you can periodically run the Cleanup.sql query on RSExecutionLog. To do this, select RSExecutionLog from the Available Databases list box on the toolbar in SQL Server Management Studio. Click File, select Open, and then click File. By default, this folder is: C:Program FilesMicrosoft SQL Server\90ToolsReporting ServicesExecution Log Browse to the folder containing Cleanup.sql, select that file, and then click OK. Click Execute on the toolbar to run the query. 242 Part IV: Maintaining Your Reports

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

1. Verify that Data Transformation Services 2000 Runtime

1. Verify that Data Transformation Services 2000 Runtime is installed on the computer running SQL Server 2005. Look for the following file: C:Program FilesMicrosoft SQL Server90ToolsBinndtsrun.exe If the file is missing, choose Add or Remove Programs in the Control Panel to install Microsoft SQL Server 2005 Tools. This installs the Data Transformation Services 2000 Runtime. 2. Create a database for the Report Execution data. In SQL Server Management Studio, in Object Explorer, create a new database that the DTS package can use as its destination database. For the database name, use the name RSExecutionLog. 3. Add tables to the database. To add tables to the database, select RSExecutionLog from the Available Databases list box on the toolbar in SQL Server Management Studio (see Figure 12-11). Choose File.Open and then click File. Browse to the following file: C:Program FilesMicrosoft SQL Server90Tools\90ToolsReporting ServicesExecution LogCreatetables.sql Figure 12-11: The new database RS Execution Log and the script createtables .sql ready to execute to create the execution log tables in a database. Chapter 12: Managing and Administering Your Reports 241

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost JSP Web Hosting services

Trace logs are text files, so you can

Parameter values used for a report execution Start and stop times that indicate the duration of a report process Percentage of time spent retrieving the data, processing the report, and rendering the report Source of the report execution (1=Live, 2=Cache, 3=Snapshot, 4=History) Status (either Success or an error code; if multiple errors occur, only the first error is recorded) Size of rendered reports in bytes Number of rows returned from queries You can, for example, use the report execution log to find out how often a report is requested, what formats are used the most, and what percentage of processing time is spent on each processing phase. Logging is enabled by default in Report Manager, with removal frequency of log entries whose default is 60 days. Reporting Services provides an ETL package that you can use to export logged records to another database for viewing and analysis. The report server logs data about report execution into an internal database table. This table does not provide complete information by itself, nor does it present data in a format that is understandable to users. To view report execution data, you must run a DTS package that Reporting Services provides to extract the data from the execution log and put it into a table structure that you can query. Querying the Report Execution Log You can turn report execution logging on or off by selecting options in Report Manager on the Site Settings page. On this page, you can also specify how long you want to keep log entries. By default, this value is 60 days. Entries that exceed this date are removed at 2 a.m. every day. You can extract the execution log data and store it in a separate local report execution log database. SQL Server ships with a Data Transformation Services (DTS) package called RSExecution_LogUpdate.dts to enable this process. This DTS package extracts the data from the report execution log and puts it into a table structure that you can query. To set up your computer for querying report execution log data, follow these steps: 240 Part IV: Maintaining Your Reports

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

Trace logs are text files, so you can

Trace logs are text files, so you can use any text editor to view a log file. The trace log file contains the following types of event information: Events logged by the application log Exceptions generated by the report server Low resource warnings logged by the report server The trace files are written to log files in the directory: C:Program FilesMicrosoft SQL ServerReporting ServicesLogFiles The trace files use the local time of the Report Server to generate a single log file per day with the following names: ReportServerService_.log Trace server log for the Report Server windows and Web services ReportServerWebApp_.log Trace server log for Report Manager ReportServer_.log Trace server log for the Report Server engine You can control the level of information recorded to the trace logs by modifying the DefaultTraceSwitch configuration setting in the ReportingServices Service.config file. The settings you can select from are 0= Disables tracing, 1= Exceptions and restarts only, 2= Exceptions, restarts, warnings, 3= Exceptions, restarts, warnings, status messages (default), and 4= Verbose mode. Using the execution log You can log information about job executions to the Report Server database. This can help you to monitor execution performance, troubleshoot report executions, and optimize report executions. For each execution, there is a variety of captured information. The following types of information can be captured in the execution log: Name of the report server instance that handled the request Report identifier User identifier Request type (either user or system) Rendering format Chapter 12: Managing and Administering Your Reports 239

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

To enable My Reports using Report Manager, use

A query timeout value is the number of seconds that the Report Server waits for a response from the database. This value is defined in a report. A report execution timeout value is the maximum number of seconds that report processing can continue before it is stopped. This value is defined at the system level. You can vary this setting for individual reports. Most timeout errors occur during query processing. If you are encountering timeout errors, try increasing the query timeout value. If necessary, adjust the report execution timeout value so that it is larger than the query timeout. The time period should be sufficient to complete both query and report processing. Query timeout values are specified during report authoring when you define a dataset. You can also specify a query timeout value for data-driven subscriptions. The timeout value is stored with the report in the Timeout element of the report definition. This feature helps you prevent unpredictable source queries from running an undesirable amount of time. When the timeout is exceeded, a failure is returned. Users who have permission to modify the properties of a published report can reset this value by editing the report definition file. You can set the report execution timeout value to limit the amount of time that a report server uses to process a report. Report execution timeout values can be specified in Report Manager in the Site Settings page (refer to Figure 12-7). You can set a default value for all reports in the Site Settings page and then override that value in the Execution Properties page for a specific report, so the site timeout value can be overridden on a report-by-report basis. This feature helps you prevent any long-running report executions that go beyond your acceptable threshold. By default, the value is set to 1,800 seconds. The Report Server evaluates running jobs at 60-second intervals. At each 60-second interval, the Report Server compares actual process time against the report execution timeout value. If the processing time for a report exceeds the report execution timeout value, report processing stops. If you specify a timeout value that is less than 60 seconds, the report may execute in full if processing starts and completes before the report server evaluates the report execution timeout. Using trace files Reporting Services trace logs contain very detailed information that s useful if you re debugging an application or investigating an issue or event. Trace logs contain information about the various Report Server operations like system information, events in the application log, and exceptions and warnings generated by the Report Server. 238 Part IV: Maintaining Your Reports

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services

To enable My Reports using Report Manager, use

To enable My Reports using Report Manager, use the Site Settings page to set the Enable Each User to Have a My Reports Folder option. The role definition used for My Reports determines what actions are supported in the My Reports workspace. For example, if the My Reports role excludes Create Linked Reports, users cannot create linked reports in the My Reports folders. When My Reports is activated, you see a My Reports folder located under the root folder, Home. In addition to a My Reports folder, report server administrators also see a Users Folders folder that contains the subfolder for each user. While the feature is activated, Users Folders and its subfolders cannot be deleted. Furthermore, the name My Reports becomes a reserved name for folders created under the root node (Home). If you activate My Reports after it has been deactivated, the report server creates a new Users Folders folder if one does not already exist. If a Users Folders folder exists, the report server adds new subfolders as users log on to their My Reports folders. To deactivate My Reports, clear Enable Each User to Have a My Reports Folder. Deactivating My Reports removes all visible indications of the My Reports folder. The folders that provide actual storage (that is, the subfolders in Users Folders) must be deleted manually once the feature is disabled. Only users who have permission to delete folders can do so. When My Reports is deactivated, the name My Reports is no longer reserved; users can create a personal folder named My Reports under the Home folder. In addition, redirection from My Reports to user-specific My Reports subfolders is no longer performed. Lastly, any report links that include a userspecific My Reports folder in the URL address will no longer work. Administering Reporting Services Administration of a Reporting Services implementation is concerned with controlling the server settings and properties, working with trace files and execution logs to understand the operation of the Report Server, and understanding the database storage requirements of the Report Server. This section provides some interesting aspects of this topic. Applying timeouts The Report Server provides an option that I affectionately refer to as the governator. This is an option to time out the execution of a report and cancel it with a message to the end user. This governor specifies a timeout value for the limit of how system resources are used. Report Server supports two timeout values: Chapter 12: Managing and Administering Your Reports 237

Note: If you are looking for good and high quality web space to host and run your application check Lunarwebhost Adult Web Hosting services