Secure support for external users If you want

data. Therefore the dataset based on your permissions table would filter the parameter options presented to the current user. This filtered set of offices can be derived from the permissions table with the following query: Select office From permissions where userid = User!UserID This filters down all the offices, which only the accessing user can see. You can use this strategy either in the source query or with a filter that restricts the report based on the current Windows user. 256 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 Inexpensive Web Hosting services

Secure support for external users If you want

Secure support for external users If you want to support external users but don t want to code a custom security extension, you can use Windows authentication or Microsoft Active Directory. The following guidelines describe how to support this scenario: 1. Create a low-privileged domain user account with read-only permissions. The account must have access to the computer hosting the report server. Provide a custom Web form so that users can log on using the low-privileged domain account. 2. Create role assignments that map the user account to specific items in the report server folder hierarchy. You can limit access to read-only operations by choosing as the role assignment the predefined Browser role. 3. Configure reports to use stored credentials to get data for the report. This approach is useful if you want to query the external data source using an account that is different from the account that allows access to the report server. Understanding data security You can restrict which users see specific data within a report, which is a finer grain of security than the role assignments. Role assignments will determine if the report can be run at all by a particular user assigned to a specific role with specific permissions. Consider the expression =User!UserID. This expression returns the user ID of the person running the report. You can utilize this parameter value with a custom permissions table of your design that associates which users can see specific groups of data within the system. For example, if you have an office sales results report that filters on the salesman ID requesting the report, you can define in a permissions table in your database which user IDs have access to which office sales information. In this Permissions table, you need to associate the Windows username with the permission level relevant to your application database. For example, assume that you want to control which users are able to see sales data for specific offices within your application. When you define the dataset used to prompt the user for which office to select, you can add a permissions table with at least two columns: user and office. The table is populated with the combination of users that have access to specific office sales Chapter 13: Securing Report Server 255

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

Reporting Services doesn t create its own user account

The report data source is configured to use integrated security and the user running the report is logged on as an administrator. The report data source is configured to prompt for credentials and the user types his or her administrator credentials to run the report. To mitigate the threat of an elevation of privileges attack, follow one or more of these recommended security practices: Use least-privilege accounts to access the external data sources that provide data to a report. You can configure report data sources to always use the stored credentials of a least-privilege account. Use shared data sources in your reports to specify dataset connection information. You can use role assignments on the shared data source to control access to the connection string and settings that define how credentials are obtained at run time. Use role assignments to ensure that only trustworthy reports are published to a report server. Through role assignments, you can restrict report publication to specific folders, and then require administrators to inspect the RDL file (and the query) of a newly published report before moving it to a final location. There is no functionality in Reporting Services to enforce an inspection requirement prior to publication. This must become a standard operating procedure. Disable integrated security as a report data source credential option. The use of integrated security to access external data sources poses a special concern for report users who may not know that their security token is being passed to an external data source (users are not warned in advance of running a report that the report is configured to use integrated security). In addition, users may not have the same concerns about opening a report as they would if they were opening an e-mail attachment from an unknown source. However, the security risks are the same in both scenarios. A malicious query can damage or compromise a server in the same way a malicious script that is exposed through a hyperlink or hidden in an e-mail attachment can damage or compromise a workstation. Note that if you disable integrated security, any report data source that is currently configured to use integrated security (or configured to use integrated security after the feature is disabled) will no longer run. The error returned when running such a report is: This data source is configured to use Microsoft Windows NT integrated security but Windows NT integrated security is disabled for this server. To disable integrated security, you must use script or code to modify the EnableIntegratedSecurity system property. 254 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

Reporting Services doesn t create its own user account

Reporting Services doesn t create its own user account for system security and instead references existing local or domain accounts and groups defined in the server operating system. Also, members of the local administrators group can always access a Report Server to change site settings no matter what role assignments are set. To ensure that a local administrator does not have rights to highly secure reports, you must secure the reports at the dataaccess level, requiring users to provide credentials to view the report. Best Practices for Protecting Against an Attack Running a report under an account that has very secure permissions exposes your SQL Server to a security threat if the report query contains malicious Transact-SQL statements (for example, statements that create unauthorized logons, modify or delete data, or introduce erroneous data), and the report is run by a user who has very secure permissions on the server that hosts the data source. For example, if an attacker publishes a report that contains a malicious query, the query will be processed under administrator credentials if either of these conditions is present: Figure 13-7: System Administrator role task-level permissions as seen on the Edit System Role page in Report Manager. Chapter 13: Securing Report Server 253

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

The corresponding view of this same information as

Using system-level security The Report Server site can be secured through system role assignments. You set security at the system level by creating role assignments that give selected users the capability to perform tasks that affect the Report Server site as a whole. These tasks include creating shared schedules, managing jobs, and setting properties. System-level security does not determine access to items in the report server folder hierarchy. Reporting Services provides two predefined system role definitions. The System User role can view the schedule information in a shared schedule, or view the other basic information about the Report Server. The System Administrator role can enable features and set defaults, set systemwide security, create role definitions, and manage jobs. The task-level permissions for the System Administrator role can be viewed in Report Manager by navigating to the Site Settings page and clicking the link to configure system-level role definitions. The privileges for the System Administrator are shown in Figure 13-7. These tasks relate to system-level operations that can be performed for the site and do not apply to the items within the folder hierarchy. You can manage this through SQL Server Management Studio by rightclicking the system role in the Security System Roles folder and selecting Properties from the list that appears. Figure 13-6: Folder Properties page showing the inheriting roles from the parent folder as the default as shown within SQL Server Management Studio. 252 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

The corresponding view of this same information as

The corresponding view of this same information as shown in Report Manager is shown in Figure 13-5. You would navigate to this page by navigating to the folder in question and editing this in the Report Manager. You can access items based on your task-level permissions in your role assignment. A role assignment consists of one user or group name and one or more role definitions that specify a collection of tasks. Security settings are inherited from the root folder down to subfolders and items within those folders. Unless you explicitly override inherited security, subfolders and items inherit the security context of the parent item. If you redefine a security policy for a folder in the middle of the hierarchy, all its subfolders and items contained within the folder assume the new security settings. You can view the folder properties Permission tab in SQL Server Management Studio by right-clicking on a folder under the Home folder and selecting Properties from the list that appears. This will show the Folder Properties page as shown in Figure 13-6. Each object within the Report Server has a Permissions tab on the Properties page showing the roles available for each group or user account. You cannot delete a role assignment if it is the only one remaining, or if it is a built-in role assignment (for example, Built-inAdministrators) that defines the security baseline for the report server. Deleting a role assignment does not delete a group or user account or role definitions. You can add new role assignments for the current item (folder, report, and so on). Existing role assignments for the current item are defined for the groups and users that appear in this column. You can click a group or user name to view or edit role assignment details. If multiple roles are assigned to a group or user account, that group or user can perform all tasks that belong to those roles. To view the tasks that are associated with a role, click the group or user name to view the role assignment, and then click the role definition. Figure 13-5: Item-level security roles assigned to user groups for a folder as shown within Report Manager. Chapter 13: Securing Report Server 251

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

You can view the item-level roles in Report

Figure 13-4: Item-level security roles assigned to user groups for a folder as shown within SQL Server Management Studio. Figure 13-3: Task-level permissions for the Publisher role as seen in the Report Manager Edit role page. 250 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 Clan Web Hosting services

You can view the item-level roles in Report

You can view the item-level roles in Report Manager by navigating to the Site Settings page and then clicking the link Configure item-level role definitions. This will bring up the predefined item-level roles, as shown in Figure 13-2. The Browser role permits only navigating the folder hierarchy, viewing reports and resources, and managing their own subscriptions. The Publisher role permits report definitions to be uploaded and deployed, creating linked reports and some other report management tasks. A user assigned to the Publisher role cannot execute a report unless he or she is also assigned to the Browser role. The Content Manager role sits at the top of the trust scale. This role provides full administrative ability for managed report components such as folders, reports, resources, and shared data sources. The My Reports role is almost as powerful as the Content Manager role, but it is used only on each user s own special My Reports folder. If you select the Publisher role, you will see a detailed task-level permissions list with the privileges available reflected in the checked boxes next to the tasks (see Figure 13-3). These tasks are relevant only to the item-level security for folders and their contents. You can view the equivalent information using the SQL Server Management Studio by right-clicking on the security roles and selecting Properties from the pop-up menu. To modify the folder-level security permissions, edit the folder and navigate to the security properties page to view or modify the security settings for that folder. This page is available for items that you create or have permission to modify. You can also access this through SQL Server Management Studio by right-clicking on the folder and selecting Properties from the list that appears. You then will see the item-level security role assigned to specific user groups, as shown in Figure 13-4. Figure 13-2: Predefined item-level security roles in the Report Manager. Chapter 13: Securing Report Server 249

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

Reporting Services includes several predefined roles to accommodate

b. If the item already has item-specific security defined for it, click New Role Assignment. 3. Type the name of a group or user account. You can specify only one account name for each role assignment. 4. Select one or more role definitions that describe how the user or group should access the item, and then click OK. 5. To determine which tasks a role definition supports, click the name of the role definition. 6. If existing role definitions are insufficient, click New Role to create a new one. Role definitions can contain either item-level or system-level tasks. You cannot combine tasks from both levels into a single role definition. Because the number of tasks that you can work with is relatively small, you typically don t need a large number of role definitions. Creating or modifying a role definition requires careful consideration. If you create too many roles, the roles become difficult to maintain and manage. If you modify an existing role, you may not know the various places in which it is used or how users will be affected by the modification. Role-based security is central to the security model of Reporting Services, and understanding its implications is important. To create a role assignment in SQL Server Management Studio, follow these steps: 1. In the Object Explorer, expand a report server node, then navigate to the item for which you want to set item-level security. 2. Right-click an item, and then click Properties. The General page of the item s Properties dialog box appears. 3. Click Permissions in the Select a Page area. 4. Select Use These Roles for each group or user account. 5. Click the Add Group or User button. The Select Users or Groups dialog box appears. 6. Type the account name of the group or user that you are creating a role assignment for, and then click OK. 7. Select one or more roles that best describe the actions that you want the user or group to be able to perform on the current item. Then click OK. Maintaining item-level security Folders in Report Manager provide the foundation for item-level security. Role assignments that you define for specific folders extend to the items in that folder and to additional folders that branch from that folder. 248 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

Reporting Services includes several predefined roles to accommodate

Reporting Services includes several predefined roles to accommodate various categories of users. You can see the roles defined in SQL Server Management Studio when you expand the Roles folder within the Security folder in the Report Server (see Figure 13-1). If you right-click one of these roles and choose Properties from the list that appears, you will see the detail task permissions checked for that role. Figure 13-1 shows the four predefined roles in the Object Explorer on the left side and the specific task permissions available for the Browser role on the right side. You can create additional roles if the predefined roles are insufficient. You can modify or delete either the predefined roles or the custom roles you create, as long as you don t invalidate the last remaining role assignment for your report server. You can define a new role or edit an existing role within Report Manager of the SQL Server Management Studio. To create a role assignment in Report Manager, proceed as follows: 1. Navigate to the Contents page, and open the folder that contains the item for which you want to apply a role assignment. 2. Click the Properties tab, then click the Security tab, and perform one of the following: a. If the item uses the security settings of a parent item, click Edit Item Security, click OK, and then click New Role Assignment. Figure 13-1: The User role properties in the SQL Server Management Studio showing the Security roles in the Object Explorer and the task permissions for the Browser role. Chapter 13: Securing Report Server 247

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