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&quot;, 1033)
	# For SSRS 2016-2017 only, older versions have a different name
	$configset.SetVirtualDirectory("ReportServerWebApp", "Reports", 1033)
	$configset.ReserveURL("ReportServerWebApp", "http://+:80&quot;, 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()
	}

view raw Configure-Sql2017RS.ps1 hosted with ❤ by GitHub