Last active
May 8, 2024 09:52
-
-
Save SvenAelterman/f2fd058bf3a8aa6f37ac69e5d5dd2511 to your computer and use it in GitHub Desktop.
PowerShell script to configure SQL Server 2017 Reporting Services
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<# | |
#> | |
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() | |
} |
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:
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)
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Thank you . It helped.
I have enhanced script to work for below cases
`$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)
{
}
Allow importing of sqlps module
Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Force
Retrieve the current configuration
$configset = Get-ConfigSet($version)
$configset
If (! $configset.IsInitialized)
{
}
`