Managing SSRS security and using PowerShell automation scripts

So much has changed with Reporting Services 2016 but in terms of security it’s the same under the hood and that’s not necessarily a bad thing. SSRS has long had a robust folder & item level security model with the ability to inherit permissions from parent folders, much like SharePoint and windows in general.

Managing this security model, however, can become difficult as the use of SSRS expands over years & even versions. 5 folders & 40 reports quickly become 30 folders, 200 reports and many different business units or even clients in the same environment. Once you introduce processes to move databases down to non-production environments, it quickly becomes a difficult task to maintain security never mind implement any changes or improvements. I want to outline some tips that have helped me over the years and some PowerShell scripts that will save you hours of clicking!

Best Practices & tips

AD Groups reduce maintenance

It might be an obvious one, but it’s a basic rule in my opinion. Wherever possible, grant security in SSRS (& your database too) to AD groups and fill those groups with the relevant users. This gives you a single place to add/remove people, whether that’s a quarterly task or once every decade. Using AD groups may give you one more step to check “who has access to what” but it makes finding & maintaining those users significantly easier.

Keep permissions to a minimum

Reporting Services has several “out of the box” roles to choose from. If none of those fit the bill or a user ~~needs~~ wants slightly more than Browser access (i.e. View Data Sources) don’t just bump them up to full Content Manager.

By connecting to your Reporting Services instance via Management Studio (SSMS) you can View the built in security roles. From here you can Add permissions to an existing role or create a whole new role, if only a subset of users need the extra permissions.

Some further reading on SSRS roles: Role Definitions – Predefined Roles

Clean up default Permissions

You may have noticed that by Default, BUILTIN\Administrators is added as a Content Manager to the Home folder (and every inherited folder!). This is great for initial setup. It allows the server admin(s) to access Report Manager & get started without any security prerequisites.

Beyond “Day 1” setup this should be removed. In the vast majority of implementations, the server admin will not be the Reporting Services admin, or there will always be people in one group who shouldn’t be in the other.

If you leave this in place you are giving everyone who has administrator rights of the SSRS server full Content Manager access. This is best to remove at first implementation before your instance grows, folders get unique permissions and it’s no longer a single click to fix (though I’ll give you a fast way to fix it later!)

Plan your Security Model

When implementing Reporting Services from scratch, or any new technology/app, it can be too easy to just use a select few “service accounts” for multiple functions & tasks. Usually it’s a case of “whatever gets this fixed/online the fastest”. Using a single AD account for each function within SSRS is good practice & minimises security risk.

An example of accounts used in a production environment:

Domain\DataAccess for stored credentials in datasources. This account doesn’t need any access in SSRS or any server permissions. It may be granted db_datareader or more on the datasources it needed to access to.
Domain\Deploy would be used to deploy content to Reporting Services. This would only need the Publisher role in SSRS. It could also be a group of senior developers or a dev manager.
Domain\Service is the account Reporting Services would run under. This would need the RSExecRole on the ReportServer DB (this is granted during configuration/install). This account would have no data access or Reporting services access.
Domain\rsAdmins is an AD group with the admins who manage content & permissions. Generally, this group would not need data access

Now, this level of separation isn’t always possible and in some smaller organizations a single person covers most of these functions so don’t take the above as a hard requirement. Using these separate domain accounts reduces a single point of failure caused by password lockouts & resets or compromised accounts.

PowerShell Automation

There’s a great deal of automation that can be achieved with PowerShell in Reporting Services., I’ve detailed a few scripts below specific to this security topic, but there’s an abundance of content out there for many tasks, such as deploying reports, folders, data sources etc. Although I’ve focused primarily on native mode Reporting Services, there are also scripts that work with SharePoint integrated mode too.

Development environment security

Unlike your production environment you may want to simplify your dev environment’s security to make it easier for developers to deploy & test without running into permissions issues. This is a good place to utilize Reporting Services’ inherit functionality. Setting all folders to “Revert to Parent Security” makes it easy to add/remove permissions to the whole environment from the top level folder.

If you ever need to copy down your production database this can be a mammoth task to update. This is where PowerShell comes in handy. The following simple script will revert all subfolders in an SSRS environment to Revert to Parent Security.

#---------------------------------------------

# Author: Craig Porteous

# @cporteous

# Synopsis: Revert all SSRS (native mode)

# folders to inherit from Parent

# Security

#---------------------------------------------

Clear-Host

$ReportServerUri = 'http://PorteousSQL1/ReportServer/ReportService2010.asmx?wsdl'

$InheritParent = $true

$rsProxy = New-WebServiceProxy -Uri $ReportServerUri -UseDefaultCredential

#List out all subfolders under the parent directory

$items = $rsProxy.ListChildren("/", $true) | `

select TypeName, Path, ID, Name | `

Where-Object {$_.typeName -eq "Folder"}

#Iterate through every folder

foreach($item in $items)

{

$Policies = $rsProxy.GetPolicies($Item.Path, [ref]$InheritParent)

#Skip over folders already marked to Inherit permissions. No changes needed.

if(-not $InheritParent)

{

#Set folder to inherit from Parent security

$rsProxy.InheritParentSecurity($item.Path)

}

You may need to adjust the .asmx file for different versions of SSRS though this should work just fine in 2012 onwards.

Security auditing

If you’re inheriting an existing environment or even want to overhaul/audit your current security, the following PowerShell script will allow you to quickly output every folder’s security to csv allowing you to analyse erroneous permissions without searching through folders in Report Manager.

#---------------------------------------------

# 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 = @()

$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:\$ReportServerUri.csv" -NoTypeInformation

Targeted Changes

Following a security review, you may want to add or remove a single AD account/group across every folder in your environment. There may be many occasions that call for such a blanket change. Again, this would normally be a laboriously manual task without PowerShell. These little snippets show how it can be done & you can always edit these to target a specific folder (& all its sub-folders).

#---------------------------------------------

# Author: Craig Porteous

# @cporteous

# Synopsis: Add a specific user/group to all

# SSRS (native mode) folders with a

# specified Role. Excludes inherited

# folders

#---------------------------------------------

Clear-Host

$ReportServerUri = 'http://PorteousSQL1/ReportServer/ReportService2010.asmx?wsdl'

$InheritParent = $true

$GroupUserName = 'Domain\Deploy'

$RoleName = 'Publisher'

$rsProxy = New-WebServiceProxy -Uri $ReportServerUri -UseDefaultCredential

$type = $rsProxy.GetType().Namespace;

$policyType = "{0}.Policy" -f $type;

$roleType = "{0}.Role" -f $type;

#List out all subfolders under the parent directory

$items = $rsProxy.ListChildren("/", $true) | `

SELECT TypeName, Path, ID, Name | `

Where-Object {$_.typeName -eq "Folder"}

#Iterate through every folder

foreach($item in $items)

{

$Policies = $rsProxy.GetPolicies($Item.Path, [ref]$InheritParent)

#Skip over folders marked to Inherit permissions. No changes needed.

if($InheritParent -eq $false)

{

#Return all policies that contain the user/group we want to add

$Policy = $Policies |

Where-Object { $_.GroupUserName -eq $GroupUserName } |

Select-Object -First 1

#Add a new policy if doesnt exist

if (-not $Policy)

{

$Policy = New-Object ($policyType)

$Policy.GroupUserName = $GroupUserName

$Policy.Roles = @()

#Add new policy to the folder's policies

$Policies += $Policy

}

#Add the role to the new Policy

$r = $Policy.Roles |

Where-Object { $_.Name -eq $RoleName } |

Select-Object -First 1

if (-not $r)

{

$r = New-Object ($roleType)

$r.Name = $RoleName

$Policy.Roles += $r

}

#Set folder policies

$rsProxy.SetPolicies($Item.Path, $Policies);

}

You can then use the following script to remove a user/group or reverse the change made in the last script.

#---------------------------------------------

# Author: Craig Porteous

# @cporteous

# Synopsis: Remove a specific user/group from

# all SSRS (native mode) folders.

# Excludes inherited folders

#---------------------------------------------

Clear-Host

$ReportServerUri = 'http://PorteousSQL1/ReportServer/ReportService2010.asmx?wsdl'

$InheritParent = $true

$GroupUserName = 'PORTEOUSSQL1\pInstall'

$rsProxy = New-WebServiceProxy -Uri $ReportServerUri -UseDefaultCredential

#List out all subfolders under the parent directory

$items = $rsProxy.ListChildren("/", $true) | `

SELECT TypeName, Path, ID, Name | `

Where-Object {$_.typeName -eq "Folder"}

#Iterate through every folder

foreach($item in $items)

{

$Policies = $rsProxy.GetPolicies($Item.Path, [ref]$InheritParent)

#Skip over folders marked to Inherit permissions. No changes needed.

if($InheritParent -eq $false)

{

#List out ALL policies on folder but do not include the policy for the specified user/group

$Policies = $Policies | Where-Object { $_.GroupUserName -ne $GroupUserName }

#Set the folder's policies to this new set of policies

$rsProxy.SetPolicies($Item.Path, $Policies);

}

NOTE: The above targeted scripts won’t add or remove users or groups from the top level folder. This process can be easily added, though I’ve omitted it to reduce the risk of removing an admin user/group from the entire site and in the case of adding users, I’ve worked with RS instances where multiple clients share a single instance and only admin accounts have access to the top level “home”.

Love PowerShell!

I hope I’ve provided a few examples of security practices in SSRS and some basic PowerShell scripts to automate administration of security in Reporting Services. You can build upon these scripts to do more advanced tasks such as setting instance wide security from an input file (good for refreshing other environments from production backups.

I know there is a lot of good work going into PowerShell for DBA tasks over at dbatools.io that shows PowerShell is something you want on your tool belt!

Microsoft also put together a bunch of PowerShell scripts for Reporting Services late last year. You can find the article: Community contributions to the PowerShell scripts for Reporting Services & the scripts are on GitHub here: ReportingServicesTools

See more

For SSRS documentation, consider ApexSQL Doc, a tool that documents reports (*.rdl), shared datasets (*.rsd), shared data sources (*.rds) and projects (*.rptproj) from the file system and web services (native and SharePoint) in different output formats.

References

SSRS Roles: Role Definitions – Predefined Roles
DBATools page
Microsoft SSRS PowerShell Tools: Community contributions to the PowerShell scripts for Reporting Services
GitHub link: Reporting Services Powershell Tools

Author
Recent Posts

Craig Porteous

Craig is a Microsoft certified BI Developer & Administrator and has worked with the Microsoft SQL stack for over 8 years. From working with cloud technologies like AWS & PowerBI to managing enterprise level Projects & deployments, Craig is passionate about developing his skills. He enjoys contributing to a personal blog to give back to the SQL community. In his spare time Craig is an avid runner

View all posts by Craig Porteous

Latest posts by Craig Porteous (see all)

How to secure Reporting Services with Group Managed Service Accounts (GMSA) - November 7, 2018
Contribute, contribute, contribute! - June 20, 2018
Top 10 things you must document in SQL Server Reporting Services (SSRS) - February 26, 2018

Best Practices & tips

PowerShell Automation

Love PowerShell!

See more

References

Related posts: