Administering SSRS with PowerShell

Reading Time: 4 minutes

I’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+)

#Prerequisites
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
#---------------------------------------------

Clear-Host
$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

$startTime
$endTime

Reporting Services Module

#------------------------------------------------------
#Prerequisites
#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={$_.Roles.name}}
}
$rsSecurity | Export-csv -Path C:\SSRS_Folder_Security.csv -NoTypeInformation
$endTime = Get-Date

$startTime
$endTime
#------------------------------------------------------

Timings

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\CraigPorteous.com\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\CraigPorteous.com\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\CraigPorteous.com\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