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

Thanks, that helped a lot! It is not part of AutomatedLab.

Very nice way of invoking the steps that the GUI would run in an automated environment.
I combine this with installing SSRS from chocolatey in my testing of a software that incorporates SSRS reports.

Has anyone added to this to configure the WindowsServiceIdentity property? We need to change it from the default to a domain account but I haven't been able to get that to work properly in PowerShell.

Has anyone added to this to configure the WindowsServiceIdentity property? We need to change it from the default to a domain account but I haven't been able to get that to work properly in PowerShell.

@redknot I have a process where I have to change the SSRS service to use domain account. Below is the PowerShell snippet but I'd imagine you would be able to adapt it during provisioning. I use this in ansible so you can ignore the curly brackets. Also, I found that I had to give the domain account SeServiceLogonRight and SeImpersonatePrivilege win user rights beforehand.

$ns = "root\Microsoft\SqlServer\ReportServer\RS_SSRS\v14\Admin"
$RSObject = Get-WmiObject -class "MSReportServer_ConfigurationSetting" -namespace "$ns"
# Set service account
$serviceAccount = "{{ ssrs_srvc_account }}"
$servicePW = "{{ ssrs_srvc_password }}"
$useBuiltInServiceAccount = $false
$RSObject.SetWindowsServiceIdentity($useBuiltInServiceAccount, $serviceAccount, $servicePW) | out-null

# Need to reset the URLs for domain service account to work
$HTTPport = 80
$RSObject.RemoveURL("ReportServerWebService", "http://+:$HTTPport", 1033) | out-null
$RSObject.RemoveURL("ReportServerWebApp", "http://+:$HTTPport", 1033) | out-null
$RSObject.SetVirtualDirectory("ReportServerWebService", "ReportServer", 1033) | out-null
$RSObject.SetVirtualDirectory("ReportServerWebApp", "Reports", 1033) | out-null
$RSObject.ReserveURL("ReportServerWebService", "http://+:$HTTPport", 1033) | out-null
$RSObject.ReserveURL("ReportServerWebApp", "http://+:$HTTPport", 1033) | out-null

# Restart SSRS service for changes to take effect
$serviceName = $RSObject.ServiceName
Restart-Service -Name $serviceName -Force

Thanks! This works for me.

Thanks! I was looking to expand on this by binding a certificate (Already imported into the server) and reserving the virtual directories on port 443. Has anyone done this?

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

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

There is a very good chance that will need to be done via the web service.

Webserver and service URL is not updating .where I need to pass the instance name below?
$RSObject.SetVirtualDirectory("ReportServerWebService", "ReportServer", 1033) | out-null
$RSObject.SetVirtualDirectory("ReportServerWebApp", "Reports", 1033) | out-null
$RSObject.ReserveURL("ReportServerWebService", "http://+:$HTTPport", 1033) | out-null
$RSObject.ReserveURL("ReportServerWebApp", "http://+:$HTTPport", 1033) | out-null

Error : The Url has already been set for this application.
HRESULT : -2147220930
PSComputerName :

__GENUS : 2
__CLASS : __PARAMETERS
__SUPERCLASS :
__DYNASTY : __PARAMETERS
__RELPATH :
__PROPERTY_COUNT : 2
__DERIVATION : {}
__SERVER :
__NAMESPACE :
__PATH :
Error : The Url has already been reserved.
HRESULT : -2147220932
PSComputerName :

I want to modify the servername

Thanks Sven this saved me hours of work. I'm in the process of updating it to use SqlServer module but haven't been able to test it yet due to https://www.powershellgallery.com/ being down.

Hey Sven, here's my updated script that uses the SqlServer module.
https://github.com/mrsquish/AutomationScripts/blob/main/ConfigureSSRS.ps1

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!