Administering SSRS with PowerShell

Reading Time: 4 minutes

tsql2sdayI’ve covered a lot of SSRS security tasks using PowerShell over on SQL Shack where I connect directly using the New-WebServiceProxy command. For T-SQL Tuesday I wanted to do a quick comparison between my original method and the official Reporting Services module and see what else the module offers.

The first thing you’ll want to do is to get the SSRS module using the following command. (You’ll need PowerShellGet if you aren’t using PowerShell 5.0+)

Install-Module -Name ReportingServicesTools

Looking at some basic tasks like retrieving a folder’s contents or a specific item’s security (assuming you have the required access), involves a single line of code. It’s easy to use & provides tool-tips for commands. I’ve installed the module, added my ReportServer URI to a variable and I’m ready to start returning data.

Here’s a few example commands:

# Declare URI
$reportServerUri = 'http://PorteousSQL1/ReportServer/ReportService2010.asmx?wsdl'
# Get folder contents
Get-RsFolderContent  -ReportServerUri $reportServerUri -RsFolder $folderName 
# Get report security
Get-RsCatalogItemRole -ReportServerUri $reportServerUri -Path $reportPath

Now lets think about the kind of tasks we might want to automate, the data we’d want to collect that would be a nightmare if done manually. This is where PowerShell shines and as I’m writing about, there’s usually several ways to solve the same problem.

Folder level security in Reporting Services

This is something you’d want to see in an audit, before a significant security change or maybe taken as a backup for piece of mind. I do this regularly, even on instances I’ve setup myself. There’s almost always a random user “added for testing” and never removed and using PowerShell is much easier than fishing through every folder manually, with no point in time record of what security looked like.

A nice feature of the Reporting Services module is recursion. A draw back of my existing scripts is having to loop through all the folders in the instance, which means more lines of code.

Simply adding the -Recurse flag will return every item in that instance (unless I restrict by type as shown):

#List all folders in SSRS
$folders = Get-RsFolderContent  -ReportServerUri $reportServerUri -RsFolder '/' -Recurse | Where-Object{$_.TypeName -eq 'Folder'}

Under the hood, the Reporting Services module has a wealth of abilities built in allowing you to do almost everything with SSRS using PowerShell. There’s also extensive error handling, all of which my “standard Web Service Proxy” example doesn’t cover but even taking that into account, it is SIGNIFICANTLY slower returning the security on all folders. Even when I declared a proxy at the start & referenced this instead of a new connection to the instance for each call to get an item’s security.

Standard Web Service Proxy

# Author:	Craig Porteous
#			@cporteous
# Synopsis: List out all SSRS (native mode)
#			folders & their security policies
#			& output dataset to CSV file

$ReportServerUri = 'http://PorteousSQL1/ReportServer/ReportService2010.asmx?wsdl'
$InheritParent = $true
$SSRSroot = "/"
$rsPerms = @()
$rsResult = @()

$startTime = Get-Date
$rsProxy = New-WebServiceProxy -Uri $ReportServerUri -UseDefaultCredential
#List out all subfolders under the parent directory and Select their "Path"
$folderList = $rsProxy.ListChildren($SSRSroot, $InheritParent) | Select -Property Path, TypeName | Where-Object {$_.TypeName -eq "Folder"} | Select Path
#Iterate through every folder 
foreach($folder in $folderList)
	#Return all policies on this folder
	$Policies = $rsProxy.GetPolicies( $folder.Path, [ref] $InheritParent )
	#For each policy, add details to an array
	foreach($rsPolicy in $Policies)
		[array]$rsResult = New-Object PSObject -Property @{
		"Path" = $folder.Path;
		"GroupUserName" = $rsPolicy.GroupUserName;
		"Role" = $rsPolicy.Roles[0].Name
		$rsPerms += $rsResult
#Output array to csv named after instance URL		
$rsPerms | Export-Csv -Path "C:\SSRS_Folder_Security.csv" -NoTypeInformation	

$endTime = Get-Date


Reporting Services Module

#Install-Module -Name ReportingServicesTools

#Lets get security on all folders in a single instance
$startTime = Get-Date
#Declare SSRS URI
$reportServerUri = 'http://PorteousSQL1/ReportServer/ReportService2010.asmx?wsdl'
#Declare final Security Array
$rsSecurity = @()
#Declare Proxy so we dont need to connect with every command
$proxy = New-RsWebServiceProxy -ReportServerUri $reportServerUri
#List all folders in SSRS
$folders = Get-RsFolderContent -Proxy $proxy -RsFolder '/' -Recurse | Where-Object{$_.TypeName -eq 'Folder'}

#Loop through each folder and add its security to the Security Array
foreach($folder in $folders.Path)
	# Returns the security on a folder
	$security = Get-RsCatalogItemRole -Proxy $proxy -Path $folder
	#Add to Security Array
	$rsSecurity += $security | SELECT Identity, Path, @{n="Roles";e={$}}
$rsSecurity | Export-csv -Path C:\SSRS_Folder_Security.csv -NoTypeInformation
$endTime = Get-Date



On an instance of 190 folders the direct Web Service Proxy script took 02:30s. On the same instance the Reporting Services module took 04:22s. Both returned a csv file of 1650 individual security roles, covering all folders.

I’ve not dug deeply into what the module is doing with all this time, though I know there are aliases being set and checks for SQL versions, defaults for variables etc. I also have no doubt it is far more robust than my quick, direct script but if you’re looking for data quickly, the module may not always be your best option. Exploring the module however, I find you can quickly and easily retrieve concise metadata with a single command.

Deploying Content

Having recently worked on the deployment of SSRS content through C#, I’m impressed to find how easy it is in PowerShell using the SSRS module.

A single command uploads a report, dataset or data source.

Write-RsCatalogItem -Proxy $proxy -RsFolder '/CPorteous/Test' -Path 'C:\Dropbox\\Average Report Execution Times.rdl'

Adding the -WhatIf flag at the end explains exactly what the command will do with the parameters you’ve provided

What if: Performing the operation “Upload from C:\Dropbox\\Average Report Execution Times.rdl to Report Serve
r at /CPorteous/Test” on target “Average Report Execution Times”.

If the destination folder doesn’t exist, you will need to create it first though.

New-RsFolder -Proxy $proxy -RsFolder '/CPorteous' -FolderName 'Test'

We can even upload the entire contents of a local folder to one in Reporting services

Write-RsFolderContent -Proxy $proxy -RsFolder '/CPorteous/Test' -Path 'C:\Dropbox\\rsTest'

Unfortunately, Write-RsFolderContent doesn’t have an -Overwrite flag so any items that already exist will end in an error.

This command would sit well with Out-RsFolderContent allowing you to export entire folders from SSRS to a local path then re-upload, possibly to a new instance.

Wrap up

There is a lot of great functionality in the SSRS module already with some small additions needed to smooth out processes. The module is constantly being updated & developed so jump over to the GitHub page to check it out & give it a try yourself. Here’s a few ideas that have came to me since playing with it…

  • Update Data Source passwords – This is always a pain where I have a single data access account across multiple data sources & need to use the UI to update them one by one.
  • Deploy content & set perms in a single script.
  • Pull down all reports from an instance to quickly re-populate a Dev instance
  • Configure an RS instance unattended
  • Grant & remove permissions from multiple folders



You may also like...

2 Responses

  1. xsnakedoctor says:

    Thanks for the great writeup on this. This is a much older article now but its still incredibly useful. One question I have is regarding import to another instance (in my situation, a QA environment). The development team wants all reports exported from Production and then imported into QA. In your article you mention that the folders must be created if they do not exist.

    Does this mean that the base folders need to be created or do all nested folders need to be created as well? Is there any reason that the script can’t create the folders as they were exported?


  2. Alan Boba says:

    Very new to SSRS and SQL and somewhat new to powershell. This has helped me immensely.

    I am trying to get an extra level of detail and hope you would help.

    From your “Reporting Services Module” script the line
    $folders = Get-RsFolderContent -Proxy $proxy -RsFolder ‘/’ -Recurse | Where-Object{$_.TypeName -eq ‘Folder’}
    gets all the folders.

    Changing the Where-Object to {$_.TypeName -ne ‘Folder’} returns Report and DataSource types.

    I would like to return the file name of the Report or DataSource. I have been unable to learn if either of these types exist as a file system object.

    Looking at $folders[x] | Get-Member I find properties Name, Path, TypeName and ItemMetaData among others.

    Every $folders[x].TypeName of DataSource or Report that I’ve looked at the .Name property matches the end of the .Path property and there is no file extension as part of the .Name property.

    I haven’t been able to figure out how to expand .ItemMetaData to see what it contains.

    Do DataSource and Report types exist as file system objects? If they do, how can the file name be found?

    If they aren’t files system objects where are they found and how are they named?

Leave a Reply