How to automate SSRS install and configuration

Question

This is my scenario: I have to setup a number of TFS 2013 Application Tier nodes (at least 6) and I am trying to automate the process, the nodes hosts also SQL Server Reporting Services 2012 SP1 in "cluster" (better group) configuration. I have no problem in installing SQL, but I am stuck with Reporting Services configuration.

The documentation states to use WMI to configure URLs, and the MSReportServer_ConfigurationSetting class has a SetVirtualDirectory which seems appropriate. I see that the value changes in RS Configuration Manager, but the message

Report Server Web Service is not configured.

do not disappear.

My Powershell code is

$wmiName = (Get-WmiObject –namespace root\Microsoft\SqlServer\ReportServer  –class __Namespace).Name
$rsConfig = Get-WmiObject –namespace "root\Microsoft\SqlServer\ReportServer\$wmiName\v11\Admin" -class MSReportServer_ConfigurationSetting  -filter "InstanceName='SQLTFS'"

CheckHResult $rsConfig.SetVirtualDirectory("ReportServerWebService","ReportServer",0)
CheckHResult $rsConfig.SetVirtualDirectory("ReportManager","Reports",0)
CheckHResult $rsConfig.SetDatabaseConnection($DatabaseInstance, $DatabaseName, 2, $SqlCredential.UserName, $SqlCredential.GetNetworkCredential().Password)
# force refresh
CheckHResult $rsConfig.SetServiceState($false,$false,$false)
Restart-Service $rsConfig.ServiceName
CheckHResult $rsConfig.SetServiceState($true,$true,$true)
# set key
& "$SQLBin\RSKeyMgmt.exe" -a -i SQLTFS -f $SSRSEncryptKeyFile -p $SSRSEncryptPassword
# join group
& "$SQLBin\RSKeyMgmt.exe" -j -m $workingNode -i SQLTFS -n SQLTFS

Answer 1

Found the culprit: need to call ReserveURL and use the http://+:80 syntax for the URL. Calling this API restart the services so it could take a while to respond.

New version of script is

$wmiName = (Get-WmiObject –namespace root\Microsoft\SqlServer\ReportServer  –class __Namespace).Name

$rsConfig = Get-WmiObject –namespace "root\Microsoft\SqlServer\ReportServer\$wmiName\v11\Admin" -class MSReportServer_ConfigurationSetting -filter "InstanceName='SQLTFS'"
CheckHResult $rsConfig.SetDatabaseConnection($DatabaseInstance, $DatabaseName, 2, $SqlCredential.UserName, $SqlCredential.GetNetworkCredential().Password)

CheckHResult $rsConfig.SetVirtualDirectory("ReportServerWebService","ReportServer",$lcid)
CheckHResult $rsConfig.ReserveURL("ReportServerWebService","http://+:80",$lcid)
CheckHResult $rsConfig.SetVirtualDirectory("ReportManager","Reports",$lcid)
CheckHResult $rsConfig.ReserveURL("ReportManager","http://+:80",$lcid)

Start-Sleep -Seconds 15
# set key
& "$SQLBin\RSKeyMgmt.exe" -a -i SQLTFS -f $SSRSEncryptKeyFile -p $SSRSEncryptPassword
Start-Sleep -Seconds 15
# join group
& "$SQLBin\RSKeyMgmt.exe" -j -m $workingNode -i SQLTFS -n SQLTFS
Start-Sleep -Seconds 15

CheckHResult $rsConfig.SetServiceState($true,$true,$true)

Answer 2

I work in a datacenter and prior to discovering this method, I used an autoit program to configure the SSRS instance.

The thing I would like to add to this solution is configuring SSL and it can be done as follows

$certhash = Get-ChildItem -path cert:\localmachine\my | Where {$_.subject -match <yourdomain> } | select -ExpandProperty thumbprint
$certhash = $certhash.tolower() #important
$sslPort = 443
$sslUrl = "https://+:443/"
$lcid = 1033 # for english


$rsConfig.ReserveURL("ReportServerWebService", $sslUrl, $lcid)
$rsConfig.CreateSSLCertificateBinding("ReportServerWebService", $certHash, "0.0.0.0", $sslPort, $lcid)

$rsConfig.ReserveURL("ReportManager", $sslUrl, $lcid)
$rsConfig.CreateSSLCertificateBinding("ReportManager", $certHash, "0.0.0.0", $sslPort, $lcid)

# Stop and Start SQL Server's Reporting Services to ensure changes take affect
$rsconfig.SetServiceState($false, $false, $false)
$rsconfig.SetServiceState($true, $true, $true)

Some other very useful resources are

http://babercode.blogspot.com/2012/03/powershell-report-server-integration_21.html

https://ruiromanoblog.wordpress.com/2010/05/08/configure-reporting-services-ssl-binding-with-wmi-powershell/