CHAPTER 11 SECURING REPORTS- Report execution activity: Which user executed which report and when, and what
status the report finished with (to find possible issues) - Parameter inputs for reports: Which parameters were entered by a user
The latter is important because even though SSRS contains validation for certain types of
parameters, such as Integer and DateTime, when a report parameter uses a string, it is susceptible to a
SQL injection attack. SQL injection attacks are made possible when a Web page or service takes input
from a user or program that could contain injected code that could execute on the SQL Server machine.
These types of attacks can be malicious in nature or can cause the report or page to deliver more data
than was intended.
We have simply made the report a table instead of a matrix, which was more in line with the
PivotTable analysis for performance. In this modified report, called Report Execution Audit (which is
available in the Pro_SSRS chapter 11 files), you will also include the parameters that the user has
selected. Go ahead and upload this report and a new datasource pointing to your ReportServer database
in a new folder in the root of your SSRS installation called Management. You will also need to point the
report to this new datasource that you are creating. You can see the query we are using to pull this
information in Listing 11-1.
Listing 11-1. Execution Log Query
SELECT
Catalog.name
, ExecutionLog.UserName
, ExecutionLog.Parameters
, ExecutionLog.TimeStart
, ExecutionLog.TimeEnd
, ExecutionLog.Status
FROM ExecutionLog
INNER JOIN Catalog ON ExecutionLog.ReportID = Catalog.ItemID
In Figure 11-23, you can see the times that the selected reports were executed as well as the
parameter name and value that were entered. In addition to showing how the user interacted with the
report via the Parameter field, the Report Execution Audit report also shows when the report was started
and ended. Having this level of auditing is a valuable ally in the struggle to maintain security for
confidential information. With HIPAA, it is also necessary to maintain an audit trail of user access to
data. If you are suspicious that someone is accessing information they are not authorized to view, this
report can serve as the audit trail, along with other normal auditing procedures such as the Windows
event log.