Complete automated configuration of SQL Server 2017 Reporting Services

In my post two days ago on silently installing SQL Server 2017 Reporting Services (SSRS 2017), I just provided the most basic information on configuration. In fact, after installing SSRS 2017, it will be completely unconfigured. Configuration can be done using the Reporting Service Configuration Manager GUI. In my case, the configuration needs to be automated, so I use PowerShell.

I have posted my complete PowerShell script for configuration of SSRS 2017 to a GitHub Gist. It’s embedded below. The script itself has comments that will hopefully allow you to follow the flow, but here is a quick overview of the different steps:

  1. Get a WMI object with the configuration settings for the SSRS 2017 instance.
  2. Get a SQL script to create the ReportServer and ReportServerTempDB databases.
  3. Establish a connection to the default SQL Server instance on the same machine.
  4. Execute the SQL script.
  5. Get and execute a second SQL script, this time to set the permissions for the SSRS 2017 service account.
  6. Set the SSRS database connection to this newly created database.
  7. Configure the virtual directory name and URL of the web service.
  8. Configure the virtual directory name and URL of the report manager web app.
  9. Initialize the report server with encryption for sensitive data.
  10. Restart the service.
  11. Output the new configuration.

Note: I expect this script will also work with SQL Server 2016 Reporting Services. It will not work with earlier versions without changing the name of the Report Manager web application to ReportManager.

function Get-ConfigSet()
return Get-WmiObject –namespace "root\Microsoft\SqlServer\ReportServer\RS_SSRS\v14\Admin" `
-class MSReportServer_ConfigurationSetting -ComputerName localhost
# Allow importing of sqlps module
Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Force
# Retrieve the current configuration
$configset = Get-ConfigSet
If (! $configset.IsInitialized)
# Get the ReportServer and ReportServerTempDB creation script
[string]$dbscript = $configset.GenerateDatabaseCreationScript("ReportServer", 1033, $false).Script
# Import the SQL Server PowerShell module
Import-Module sqlps -DisableNameChecking | Out-Null
# Establish a connection to the database server (localhost)
$conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection -ArgumentList $env:ComputerName
$conn.ApplicationName = "SSRS Configuration Script"
$conn.StatementTimeout = 0
$smo = New-Object Microsoft.SqlServer.Management.Smo.Server -ArgumentList $conn
# Create the ReportServer and ReportServerTempDB databases
$db = $smo.Databases["master"]
# Set permissions for the databases
$dbscript = $configset.GenerateDatabaseRightsScript($configset.WindowsServiceIdentityConfigured, "ReportServer", $false, $true).Script
# Set the database connection info
$configset.SetDatabaseConnection("(local)", "ReportServer", 2, "", "")
$configset.SetVirtualDirectory("ReportServerWebService", "ReportServer", 1033)
$configset.ReserveURL("ReportServerWebService", "http://+:80", 1033)
# For SSRS 2016-2017 only, older versions have a different name
$configset.SetVirtualDirectory("ReportServerWebApp", "Reports", 1033)
$configset.ReserveURL("ReportServerWebApp", "http://+:80", 1033)
# Re-start services?
$configset.SetServiceState($false, $false, $false)
Restart-Service $configset.ServiceName
$configset.SetServiceState($true, $true, $true)
# Update the current configuration
$configset = Get-ConfigSet
# Output to screen
$inst = Get-WmiObject –namespace "root\Microsoft\SqlServer\ReportServer\RS_SSRS\v14" `
-class MSReportServer_Instance -ComputerName localhost

25 thoughts on “Complete automated configuration of SQL Server 2017 Reporting Services

  1. Hi Sven
    I did an automated install of SSRS 2017 and the Get-WmiObject resturn ‘Invalid Namespace’. Any ideas?

      1. Hi Sven,
        The installation hadn’t completed properly. I enabled .netFx35 feature, reinstalled SSRS (on a clean machine) and added the following line before the call to Get-Configset:
        $env:PSModulePath = $env:PSModulePath + “;C:\Program Files (x86)\Microsoft SQL Server\140\Tools\PowerShell\Modules”

        All works great now.

  2. Hi Sven

    Many thanks for this! I’ve used it to set up SSRS in a docker container and it works great!
    source on (credit and link back to this article are in the readme)

    cheers, Geoff

  3. Hi Sven, this script has made my lite-touch sccm configuration even lighter (lite-r).

    One question, is it possible to change the service account that is used, so it is not using the Virtual Service Account and instead uses my own domain account created specifically? This also is reflected in the “Current Report Server Database Credential” whereby the end result from running your script shows the Login name as: “NT SERVICE\SQLServerReportingServices”, and i’d rather this be the domain account I created specifically.

    If you could provide any guidance here, that’d be great. Thanks.

  4. Hi Sven,
    I just downloaded the script from GitHub and attempted to run it on my SQL server with RS installed. When ran the script appears to do nothing and comes immediately back to a PS prompt. Is there any PS module we have to import first?

      1. Hello JC, you might be running against RS V15 and this solution appears to be using V14.
        Have you thought about doing the same thing using CIM commandlets wtih WMI being deprecated…

  5. Hi Sven,
    I am testing and so far it is what i actually need to make my script automated, but i was wondering when i use this : $configset.CreateSSLCertificateBinding(‘ReportServerWebService’,($SSLList.CertificateHash)[0],’′,443,1033) for example, it does add the name of the cert plus the IP, but one thing i noticed is that it doesn’t exactly do the commit of it until i go check it on the GUI and see if it is there then press okay. Is there a way of committing it or is it normal like that and it will be recognized that way ?

    Thank you in advance.

      1. No i didn’t, thank you very much.
        Now just for curiosity the moment that the information needed was inserted, it won’t do the commit by itself right ?

  6. Hi Sven,
    I have tested your script and it just works perfectly. I have one question though… What do I have to change in case I want to SSRS Instance not to be called “SSRS” but having a different name to fit to my naming standard, e.g. R01.

      1. Can you please share some snippet or thoughts on how to change the ssrs 2019 instance name? Not able to find any parameter to specify the instance name on installation setup (UI/CMD). Thanks in advance.

  7. Hi Sven,
    I’ve tried running the above script it was working fine as admin but i am trying to automate the env using packer or either terraform at the run time it was throwing the error

    return Get-WmiObject -namespace “root\Microsoft\SqlServer\ReportS …
    ==> googlecompute: + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    googlecompute: UserPolicy Undefined
    ==> googlecompute: + CategoryInfo : InvalidArgument: (:) [Get-WmiObject], ManagementException
    ==> googlecompute: + FullyQualifiedErrorId : GetWMIManagementException,Microsoft.PowerShell.Commands.GetWmiObjectCommand

    Can u please help me out. I’ve tried several things like elevating powershell internally and as well as in packer command as well but none of it works. any workaround to run the powershell script as admin or to get rid of the error

Let me know what you think, or ask a question...

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.