Problem
What are the various security ramifications when deploying and managing Microsoft SQL Server Reporting Services (SSRS)? What are some of the best practices when setting up security within SSRS?
Solution
SSRS continues to be a reporting platform of choice for many organizations, enterprises, mid market and small businesses. Even with all the hype around artificial intelligence and Power BI, SSRS still garners significant acclaim as the "go-to" business intelligence tool for traditional data sets and dashboards.
SSRS Security
Security should be at the forefront of any data reporting app, no matter the type of data. Fortunately, SSRS provides multiple avenues for security gate installation to control access to data:
- Configuration Level: Several methods of identifying the proper security for connecting to SSRS and from SSRS to the data source.
- Web Service Level: Various roles can be set for administering and utilizing SSRS on the report.
- Folder Level: Permissions can be set, including at the home folder.
- Report Level: Certain tools can be used with filters and parameters to exclude access.
This tutorial will cover each level, highlighting the best possible options for securing your data.
SSRS Configuration
After installing SSRS, the Report Server Configuration Manager is used to set up your Report Server. While many of the items in the Configuration Manager center on the Service Account (to run the SSRS services or to specify the webserver URL), the Execution Account is one of the first lines of security fencing. Setting up the Execution Account provides a way to configure the account when it is used by non-credentialed sources or the report connects to an image on a remote file share. As shown below, this account is specified with an account ID and password. Remember: This account should not be used to run any other important tasks, connections, or services.
In addition to the encryption key, a second security resource, Subscription Settings, allows you to set the value used during subscription executions (see this tip for subscription details: SSRS 2016 Subscription Enhancements). This security setting adds the account for the report server to connect to file shares when exporting out subscription run reports. Similar to the prior account, this account should have minimal permissions, and those permissions are only related to having access to a specific file share.
One of the last settings that is security related is setting up and maintaining the Encryption Keys. These keys encrypt the connection string details and any other sensitive information.
SSRS Admin and Folder Settings
The next set of security settings is maintained within the SSRS web server, including who can administer the web server and grant other permissions for the report server roles.
Users in either of these roles are the main "super" administrators. The features of these roles include the ability to:
- Create and maintain various users and groups.
- Create or modify other roles.
- Define subscription schedules and shared schedules.
- Change various SSRS properties and settings, such as timeouts.
- Set branding for your report server.
Note: These roles set the precedent for later security settings and should be planned and thought through carefully.
Drilling into the roles beyond the two system roles, additional roles exist that allow you to set various levels of access to the system roles. As shown below, this ranges from a Browser, which allows a user to view reports and folders, to a Content Manager, which allows a user to view and maintain reports and folders, to a Publisher, which is in between the two and allows a report designer to publish a designed report.
The two additional roles, My Reports and Report Builder, are related to enabling special features, like Report Builder or the My Reports features, to users.
The above settings are the "Home" page folder settings. These settings automatically permeate down to any subfolders that may be created under the home folder. Likewise, this chain of permissions can be broken and customized to any permissions needed for that particular folder. Furthermore, any subfolders under the customized folder inherit the permissions from its customized parent folder (or the sub-folder) permissions. You can implement the specific security down to the report level using the customized security option outlined below.
The customized folder and report security can be reverted by using the "use same security as parent folder" button, which will re-establish the folder to use the same security as its parent folder. Up to this point, the focus has been on using the SSRS web server to establish and maintain security.
However, in addition to the web server, many security settings can be established and changed by connecting to the SSRS server via SQL Server Management Studio (SSMS).
Once connected in SSMS, the security folder can be expanded to show the regular and system level roles below in the yellow box.
The primary changes that can be made in SSMS are adjustments to assigned tasks enabled by that role. As shown below, the System Administrator role is granted certain tasks by default, but additional tasks can be added to that role’s permission task list.
Furthermore, a completely new role can be created to customize the tasks available to that role. Similarly, a role can be deleted (be careful deleting roles that are still used).
One last item that often gets overlooked in the SSRS area of SSMS is related to the SSMS Server Properties. Specifically, under the Security tab, there is an option to "Enable Windows integrated security for report data sources." This option allows for "pass through" security for connections to data sources. Generally, if you are using any sort of Windows Security, the option should be checked.
Our final line of security stems from how a report’s data sources are used to connect to the database or other sources. When creating a data source, certainly the connection string must be specified and set up, as shown below.
However, the important part of setting up the data source is specifying the credentials that will be used. As shown in the example below, four main options exist for completing this connection. It should be noted that these details are used when SSRS attempts to establish a connection with the host of the data. Integrated Windows security is often the best option, but the report designer must remember that whatever credentials are used here will be required to be set up on that SQL Server database, MySQL, Oracle or file source (i.e. Excel, CSV, etc.). Scalability could be an issue if careful thought is not made about which individuals will have access to the specific data and how to best manage users being added and removed from connecting to a data source. The establishment of groups that are automatically updated with additions and deletions is often one of the best ways to manage the scalability of these connections.
One of the final security processes that can be utilized is to establish row level security for accessing data within a database. Generally, this situation captures the user/user ID running the report and then passes this ID to the query/queries running against the data source. These queries will have a filter that will only return data for the assigned user. This process has been covered extensively in a previous tip, SSRS Report Security Management, so reviewing that tip and the related example is highly recommended to review how the row level security is established.
Within this tutorial, we covered the various security features that are in place for SSRS. Security starts at the configuration level, moves to the role level, then to the folder and report level, and finally to the report row level.
Next Steps
