The Intransigent Data Blog

This is an attempt to create a script that will configure an instance of SSRS that has been left at the “installed but not configured” stage by the installer.   The script is required to run during the spin-up of a virtual SQL Server machine (e.g. AWS) to result in a fully functioning SSRS site with no further configuration to be done.

(Work in progress)

Versions

• SSRS Native Mode 2017 (v14) Standard edition
• Windows Server 2016 Standard

Actions to be Scripted

• Reserve /Reports and /ReportServer URLs  [done]
• Greate SSRS databases and assign rights [done]
• SMTP Settings
• Specify Execution Account
• Create and Backup Encryption Keys
• Set File Share Account for Subscriptions

Resources

• This is largely derived from the MS article:   Use Powershell to Create an Azure VM with Native Mode Report Server.  However that is written for SSRS 2014 (v12) and the scripting requires more than mere version number alterations to run against v14.
• MSReportServer_ConfigurationSetting Members.  List of properties and methods of the WMI /Admin object for SSRS, giving full definitions of the methods used in the script and many more that aren’t (and that will certainly be required for completing this task).

The Script

Main changes to update from SSRS v12 to v14:

• WMI object namespace is now “root\Microsoft\SqlServer\ReportServer\RS_SSRS\v14\Admin”   (v12 was “…\RS_MSSQLSERVER\v12\Admin”) – reflecting the fact that SSRS as an application is now separate to SQL Server.
• The default service account is now “NT Service\SQL Reporting Services” (instead of “NT Service\ReportServer” for v12).
• The Report Manager (/ReportServer) web application name is “ReportServerWebApp” (formerly “ReportManager”).  This means there are two very similarly, but logically, named applications:
  • ReportServerWebService  the service that emits the actual reports – /ReportServer
  • ReportServerWebApp (formerly ReportManager) the full SSRS user and admin interface – /Reports

## This script configures a Native mode report server without HTTPS
## SSRS Version :  2017 - v14
## Script source: https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sqlclassic/virtual-machines-windows-classic-ps-sql-report
## Amended by Andrew Ing for v14
## Script Version 25-11-2018
 
 
$ErrorActionPreference = "Stop"
 
$server = $env:COMPUTERNAME
$HTTPport = 80 # change the value if you used a different port for the private HTTP endpoint when the VM was created.
 
## Set PowerShell execution policy to be able to run scripts
Set-ExecutionPolicy -Scope CurrentUser RemoteSigned -Force
 
## Utility method for verifying an operation's result
function CheckResult
{
     param($wmi_result, $actionname)
     if ($wmi_result.HRESULT -ne 0) {
         write-error "$actionname failed. Error from WMI: $($wmi_result.Error)"
     }
}
 
$starttime=Get-Date
write-host -foregroundcolor DarkGray $starttime StartTime
 
## ReportServer Database name - this can be changed if needed
$dbName='ReportServer'
 
## Register for MSReportServer_ConfigurationSetting
$RSObject = Get-WmiObject -class "MSReportServer_ConfigurationSetting" -namespace "root\Microsoft\SqlServer\ReportServer\RS_SSRS\v14\Admin"
 
## Report Server Configuration Steps

## Setting the web service URL ##
write-host -foregroundcolor green "Setting the web service URL"
write-host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
$time=Get-Date
write-host -foregroundcolor DarkGray $time
 
## SetVirtualDirectory for ReportServer site
     write-host 'Calling SetVirtualDirectory'
     $r = $RSObject.SetVirtualDirectory('ReportServerWebService','ReportServer',1033)
     CheckResult $r "SetVirtualDirectory for ReportServer"
 
## ReserveURL for ReportServerWebService - port $HTTPport (for local usage)
     write-host "Calling ReserveURL port $HTTPport"
     $r = $RSObject.ReserveURL('ReportServerWebService',"http://+:$HTTPport",1033)
     CheckResult $r "ReserveURL for ReportServer port $HTTPport" 
 
## Setting the Database ##
write-host -foregroundcolor green "Setting the Database"
write-host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
$time=Get-Date
write-host -foregroundcolor DarkGray $time
 
## GenerateDatabaseScript - for creating the database
     write-host "Calling GenerateDatabaseCreationScript for database $dbName"
     $r = $RSObject.GenerateDatabaseCreationScript($dbName,1033,$false)
     CheckResult $r "GenerateDatabaseCreationScript"
     $script = $r.Script
 
## Execute sql script to create the database
     write-host 'Executing Database Creation Script'
     $savedcvd = Get-Location
     Import-Module SQLPS              ## this automatically changes to sqlserver provider
     Invoke-SqlCmd -Query $script
     Set-Location $savedcvd
 
## GenerateGrantRightsScript 
     $DBUser = "NT SERVICE\SQLServerReportingServices"
     write-host "Calling GenerateDatabaseRightsScript with user $DBUser"
     $r = $RSObject.GenerateDatabaseRightsScript($DBUser,$dbName,$false,$true)
     CheckResult $r "GenerateDatabaseRightsScript"
     $script = $r.Script
 
## Execute grant rights script
     write-host 'Executing Database Rights Script'
     $savedcvd = Get-Location
     cd sqlserver:\
     Invoke-SqlCmd -Query $script
     Set-Location $savedcvd
 
## SetDBConnection - uses Windows Service (type 2), username is ignored
     write-host "Calling SetDatabaseConnection server $server, DB $dbName"
     $r = $RSObject.SetDatabaseConnection($server,$dbName,2,'','')
     CheckResult $r "SetDatabaseConnection"  

## Setting the Report Manager URL ##
 
write-host -foregroundcolor green "Setting the Report Manager URL"
write-host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
$time=Get-Date
write-host -foregroundcolor DarkGray $time
 
## SetVirtualDirectory for Reports (Report Manager) site
     write-host 'Calling SetVirtualDirectory'
     $r = $RSObject.SetVirtualDirectory('ReportServerWebApp','Reports',1033)
     CheckResult $r "SetVirtualDirectory"
 
## ReserveURL for ReportManager  - port $HTTPport
     write-host "Calling ReserveURL for ReportServerWebApp, port $HTTPport"
     $r = $RSObject.ReserveURL('ReportServerWebApp',"http://+:$HTTPport",1033)
     CheckResult $r "ReserveURL for ReportManager port $HTTPport"
 
write-host -foregroundcolor green "Open Firewall port for $HTTPport"
write-host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
$time=Get-Date
write-host -foregroundcolor DarkGray $time
 
## Open Firewall port for $HTTPport
     New-NetFirewallRule -DisplayName "Report Server (TCP on port $HTTPport)" -Direction Inbound –Protocol TCP –LocalPort $HTTPport
     write-host "Added rule Report Server (TCP on port $HTTPport) in Windows Firewall"
 
## Restart SSRS service ##
 write-host -foregroundcolor green "Restart SQLServerReportingServices"
 write-host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
 $time=Get-Date
 write-host -foregroundcolor DarkGray $time

Restart-Service SQLServerReportingServices

write-host 'Operations completed, Report Server is ready'
write-host -foregroundcolor DarkGray $starttime StartTime
$time=Get-Date
write-host -foregroundcolor DarkGray $time

Restoring the Unconfigured state

During iterative testing it’s necessary to restore the SSRS instance to the unconfigured state.   This is not possible from the SSRS Configuration Tool.  There are some WMI methods to remove URLs, which I haven’t tried yet.  Here are some manual methods.

• Remove URL Reservations .
  • Open C:\Program Files\Microsoft SQL Server Reporting Services\SSRS\rsreportserver.config  in notepad++, find <URLReservations>  and remove all <URL> nodes  (there will be at least two once configured: for the ReportServerWebService and ReportServerWebApp applications.
  • Run commands –  assuming default names (Use netsh http show urlacl to show all current reservations):
    • netsh http delete urlacl URL=http://+:80/Reports
    • netsh http delete urlacl URL=http://+:80/ReportServer
  • Restart SQLServerReportingServices service.
  • Alternatively use this script, which calls the WMI RemoveURL methods:

    ## UN-CONFIGURE a native instance of SQL Server Reporting Services.
    ##
    ## This is intended for test iterations where SSRS needs to be returned to its freshly-installed state.
    ## SSRS Version :  2017 - v14
    ## Script Version 25-11-2018
    
    
     $ErrorActionPreference = "Stop"
    
     $server = $env:COMPUTERNAME
     $HTTPport = 80 # change to match what was configured.
    
     ## Set PowerShell execution policy to be able to run scripts
     Set-ExecutionPolicy -Scope CurrentUser RemoteSigned -Force
    
     ## Utility method for verifying an operation's result
     function CheckResult
     {
         param($wmi_result, $actionname)
         if ($wmi_result.HRESULT -ne 0) {
             write-error "$actionname failed. Error from WMI: $($wmi_result.Error)"
         }
     }
    
     $starttime=Get-Date
     write-host -foregroundcolor DarkGray $starttime StartTime
    
     ## ReportServer Database name - this can be changed if needed
     $dbName='ReportServer'
    
     ## Register for MSReportServer_ConfigurationSetting
     $RSObject = Get-WmiObject -class "MSReportServer_ConfigurationSetting" -namespace "root\Microsoft\SqlServer\ReportServer\RS_SSRS\v14\Admin"
    
     
     ## Unset Web Service (/Reports) URL ##
     write-host -foregroundcolor green "Unset Web Service (/Reports) URL"
     write-host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
     $time=Get-Date
     write-host -foregroundcolor DarkGray $time
    
    
         write-host "Calling RemoveURL port $HTTPport"
         $r = $RSObject.RemoveURL('ReportServerWebService',"http://+:$HTTPport", 1033)
         CheckResult $r "RemoveURL for ReportServer port $HTTPport" 
    
     ## Unset Web App (/Reportserver) URL ##
     write-host -foregroundcolor green "Unset Web App (/Reportserver) URL"
     write-host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
    
    
        write-host "Calling RemoveURL for ReportServerWebApp, port $HTTPport"
        $r = $RSObject.RemoveURL('ReportServerWebApp',"http://+:$HTTPport",1033)
        CheckResult $r "RemoveURL for ReportManager port $HTTPport"
    
    
     
     ## Restart SSRS service ##
     write-host -foregroundcolor green "Restart SQLServerReportingServices"
     write-host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
     $time=Get-Date
     write-host -foregroundcolor DarkGray $time
    
     Restart-Service SQLServerReportingServices
    
     $time=Get-Date
     write-host -foregroundcolor DarkGray $time

     

• Drop the SSRS databases

  ALTER DATABASE ReportServer SET SINGLE_USER WITH ROLLBACK IMMEDIATE
  ALTER DATABASE ReportServerTempDB SET SINGLE_USER WITH ROLLBACK IMMEDIAT
  USE master
  DROP DATABASE ReportServer
  DROP DATABASE ReportServerTempDB

   

Selected Exception Messages

…you might encounter during testing

• Error from WMI: The Url has already been set for this application. Remove existing URL reservations (see above) first.  
• Error from WMI: The Url has already been reserved.   (as above)
• GenerateDatabaseRightsScript failed. Error from WMI: [blank!]    WMI returned no error information, but at this point the script has already created the databases so is able to execute SQL commands.  This script is assigning rights, therefore it is likely  that the database user specfied in the script ($DBUser)  is not valid – which turned out to be the case in this instance.
• Error from WMI: A virtual directory must first be created before performing this operation.   This occurs if the whole <Application> node is deleted from rsreportserver.config. Only delete the <URL> nodes.  Restore the config file and restart SQLServerReportingServices.

Further Reading

• Powershell ReportingServicesTools (repository: https://github.com/Microsoft/ReportingServicesTools) contains commands for configuring as well as administering SSRS.  See Claudio Silva’s SQL Bits presentation: https://sqlbits.com/Sessions/Event17/Administrating_SSRS_without_boring_web_based_clicks.