Single Server Installation and Configuration of Parallels RAS Reporting Service with SQL Server 2019 and Microsoft SSRS 2019 via PowerShell script

## Set PowerShell execution policy to be able to run scripts
Set-ExecutionPolicy -Scope CurrentUser Bypass -Force

###Addind dependencies
Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing

###GUI Form
$form = New-Object System.Windows.Forms.Form
$form.Text = 'Reporting Helper'
$form.Size = New-Object System.Drawing.Size(500,200)
$form.StartPosition = 'CenterScreen'

###GUI Button Yes
$okButton = New-Object System.Windows.Forms.Button
$okButton.Location = New-Object System.Drawing.Point(100,80)
$okButton.Size = New-Object System.Drawing.Size(100,50)
$okButton.Text = 'Yes'
$okButton.DialogResult = [System.Windows.Forms.DialogResult]::Yes
$form.StartPosition = 'CenterScreen'
$form.Controls.Add($okButton)


###GUI Button No
$cancelButton = New-Object System.Windows.Forms.Button
$cancelButton.Location = New-Object System.Drawing.Point(250,80)
$cancelButton.Size = New-Object System.Drawing.Size(100,50)
$cancelButton.Text = 'No'
$cancelButton.DialogResult = [System.Windows.Forms.DialogResult]::No
$form.StartPosition = 'CenterScreen'
$form.Controls.Add($cancelButton)


###GUI text
$label = New-Object System.Windows.Forms.Label
$label.Location = New-Object System.Drawing.Point(50,20)
$label.Size = New-Object System.Drawing.Size(400,100)
$label.Text = "Do you have installers? `n`If you'll press 'Yes' - choose them via file-picker `n`If you'll press 'No' script will download them"
$form.StartPosition = 'CenterScreen'
$form.Controls.Add($label)

###GUI itself to show
$form.Topmost = $true
$result = $form.ShowDialog()


###Minimize this block, its configuration of MSSQL install.
$config = @"
;SQL Server 2019 Configuration File
[OPTIONS]
; By specifying this parameter and accepting Microsoft R Open and Microsoft R Server terms, you acknowledge that you have read and understood the terms of use. 
IACCEPTPYTHONLICENSETERMS="True"
IAcceptSQLServerLicenseTerms="True"
; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter. 
ACTION="Install"
; Detailed help for command line argument ROLE has not been defined yet. 
ROLE="AllFeatures_WithDefaults"
; By specifying this parameter and accepting Microsoft R Open and Microsoft R Server terms, you acknowledge that you have read and understood the terms of use. 
IACCEPTROPENLICENSETERMS="True"
; Specifies that SQL Server Setup should not display the privacy statement when ran from the command line. 
SUPPRESSPRIVACYSTATEMENTNOTICE="True"
; Use the /ENU parameter to install the English version of SQL Server on your localized Windows operating system. 
ENU="True"
; Setup will not display any user interface. 
;QUIET="False"
; Setup will display progress only, without any user interaction. 
QUIETSIMPLE="True"
; Parameter that controls the user interface behavior. Valid values are Normal for the full UI,AutoAdvance for a simplied UI, and EnableUIOnServerCore for bypassing Server Core setup GUI block. 
;UIMODE="Normal"
; Specify whether SQL Server Setup should discover and include product updates. The valid values are True and False or 1 and 0. By default SQL Server Setup will include updates that are found. 
UpdateEnabled="False"
; If this parameter is provided, then this computer will use Microsoft Update to check for updates. 
USEMICROSOFTUPDATE="False"
; Specifies that SQL Server Setup should not display the paid edition notice when ran from the command line. 
SUPPRESSPAIDEDITIONNOTICE="True"
; Specify the location where SQL Server Setup will obtain product updates. The valid values are "MU" to search Microsoft Update, a valid folder path, a relative path such as .\MyUpdates or a UNC share. By default SQL Server Setup will search Microsoft Update or a Windows Update service through the Window Server Update Services. 
UpdateSource="MU"
; Specifies features to install, uninstall, or upgrade. The list of top-level features include SQL, AS, IS, MDS, and Tools. The SQL feature will install the Database Engine, Replication, Full-Text, and Data Quality Services (DQS) server. The Tools feature will install shared components. 
FEATURES=SQLENGINE
; Displays the command line parameters usage 
HELP="False"
; Specifies that the detailed Setup log should be piped to the console. 
INDICATEPROGRESS="True"
; Specifies that Setup should install into WOW64. This command line argument is not supported on an IA64 or a 32-bit system. 
X86="False"
; Specify a default or named instance. MSSQLSERVER is the default instance for non-Express editions and SQLExpress for Express editions. This parameter is required when installing the SQL Server Database Engine (SQL), or Analysis Services (AS). 
INSTANCENAME="SQLEXPRESS"
; Specify the root installation directory for shared components.  This directory remains unchanged after shared components are already installed. 
INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"
; Specify the root installation directory for the WOW64 shared components.  This directory remains unchanged after WOW64 shared components are already installed. 
INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server"
; Specify the Instance ID for the SQL Server features you have specified. SQL Server directory structure, registry structure, and service names will incorporate the instance ID of the SQL Server instance. 
INSTANCEID="SQLEXPRESS"
; TelemetryUserNameConfigDescription 
SQLTELSVCACCT="NT AUTHORITY\LOCAL SERVICE"
; TelemetryStartupConfigDescription 
SQLTELSVCSTARTUPTYPE="Automatic"
; Specify the installation directory. 
INSTANCEDIR="C:\Program Files\Microsoft SQL Server"
; Agent account name 
AGTSVCACCOUNT="NT AUTHORITY\NETWORK SERVICE"
; Auto-start service after installation.  
AGTSVCSTARTUPTYPE="Disabled"
; CM brick TCP communication port 
COMMFABRICPORT="0"
; How matrix will use private networks 
COMMFABRICNETWORKLEVEL="0"
; How inter brick communication will be protected 
COMMFABRICENCRYPTION="0"
; TCP port used by the CM brick 
MATRIXCMBRICKCOMMPORT="0"
; Startup type for the SQL Server service. 
SQLSVCSTARTUPTYPE="Automatic"
; Level to enable FILESTREAM feature at (0, 1, 2 or 3). 
FILESTREAMLEVEL="0"
; The max degree of parallelism (MAXDOP) server configuration option. 
SQLMAXDOP="0"
; Set to "1" to enable RANU for SQL Server Express. 
ENABLERANU="True"
; Specifies a Windows collation or an SQL collation to use for the Database Engine. 
SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"
; Account for SQL Server service: Domain\User or system account. 
SQLSVCACCOUNT="NT AUTHORITY\LOCAL SERVICE"
; Set to "True" to enable instant file initialization for SQL Server service. If enabled, Setup will grant Perform Volume Maintenance Task privilege to the Database Engine Service SID. This may lead to information disclosure as it could allow deleted content to be accessed by an unauthorized principal. 
SQLSVCINSTANTFILEINIT="False"
; Windows account(s) to provision as SQL Server system administrators. 
SQLSYSADMINACCOUNTS="ras\admin" "ras\rasreportingview" "NT AUTHORITY\SYSTEM"
; The number of Database Engine TempDB files. 
SQLTEMPDBFILECOUNT="1"
; Specifies the initial size of a Database Engine TempDB data file in MB. 
SQLTEMPDBFILESIZE="8"
; Specifies the automatic growth increment of each Database Engine TempDB data file in MB. 
SQLTEMPDBFILEGROWTH="64"
; Specifies the initial size of the Database Engine TempDB log file in MB. 
SQLTEMPDBLOGFILESIZE="8"
; Specifies the automatic growth increment of the Database Engine TempDB log file in MB. 
SQLTEMPDBLOGFILEGROWTH="64"
; Provision current user as a Database Engine system administrator for %SQL_PRODUCT_SHORT_NAME% Express. 
ADDCURRENTUSERASSQLADMIN="True"
; Specify 0 to disable or 1 to enable the TCP/IP protocol. 
TCPENABLED="1"
; Specify 0 to disable or 1 to enable the Named Pipes protocol. 
NPENABLED="0"
; Startup type for Browser Service. 
BROWSERSVCSTARTUPTYPE="Automatic"
; Use SQLMAXMEMORY to minimize the risk of the OS experiencing detrimental memory pressure. 
SQLMAXMEMORY="2147483647"
; Use SQLMINMEMORY to reserve a minimum amount of memory available to the SQL Server Memory Manager. 
SQLMINMEMORY="0"
"@


###Function forest

function Select-FileSQL
{
  $dialog = [System.Windows.Forms.OpenFileDialog]::new()
  $dialog.Title = "Choose SQLEXPR_x64_ENU.exe"
  $dialog.InitialDirectory = [Environment]::GetFolderPath('Desktop')
  $dialog.Filter = 'SQLEXPR_x64_ENU.exe|SQLEXPR_x64_ENU.exe'
  $result = $dialog.ShowDialog()

  if($result -eq [System.Windows.Forms.DialogResult]::OK){
    return $dialog.FileName
  }
}

function Select-FileSSRS
{
  $dialog = [System.Windows.Forms.OpenFileDialog]::new()
  $dialog.Title = "Choose SQLServerReportingServices.exe"
  $dialog.InitialDirectory = [Environment]::GetFolderPath('Desktop')
  $dialog.Filter = 'SQLServerReportingServices.exe|SQLServerReportingServices.exe'
  $result = $dialog.ShowDialog()

  if($result -eq [System.Windows.Forms.DialogResult]::OK){
    return $dialog.FileName
  }
}

function Install-RASQL
{
$ErrorActionPreference = "Stop"
cd $env:TEMP
Start-Process .\SQLEXPR_x64_ENU.exe -ArgumentList "/x:$env:TEMP\sql /q" -Wait
$config | Out-File "$env:TEMP\sql\config.ini"
cd $env:TEMP\sql
Start-Process .\Setup.exe -ArgumentList "/ConfigurationFile=config.ini" -Wait
}


function Setup-RASQL
{
$ErrorActionPreference = "Stop"
$instance = "SQLEXPRESS"
$port = "1433"
$settings = Get-WmiObject `
        -Namespace root/Microsoft/SqlServer/ComputerManagement15 `
        -Class ServerNetworkProtocolProperty `
        -Filter "InstanceName='$instance' and IPAddressName='IPAll' and PropertyType=1 and ProtocolName='Tcp'"
foreach ($setting in $settings)
{
        if ($setting -ne $null)
        {
            # set the static TCP port and at the same time clear any dynamic ports
            if ($setting.PropertyName -eq "TcpPort")
            {
                $setting.SetStringValue($port)
            }
            elseif ($setting.PropertyName -eq "TcpDynamicPorts")
            {
                $setting.SetStringValue("")
            }
        }
        }
Restart-Service -Name "SQLBrowser"
Restart-Service -Name 'MSSQL$SQLEXPRESS'
}


function Install-RASRS
{
$ErrorActionPreference = "Stop"
cd $env:TEMP
Start-Process .\SQLServerReportingServices.exe -ArgumentList "/quiet /norestart /IAcceptLicenseTerms /Edition=Expr" -Wait
}

$SetupRASRS = @'
$ErrorActionPreference = "Stop"
$server = $env:COMPUTERNAME
$HTTPport = 8085
## 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
$dbName='ReportServer'

##WMI Register for MSReportServer_ConfigurationSetting
$RSObject = Get-WmiObject -class "MSReportServer_ConfigurationSetting" -namespace "root\Microsoft\SqlServer\ReportServer\RS_SSRS\v15\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_SQLEXPRESS',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 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_SQLEXPRESS',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"


## 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"  


## 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
Unregister-ScheduledTask -TaskName "Configuration" -Confirm:$false
'@
$SetupRASRS | Out-File "C:\Program Files\WindowsPowerShell\SetupRASRS.ps1"
function Sch-Task
{
$schAction = New-ScheduledTaskAction -Execute "Powershell.exe" -Argument '-NoProfile -WindowStyle Hidden -File "C:\Program Files\WindowsPowerShell\SetupRASRS.ps1"'
$schTrigger = New-ScheduledTaskTrigger -AtLogOn
$schPrincipal = New-ScheduledTaskPrincipal -UserId "$env:USERNAME" -LogonType ServiceAccount -RunLevel Highest
Register-ScheduledTask -Action $schAction -Trigger $schTrigger -TaskName "Configuration" -Description "Scheduled Task to run configuration Script At Startup" -Principal $schPrincipal
}

if ($result -eq [System.Windows.Forms.DialogResult]::Yes)
{
$ErrorActionPreference = "Stop"
$pathSQL = Select-FileSQL
$pathSSRS = Select-FileSSRS

Write-Host -foregroundcolor green "Copying SQL"
Write-Host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
$time=Get-Date
Write-Host -foregroundcolor DarkGray $time

Start-BitsTransfer -Source $pathSQL -Destination $env:TEMP -TransferType Download


Write-Host -foregroundcolor green "Copying SSRS"
Write-Host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
$time=Get-Date
Write-Host -foregroundcolor DarkGray $time

Start-BitsTransfer -Source $pathSSRS -Destination $env:TEMP -TransferType Download

Write-Host -foregroundcolor green "Installing SQL"
Write-Host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
$time=Get-Date
Write-Host -foregroundcolor DarkGray $time

Install-RASQL

Write-Host -foregroundcolor green "Configuring SQL"
Write-Host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
$time=Get-Date
Write-Host -foregroundcolor DarkGray $time

Setup-RASQL

Write-Host -foregroundcolor green "Installing SSRS"
Write-Host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
$time=Get-Date
Write-Host -foregroundcolor DarkGray $time

Install-RASRS

Write-Host -foregroundcolor green "Configuring SSRS via Scheduled task"
Write-Host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
$time=Get-Date
Write-Host -foregroundcolor DarkGray $time

Sch-Task

Write-Host -foregroundcolor green "Process Complete. Please reboot your PC"
Write-Host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
$time=Get-Date
Write-Host -foregroundcolor DarkGray $time
}


if ($result -eq [System.Windows.Forms.DialogResult]::No)
{
$ErrorActionPreference = "Stop"
Write-Host -foregroundcolor green "Downloading SQL"
Write-Host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
$time=Get-Date
Write-Host -foregroundcolor DarkGray $time

$InstallerSQL = $env:TEMP + "\SQLEXPR_x64_ENU.exe"
Start-BitsTransfer "https://download.microsoft.com/download/7/c/1/7c14e92e-bdcb-4f89-b7cf-93543e7112d1/SQLEXPR_x64_ENU.exe" $InstallerSQL


Write-Host -foregroundcolor green "Downloading SSRS"
Write-Host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
$time=Get-Date
Write-Host -foregroundcolor DarkGray $time

$InstallerSSRS = $env:TEMP + "\SQLServerReportingServices.exe"
Start-BitsTransfer "https://download.microsoft.com/download/1/a/a/1aaa9177-3578-4931-b8f3-373b24f63342/SQLServerReportingServices.exe" $InstallerSSRS

Write-Host -foregroundcolor green "Installing SQL"
Write-Host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
$time=Get-Date
Write-Host -foregroundcolor DarkGray $time

Install-RASQL

Write-Host -foregroundcolor green "Configuring SQL"
Write-Host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
$time=Get-Date
Write-Host -foregroundcolor DarkGray $time

Setup-RASQL

Write-Host -foregroundcolor green "Installing SSRS"
Write-Host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
$time=Get-Date
Write-Host -foregroundcolor DarkGray $time

Install-RASRS

Write-Host -foregroundcolor green "Configuring SSRS via Scheduled task"
Write-Host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
$time=Get-Date
Write-Host -foregroundcolor DarkGray $time

Sch-Task

Write-Host -foregroundcolor green "Process Complete. Please reboot your PC"
Write-Host -foregroundcolor green ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"
$time=Get-Date
Write-Host -foregroundcolor DarkGray $time
}