Using Report Builder 2.0 to create reports against data from base tables, views, and stored procedures is easy—perhaps too easy. When users create reports, they can specify raw SQL code to fetch data for a report. Unfortunately, users don’t always write efficient queries.
Before you expose your company’s most precious data to end users, carefully consider which users need the information. Simply blocking access to base tables and granting limited (i.e., read-only) access to specific views and stored procedures might not be enough. Any application that uses Security Support Provider Interface (SSPI) authentication is vulnerable to Trojan attacks. Seemingly innocent queries embedded in reports can contain malicious SQL statements that extend rights to certain users or groups—or worse, actually destroy data. To prevent such security breaches, make sure you have no “super users” who might receive a report that surreptitiously executes SQL statements that are beyond the scope of the query used to return report data. DBAs themselves are the most tempting and obvious targets for these attacks.
To ensure security when deploying Report Builder 2.0, adhere to the following additional recommendations:
- Configure SQL Server Reporting Services (SSRS) to restrict report users to focused portions of the report catalog. Organize the SSRS report catalog so that DBAs can grant access to only specific trees of the catalog’s hierarchy.
- Configure production report data sources to avoid the use of SSPI authentication. Data source connection strings should use SQL Server login accounts designed to grant access only to specific database stored procedures and views. In addition, you should disable SSPI authentication on the SSRS server, as I discuss in my blog (see “SSPI Security Meets Helen of Troy”).
- Reduce the database surface area of attack to deny access to the base tables and to permit access only to specific stored procedures and views written to return appropriate access to your guarded data.
- Use parameters in the stored procedures that you expose to report users that help focus report queries without overloading the system.
- Familiarize yourself with SSRS roles and how to assign them to end users and domain groups. For information about SSRS roles, see Peter Blackburn and William R. Vaughn, Hitchhiker’s Guide to SQL Server 2000 Reporting Services (Addison Wesley, 2004), Chapter 4.
