Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
PowerShell script to configure SQL Server 2017 Reporting Services
<#
#>
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()
}
@raandree

This comment has been minimized.

Copy link

@raandree raandree commented Sep 26, 2018

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

@QuadmanSWE

This comment has been minimized.

Copy link

@QuadmanSWE QuadmanSWE commented Apr 15, 2019

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.

@thefamousbrian

This comment has been minimized.

Copy link

@thefamousbrian thefamousbrian commented Jun 6, 2019

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.

@ggsince88

This comment has been minimized.

Copy link

@ggsince88 ggsince88 commented Jul 11, 2019

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
@ghost

This comment has been minimized.

Copy link

@ghost ghost commented Jul 24, 2019

Thanks! This works for me.

@jsn2020

This comment has been minimized.

Copy link

@jsn2020 jsn2020 commented Feb 29, 2020

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?

@lenntun

This comment has been minimized.

Copy link

@lenntun lenntun commented Sep 10, 2020

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

@SvenAelterman

This comment has been minimized.

Copy link
Owner Author

@SvenAelterman SvenAelterman commented Sep 11, 2020

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.