PowerShell script to configure SQL Server 2017 Reporting Services

Configure-Sql2017RS.ps1

<#
#>
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()
}

Much appreciated! This helped me a LOT!

Thank you . It helped.

I have enhanced script to work for below cases

• Work on all other versions of SQL Server
• Configure ReportServer URL with SSL certificate
• Configure Custom HTTPS and HTTP URLs

`$url ="customurl.com"
$httpsport = 443
$wmiName=(Get-WmiObject -namespace root\Microsoft\SqlServer\ReportServer -class __Namespace -ComputerName $env:COMPUTERNAME).Name
$version = (Get-WmiObject –namespace root\Microsoft\SqlServer\ReportServer$wmiName –class __Namespace).Name
$lcid=(get-culture).LCID

Function Get-ConfigSet($version)
{

return Get-WmiObject –namespace "root\Microsoft\SqlServer\ReportServer\RS_SSRS\$version\Admin" `
	-class MSReportServer_ConfigurationSetting -ComputerName localhost

}

Allow importing of sqlps module

Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Force

Retrieve the current configuration

$configset = Get-ConfigSet($version)

$configset

If (! $configset.IsInitialized)
{

# Get the ReportServer and ReportServerTempDB creation script
[string]$dbscript = $configset.GenerateDatabaseCreationScript("ReportServer", $lcid, $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, "", "")

if( $Version -lt 13)
    {
        $reportServerWebappName = "ReportManager"
    }
else
    {
        $reportServerWebappName = "ReportServerWebApp"
    }
$certhash = $configset.ListSSLCertificates().CertificateHash

if ($certhash -eq $Null)
{
    write-host "ERROR : SSL does not exists on the host"
}

$configset.SetVirtualDirectory("ReportServerWebService", "ReportServer", $lcid)
$configset.ReserveURL("ReportServerWebService", "http://+:80", $lcid)
$configset.CreateSSLCertificateBinding("ReportServerWebService",$certhash,'0.0.0.0',$httpsport,$lcid)
$configset.ReserveURL("ReportServerWebService", "https://$($url):$httpsport", $lcid)


$configset.SetVirtualDirectory($reportServerWebappName, "Reports", $lcid)
$configset.CreateSSLCertificateBinding("ReportServerWebApp",$certhash,'0.0.0.0',$httpsport,$lcid)
$configset.ReserveURL("ReportServerWebApp", "http://+:80", $lcid)
$configset.ReserveURL("ReportServerWebApp", "https://$($url):$httpsport", $lcid)


$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($version)

# 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\$version" `
	-class MSReportServer_Instance -ComputerName localhost
$inst.GetReportServerUrls()

}
`

The scripts works like a charm, but I'm banging my head against the wall when I am trying to adjust some system properties.
Anyone know if it's possible to change the property "AllowedResourceExtensionsForUpload" using this method. Or do I need to invoke the webservice? I can't even find where these settings gets saved. Thanks in advance

For anyone who stumbles upon this question:

See: https://learn.microsoft.com/en-us/troubleshoot/system-center/scom/cannot-deploy-operations-manager-reports#resolution-2

Short version:

$ServiceAddress = "http://localhost"
$Uri = [System.Uri]"$ServiceAddress/ReportServer/ReportService2010.asmx"
$Proxy = New-WebServiceProxy -Uri $Uri -UseDefaultCredential
$Type = $Proxy.GetType().Namespace + ".Property"
$Property = New-Object -TypeName $Type
$Property.Name = "AllowedResourceExtensionsForUpload"
$Property.Value = "*.*"
$Proxy.SetSystemProperties($Property)

Apologies for my stupidity on this. I am attempting to use this script to configure SSRS 2019 as part of a wider VM build process.

When the script is run, it creates the $dbscript script, but fails on "You cannot call a method on a null-valued expression" somewhere inside the generated SQL script (over 17,000 lines long)...

Any ideas where I might start looking?

Thanks!