Automating configuration of SQL Server 2017 Reporting Services

Question

I am trying to silently install and configure SQL Server 2017 Reporting Services. The silent install is straightforward, and I have most of the configuration done, using the PowerShell script below.

Where I run into an issue is when attempting to set the virtual directory for the Report Manager. I receive an error on the following line

$configset.SetVirtualDirectory("ReportManager", "Reports", 1033)

HRESULT -2147220938: The application is not found.

Per How to automate SSRS install and configuration, it appears I am performing the steps in the correct order.

$configset = Get-WmiObject –namespace "root\Microsoft\SqlServer\ReportServer\RS_SSRS\v14\Admin" `
    -class MSReportServer_ConfigurationSetting -ComputerName localhost

$configset

If (! $configset.IsInitialized)
{
    [string]$dbscript = $configset.GenerateDatabaseCreationScript("ReportServer", 1033, $false).Script

    Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Force
    Import-Module sqlps -DisableNameChecking | Out-Null

    $conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection -ArgumentList $env:ComputerName
    $conn.ApplicationName = "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)

    $configset.SetVirtualDirectory("ReportManager", "Reports", 1033)
    $configset.ReserveURL("ReportManager", "http://+:80", 1033)

    $configset.InitializeReportServer($configset.InstallationID)

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

Any insights into this issue are appreciated!

Answer 1

The issue was the with SQL Server 2016 (and later), the name of the web application for the report manager has changed from ReportManager to ReportServerWebApp.

Answer 2

You could use DSC this link should have information on how to use the dsc resources:

https://learn.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-on-server-core

When this code was Xrev I was able to install and configure SSRS using a resource at that time called MSFT_xSQLServerSetup.

Here is an example of setting up a single sql server. https://github.com/PowerShell/SqlServerDsc/blob/master/Examples/SQL-Standalone.ps1