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
$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
$conn.Connect()
$smo = New-Object Microsoft.SqlServer.Management.Smo.Server -ArgumentList $conn
# Create the ReportServer and ReportServerTempDB databases
$db = $smo.Databases["master"]
$db.ExecuteNonQuery($dbscript)
# Set permissions for the databases
$dbscript = $configset.GenerateDatabaseRightsScript($configset.WindowsServiceIdentityConfigured, "ReportServer", $false, $true).Script
$db.ExecuteNonQuery($dbscript)
# 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)
$configset.InitializeReportServer($configset.InstallationID)
# 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
$configset.IsReportManagerEnabled
$configset.IsInitialized
$configset.IsWebServiceEnabled
$configset.IsWindowsServiceEnabled
$configset.ListReportServersInDatabase()
$configset.ListReservedUrls();
$inst = Get-WmiObject –namespace "root\Microsoft\SqlServer\ReportServer\RS_SSRS\v14" `
-class MSReportServer_Instance -ComputerName localhost
$inst.GetReportServerUrls()
}

22 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 github.com/phola/SSRS-Docker (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.
        Sven,
        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],’192.168.10.4′,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.

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

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

WordPress.com Logo

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

Google photo

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

Twitter picture

You are commenting using your Twitter 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.