SSRS – Report Builder: Recommendations and workarounds

SSRS – Report Builder: The first version of Report Builder was released by Microsoft in 2005. The product was advertised as a development and management tool with which end users and database administrators can generate specialized reports. Unfortunately, the company’s specialists created a complex set of templates and intricate development paradigm, which made it more difficult to work with this product than to create reports using business intelligence (BI) tools of Visual Studio product suite or ReportViewer suite. Report Builder 2.0 has little resemblance to the original version, and it is easy to manage both end users and managers with this product. However, Report Builder is not a universal tool for all networks.

SSRS tutorial – Architecture

Report Builder 2.0 is based on the SQL Server 2000 Reporting Services (SSRS) code. This code is essentially a locally installed report processor written in Report Definition Language (RDL) with a specialized report visualization extension that uses Windows API calls to draw the generated report on the user’s screen (i.e., the report is not passed to the browser as an HTML document).

The Report Builder (see figure), like the SSRS report handler hosted on the server, performs the following operations after an end user connects to the report directory of a given SSRS instance:

• opens the selected report in the SSRS directory; this RDL file can also be downloaded from the network file system as a second generation RDL file (first and second generation RDL file formats are discussed later);
• interprets and evaluates the RDL report definition file;
• opens the catalogued data source specified in the RDL file and then opens a database connection to authenticate the user if the credentials are mentioned in the data source;
• responds to unfilled input parameters by displaying dialog boxes;
• performs built-in queries and passes a set of lines to the report handler; combines data with the report layout from the report definition to the RDL;
• displays the report for the user;
• when exceptions occur, displays warning dialog boxes for the user.

This is the same sequence of events that the SSRS handler performs on the server when visualizing simple reports. However, unlike the SSRS service which returns HTML code to a browser, the Report Processor module of the Report Builder performs these steps on the client system and creates a report in the Report Builder window. The most important difference between Report Builder and SSRS is that the connection to the database is made through the remote client report processor, not through the server-side SSRS report processor.

Note that the connection string defined in the data source must contain the same credentials to access the data. That is, if the data source authenticates using the Security Support Provider Interface (SSPI), the same credentials are transmitted to SQL Server as if the user accessed the report from a Windows browser window. Report Builder does not allow developers to refer to SSRS catalogued reports that include user credentials – only reports from publicly available data sources are supported. You can create locally stored reports that use user-provided fixed authentication methods or SSPI-based methods, but these reports cannot be stored in the same SSRS directory – only the file system can store them.

Given the basic architecture of Report Builder 2.0, users of this product typically create, save, and retrieve reports that are already saved in a given SSRS instance. While it is possible to create reports that refer to data stored in different sources, this procedure is not intuitive. As I analyze the process of using Report Builder, I will explain how to work with data sources other than those that are represented in the SSRS.

First steps in SSRS – Report Builder

Report Builder 2.0 is available from the Microsoft download site. The product is installed on an SSRS server, which allows users to easily deploy the program from Report Manager with a single click of the mouse. However, be sure to make sure that the database administrator has not disabled the report generator. In this section, I will describe in detail how to activate (or deactivate) the Report Builder features. Begin by configuring the SSRS instance to provide Report Builder 2.0 to users who have access to the appropriate security groups through Report Manager.

Start SQL Server Management Studio (SSMS) and establish a connection to the SSRS instance, as shown in screen 1 (not the database server). After establishing the connection, right-click on it and select Facets from the drop-down menu; the Surface Area Configuration for Reporting Services component will be displayed.

Make sure that the WebServiceAndHTTPAccessEnabled property is set to True as shown in screen 2; this will allow you to use the Report Builder as well as the Report Manager or other SSRS based applications that communicate with the SOAP (Simple Object Access Protocol). Click the OK button.

Now go to the Server Properties dialog box and click the Security option. Make sure that the Enable ad hoc report executions flag is selected. You may want to clear the Enable Windows integrated security for report data sources check box to block attacks using Trojan horsepower.

Start your browser and go to Report Manager. Open the SSRS Configuration Manager window and, together with the database administrator, verify that the URL for Report Manager is correct. Usually, the address looks something like this: https:///Reports_/Pages/Folder.aspx.

Click on Site Settings and in the Custom Report Builder launch URL text box enter /ReportBuilder/ReportBuilder_2_0_0.application. This pointer will allow users to launch Report Builder 2.0 with a single click, as long as the systems they are running on have sufficient permissions.

Assign individual users (or better, Windows domain groups) certain roles in Report Builder as shown in screen 3. This should be done so that only authorized users can access the Report Builder and associated SSRS roles. Although this process is quite simple, it is beyond the scope of this article to describe it. More information on this can be found in Chapter 4 of Hitchhiker’s Guide to SQL Server 2000 Reporting Services (by Peter Blackburn and William R. Vaughn, Addison Wesley Publishing, 2004).

In the Report Manager window, navigate to the Home directory of SSRS reports. Note that the Report Builder icon is displayed next to the My Reports folder. Select the My Reports check box; this will instruct SSRS to create a dedicated folder for each user so that individual employees have personal directories to store reports on. You can set security settings so that users can only store reports in their own folders. This is useful because in this way you will protect existing production reports from both inexperienced users and intruders. In addition, I recommend creating a Run As screen shortcut that opens a browser with the target user’s credentials; this gives you an idea of what the end user will see when they launch Report Manager. This is much more convenient than going to a user and checking everything from their computer.

Report Management

Once you activate Report Builder and configure the appropriate site settings in Report Manager, end users will be able to create reports or open existing reports from the SSRS directory. To access the report, launch a browser and open the Report Manager. Then click the Report Builder icon to initiate a one-click deployment process that either performs the initial installation of the Report Builder or verifies the client portion installation. Note that with some versions of Windows and depending on the security settings applied, the user may not have sufficient rights to install applications such as the Report Builder on the client system. In such cases, the client opening the browser must have administrator status.

Once the installation of Report Builder 2.0 is complete, it can be run on the user’s system via the Start menu. Once started, a window with two icons will appear on the screen. Clicking the Table or Matrix icon launches a wizard that provides step-by-step instructions as you create a new report based on a single Tablix control. This is a new mechanism for the second generation report description language. The Chart icon launches a similar wizard that generates a report based on the Chart control.

To access a directoryed SSRS report, you must first configure another configuration property that is addressed to a specific SSRS instance containing a report directory. Click the Files icon located at the top left corner of the Report Builder window and select Options. The Report Builder Options dialog box will appear, and you must specify a Report Builder URL that points to the instance that you want. As I mentioned earlier, you can use the SSRS Configuration Manager utility to find this URL. Another option is to let the URL point to any SSRS instance that shows the report directory using the SOAP protocol.

Editing the cataloged report. After Report Builder establishes a connection to the SSRS target directory via SOAP, the user will be able to extract reports or general data from the directory. Let us analyze in detail the process of extracting a report from the directory and then editing, testing, and saving the report to the same directory. To perform this process, the user must have sufficient permissions to extract and edit reports.

In this case, again, in the Report Builder window, click the Files icon and select Open. The Open Report dialog box will appear, displaying the main folder in the report directory. Note that users will only be able to see the directory items that they have access to. In the directory, select the report to view and edit. Once the RDL of the report is extracted from the SSRS directory, Report Builder displays the report in the design view.

In my example, shown in screen 4, Report Builder completed the Parameters report collection as well as the data sets used by the report. In the report in question, RDL defines three datasets – two of which are used to populate the UI parameter selection lists; these lists allow the user to specify parameter values that subsequently guide the query addressed to the main report row set to the desired subset of the product table.

You can now make any changes to the report. When you are ready to view the changes in the completed report, press the Run button. This will launch a local (i.e. client) report handler that will display your report in the Report Builder view. The report handler will generate a parameter input interface similar to the one that would be displayed if the report were generated by SSRS tools and displayed in a browser window.

Creating a locally-posted report

Creating a locally-posted report. In some cases, it may be useful to save and retrieve reports without using the SSRS directory. This mechanism is similar to the architecture of the ReportViewer toolkit, but not so easy to learn. The process implies a departure from the typical process of creating a new report offered by the wizard: this is done to help the Report Builder report handler find a specific report when needed.

In the Report Builder window, click the Files icon and select New. The Report Builder dialog box will appear, containing two wizard icons (Table or Matrix and Chart). Click the Table or Matrix icon to initiate the locally stored report creation process. You can also use the Chart wizard to create a locally stored report.

The New Table or Matrix dialog box will appear and you will be prompted to enter the data source (including establishing a connection to an existing SSRS instance). The list of data sources is populated from connections to data sources stored on the system since the last time Report Builder was launched. If you want to see the report hosted on the server, click the Browse button and review the contents of the SSRS directory. To create a new locally hosted report, click the New button to create a new data source.

You will see the Data Source Properties dialog box, which is used to create a connection string to SQL Server (or any other data source). From the drop-down menu, select the desired connection type (e.g. Microsoft SQL Server, OLE DB, Microsoft SQL Server Analysis Services) and click Build to connect to the provider. NET Data Provider launches a typical connection dialog box. Select the data source you are interested in and click OK, which will create a connection string.

To enter credentials into the connection string, click the Credentials item and select the type of authentication that the report handler will use to establish the connection to the database, as shown in screen 5. When preparing reports, it is desirable to use SQL Server; in this case, reports will not be able to execute SQL queries as well as operations in Data Manipulation Language (DML) that they should not perform. Keep in mind that end users can select the Use current Windows user option, which can make the system a target for Trojan horse attacks. By clicking OK, accept the connection string and account settings created.

When the New Table or Matrix Report Wizard window opens again with the newly created data source selected for this report, click Next and continue with the report generation. The Design a query dialog box will open. Note that the database view pane is populated only with database objects (tables, views, stored procedures) to which the authenticated user has access.

From now on, you can work with this wizard in the same way as with the Visual Studio BI or ReportViewer report design wizards. You should correctly select columns for aggregation or to group rows around them or (when working with matrix reports) data columns. Practical guidelines for creating reports can be found in Hitchhiker’s Guide to SQL Server 2000 Reporting Services (by Peter Blackburn and William R. Vaughn, Addison Wesley Publishing, 2004).

Thus, Microsoft experts have subjected the original version of Report Builder so deeply recycled that they received the output of a completely new application. In fact, Report Builder 2.0 resembles the original tool so little that Microsoft should have renamed it rather than transfer to the new product the negative associations that database administrators may have developed with Report Builder 1.0. While Report Builder is a valuable and, generally speaking, easy to use tool, it is difficult to use it to edit existing deployed reports. In addition, database administrators and managers must perform a thorough security analysis before deploying this application.

Are you ready to use Report Builder?

The Report Builder report designer is not suitable for all operating conditions. To decide if the application will be useful for your organization, you need to find out if report developers and end users will be able to perform the following operations.

• Specify the specified instance of SQL Server Reporting Services (SSRS). This SSRS instance must be properly activated and running, and users must know how to make the initial connection to the correct instance.
• Select the appropriate data source for the report. Users need to know what the data source is, and how to select a catalogued shared data source or create a new data source that points to a report database, file, or other data source. Report Builder 2.0 makes this easy by providing an SSRS directory that can show the public data sources, but in cases where the reporting administrator has not created a specific directory for the data sources that users can access, there is likely to be little benefit in providing a directory.
• Select the appropriate data row source for the report. Users should know how to select the appropriate table, view or stored procedure that the database administrator responsible for reports has given sufficient rights. In some cases, the user will have to create an SQL query in order to get the report data. It should be noted that granting rights even to a table that is not dangerous can be problematic if users do not know how to build a WHERE sentence or why the ability to build such sentences is important.
• Understand how data extraction operations affect system performance. That is, are user-generated reports built on actual data or snapshot data? Do users have access to the production database where their queries may lead to performance degradation?
• Understand how to use report elements to create a report layout. Alternatively, use the report wizard.
• Save/change new reports in SSRS directory. Keep in mind that the decision to allow users to make changes to existing reports has far-reaching consequences.
• Refer to the data mentioned in the queries. Although the database itself should be locked down, users need access to some of the data.
• Understand how to process exceptions. Users need to know how to handle typical exceptions. Like SSRS, locally generated reports provide all exceptions to end users who are likely the least prepared to handle such issues, although they may themselves be responsible for the problems. I recommend that you create a page listing the typical errors as well as the solutions offered by the database administrator responsible for the reports.

Organizations implementing Report Builder 2.0 will have to invest heavily in training the responsible database administrators, report developers and end users.

Report Builder and security issues

For users of Report Builder, it’s easy and maybe even too easy to solve tasks such as creating reports on data from basic tables, views and stored procedures. When users create reports, they can specify the SQL source code to extract the data. Unfortunately, queries generated by users are not always efficient.

Before putting your company’s most valuable information at the disposal of end users, carefully analyze which users really need this data. The fact is that measures such as blocking access to base tables and granting limited permissions to work with certain views and stored procedures may not be enough. Any application that allows authentication based on the Security Support Provider Interface (SSPI) is vulnerable to Trojan horse attacks. Harmless seemingly harmless queries embedded in reports can contain malicious SQL instructions that grant rights to certain users and groups, or worse, actually destroy data. To prevent such information security breaches, make sure you do not have any “super-users” who may receive reports that secretly execute SQL instructions unrelated to a query that aims to return certain data to the report. The most obvious targets of these attacks are database administrators.

To ensure security when deploying Report Builder, follow the additional recommendations below.

• Configure SQL Server Reporting Services (SSRS) so that report users only have access to the target sections of the report directory. Organize the SSRS report directory so that database administrators can only provide access to a limited number of directory hierarchy trees.
• Configure production report data sources to exclude SSPI-based authentication. In the connection lines to the data sources, you should use SQL Server registration accounts designed to grant access rights only to specified stored database procedures and views. In addition, you should disable the SSPI interface authentication tools on the SSRS server.

Reduce the database attack contact zone so that access to the base tables is blocked and only access to a limited number of stored procedures and views designed to provide appropriate access to your protected data is left.

Use stored procedure report settings that help you refine report requests without overloading your system.

Learn about SSRS roles and how to assign them to end users and domain groups. For information on SSRS roles, see Chapter 4 of Hitchhiker’s Guide to SQL Server 2000 Reporting Services (by Peter Blackburn and William R.Vaughn, Addison Wesley Publishing, 2004).

Report Builder and ReportViewer

There are significant differences between Report Builder 2.0 report designer and Windows Forms or the ReportViewer control located in ASP.NET environment. All connection operations and requests embedded in the Report Definition Language (RDL), Report Builder performs from the client.

When using the ReportViewer control, these operations are performed by the user code. To establish a connection and return a suitable data structure (say, a data table), which will be transferred to the local report processor of the ReportViewer element, it is necessary to create and execute code on the client side.

You can also set ReportViewer to Server mode and simply point to an existing catalogued report, after which SSRS services will return the HTML code. Another significant difference is that Report Builder 2.0 developers (as well as users) do not have to install either Visual Studio or SQL Server business intelligence (BI) tools, besides they can do without programming skills in Visual Basic.NET (VB. NET) or even C#.

SSRS Report Builder Tutorial – Creating Your First Report