0161 513 0960Jack O'Sullivan
March 22 2021
Under the hood, SSRS relies on SQL queries to extract data from the back-end database(s). SSRS supports parameterised queries, automatically generating input fields for users to supply the required values. Connections to the back-end database are controlled by a "data source", which holds the connection string and credentials for the database server. A data source can be configured on the SSRS server and shared between multiple reports, or embedded within each individual report.
ATTACKING SSRS
SSRS has a web interface, where users can view reports. With the correct permissions, users can also upload their own report files and execute them on the SSRS server. Report definition files contain the SQL queries required to generate the report, if an attacker is able to upload their own report files, they will gain the ability to execute arbitrary SQL on the database server. Much like traditional SQL Injection attacks, this can allow the attacker to access any data the DB user account (configured in the data source) has access to.
BUILDING REPORTS
Lets take a look at how reports are generated. For this post, I have created a Virtual Machine with the following software:
- Window Server 2016
- SQL Server 2016 Express with Advanced Features (which allows SSRS to be installed)
- Report Builder 3
Some dummy databases were then added to the SQL Server installation, and an SSRS data source, running as the SA account was added.
Installation and configuration of SSRS is out outside of the scope of this post, but there are >plenty of tutorials online.
This server will be our "victim" host. The VM was cloned, and the "AdventureWorks2016" database added to SQL Server. This is the Microsoft demo database, designed to mimic a real company's data. This clone will be where we create our malicious reports. To make things more realistic, a new data source with a different name was configured on the development server.
Lets create a simple report. A full tutorial on how to build reports would take up an entire series of posts. The steps are outlined here but plenty of resources are available online if you want to follow along.
In report builder, we first add the data source we configured to our report. Note that this is the data source configured on the development (cloned) server. Next, we can add a new data set and specify a SQL query.
This query simply selects the top 100 records from the Person table in the Person schema, within the AdventureWorks2016 database. We can then add a table to the report to display the data.
It's worth noting that we need to specify the columns in the table, this will be important later.
With the report created, we can run it on the development server and see the data.
We now have a working report, which can be saved and uploaded to our victim server.
WHAT ABOUT THE DATA SOURCE?
You may have spotted that we purposefully changed the name of the data source on the development server. To make things realistic, we are assuming zero knowledge of the victim server configuration. So far we have a working report and the ability to upload it to the victim. Without a valid data source, SSRS won't be able to execute the report. Let's see how we can get around this problem.
On the victim machine, we upload our new report.
If we try and access the report, SSRS returns the following error:
"The report server cannot process the report or shared dataset. The shared data source 'DataSource1' for the report server or SharePoint site is not valid. Browse to the server or site and select a shared data source. (rsInvalidDataSourceReference)"
Back on the home page, clicking the three dots next to the report will open a menu. click Manage and the following screen will load.
We can select a new data source from this menu.
SSRS gives us a choice of data source, displaying any pre-configured, shared, data sources present on the server.
With the "DS_All_Reports" data source selected, running the report now presents a different error.
"An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset "DataSet1". (rsErrorExecutingCommand)
Invalid object name 'AdventureWorks2016.Person.Person'."
Unless the victim server also has the AdventureWorks2016 database (it doesn't), this error is expected. The table we are trying to read from doesn't exist. However, we have removed the need to know the data source details and can now upload and execute arbitrary reports.
RETRIEVING DATA
We can now start enumerating the victim server and extracting data from whatever databases we have access to. As we are assuming zero knowledge of the victim server, we will start by getting the version number, a list of databases and tables within those databases.
This brings us to parameterised queries. In SSRS, we can use parameters in reports, just like you can in standard SQL. The difference is, SSRS presents a nice interface for the user to select a parameter value. Parameters can be free-text input, or pre-populated from report data sets.
To grab the list of available databases, we can use the following basic SQL query:
|
1 |
SELECT name FROM master.dbo.sysdatabases
|
The result of this query can be set as the available values for a report parameter.
We can use this value in a new query, which will return all the tables and their schemas from the selected database:
|
1
2
3 |
declare @sql varchar(max)
set @sql = 'use '+@DatabsaeName+' select TABLE_NAME, TABLE_SCHEMA from INFORMATION_SCHEMA.TABLES;';
Exec (@sql)
|
This is where things get a bit more complicated. As we don't know the database name in advance, we can't use a USE statement to set the database for the SQL queries we build, as such, they all run on the Master database. INFORMATION_SCHEMA is database dependant, querying INFORMATION_SCHEMA.TABLES on the master database will return different data than when queried in the AdventureWorks2016 database. Unfortunatly, SQL does not let us use parameters in USE statements, so the following won't work.
|
1 |
USE @DatabaseName
|
To get around this, we need to use dynamic SQL. We build the query as a string, letting SQL evaluate the @DatabaseName parameter, this gives us the actual query which we can execute by calling Exec.
Dropping these queries in a report and setting up the parameter gives us a generic report, which we can upload to any victim server, that will display all the tables in a selected database.
The above shows the new report uploaded to the victim server. We are displaying the tables in the "RnD" database. SSRS comes with an export feature, so we can export this data into a PDF or a Word document and drop it right into our reports.
RETRIEVING TABLE DATA
Displaying the databases and tables is interesting, but we really want the data. The "Portal_Users" table above likely contains credentials, which could allow us to elevate privileges or access other systems.
Unfortunately, this is where things start getting complicated. SSRS does not support dynamic data sets. You must know the number of output columns when building the report. We could retrieve the columns, build a mock database and a custom report for each table we want to view, but this is time consuming. On a time-limited engagement we likely won't have the time to build a custom report for each table in a database. We need a generic solution.
Lets list the problems we need to solve:
- The number of columns must be known at build time
- Each table in the database will have a different number of columns, and different data types
- We need this report to work on any database, so pre-compiling reports for different commercial applications is out.
All these problems are further complicated by the need to write dynamic SQL queries for everything. We can't set the DB name in advance, and even if we could, we don't know the table structures.
Concatenating all the column values into one string per table row would allow us to build a report with one column in the display table, our data set would always return one item, and SSRS doesn't care about the length of strings.
We can retrieve schema information from the database and use that to build a generic query which will select each column, concatenate them and output the results.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31 |
/*Populate a table containing all the schema/table/column names in the provided DB */
DECLARE @SQL varchar(max)
SET @SQL=''
SELECT @SQL=@SQL+'UNION
select
sh.name, o.name,c.name
from '+d.name+'.sys.columns c
inner join '+d.name+'.sys.objects o on c.object_id=o.object_id
INNER JOIN '+d.name+'.sys.schemas sh on o.schema_id=sh.schema_id
'
FROM sys.databases d where d.name = @DatabsaeName
SELECT @SQL=RIGHT(@SQL,LEN(@SQL)-5)+'order by 1'
print @SQL
declare @Resultstable table (SchemaName varchar(max), TableName varchar(max), ColumnName varchar(max))
insert @Resultstable exec(@SQL)
/*Build the data retrieval statement*/
DECLARE @s VARCHAR(max)
DECLARE @query NVARCHAR(max)
DECLARE @string nvarchar(max)
SELECT @s = ISNULL(@s+', ','') + 'COALESCE(''-'' + CONVERT(nvarchar(max),'+c.ColumnName +'),'''')'
FROM @Resultstable c
WHERE c.TableName = @table and c.SchemaName = @schema
set @string = CONVERT(nvarchar(max), @s)
set @query = 'use '+@DatabsaeName+' select CONCAT(' + @string + ') from ' + @schema +'.' +@table
exec(@query)
|
The first part of this query populates a temporary table with the table names and schemas of all the tables in the supplied database.
It then builds a generic query, wrapping each column in a COALESCE statement, and separating each value with a "-" to make it easier to read later.
This query is then executed inside a CONCAT statement, which merges all the columns into one returned value.
The above shows the output when the report is run against the AdventureWorks person.person table.
The schema is important here, as there may be multiple person tables within the database. We need to specify the schema to ensure we select the right data. In this report, the database name, table name and schema are all selected from report parameters, which are populated with available values.
We can now upload this report to the victim server, reset the data source and exfiltrate data from any table the data source has read access to.
In our demo database, we have managed to extract plain-text credentials.
PREVENTION
If an attacker can access your SSRS instance, you already have problems. The report data may be extremely valuable. Enforcing strong passwords and removing external access should help mitigate this risk.
If an attacker is able to access SSRS, they will still need permissions to upload reports. Removing this permission from all users would prevent this attack. This feature may be required by the business, in which case removing it is simply not an option.
Using a data source with limited access rights will prevent an attacker from accessing any data other than that which is intended to be reported on. If you have an application database, containing usernames and passwords, make sure that your SSRS data source does not have access to read that database. Remove read permissions from any table not required for reporting, or ideally, copy the data to a dedicated reporting database on a separate server.
CONCLUSION
We have seen that SSRS can be exploited to extract data and presented two, completely generic, reports which will allow easy exploitation. In part 2, we will look at how we can exploit SSRS to get shells on the victim server.
The reports presented in this blog are available from our GitHub Repository.
