How to run a scheduled task to stop and start SSRS service with elevated permissions?

Question

I have this SSRS latency issues on my site. So I have googled it and found out that it is the common issues for so many people. Here it is:

1. I have created a powershell script as follows:

   Stop-Service "SQL Server Reporting Services (MSSQLSERVER)"
   Start-Service "SQL Server Reporting Services (MSSQLSERVER)"
   $wc = New-Object system.net.webClient
   $cred = [System.Net.CredentialCache]::DefaultNetworkCredentials
   $wc.Credentials = $cred
   $src = $wc.DownloadString("http://example.com/Reports/Pages/Folder.aspx")
   

2. When i run this script from poweshell cmd it is throwing me an error says cannot open/access sql report server service. It seems like permissions issue. Then I came with this online solution, which invokes/elevates admin permissions to run the script to that perticular user.

   function Invoke-Admin() {
   
       param ( [string]$program = $(throw "Please specify a program" ),
           [string]$argumentString = "",
           [switch]$waitForExit )
   
       $psi = new-object "Diagnostics.ProcessStartInfo"
       $psi.FileName = $program 
       $psi.Arguments = $argumentString
       $psi.Verb = "runas"
       $proc = [Diagnostics.Process]::Start($psi)
       if ( $waitForExit ) {
           $proc.WaitForExit();
       }
   }
   

But I dont know how to run this function before running that script. Please suggest. I have added this function also to the same script file and added function-Admin() call at the top of the script to to execute this function before running the script as follows:

function-Admin()
 Stop-Service "SQL Server Reporting Services (MSSQLSERVER)"
    Start-Service "SQL Server Reporting Services (MSSQLSERVER)"
    $wc = New-Object system.net.webClient
    $cred = [System.Net.CredentialCache]::DefaultNetworkCredentials
    $wc.Credentials = $cred
    $src = $wc.DownloadString("http://example.com/Reports/Pages/Folder.aspx")

But is throwing following error:

Please specify a program
At C:\SSRS_Script\SSRSScript.ps1:3 char:39
+     param ( [string]$program = $(throw <<<<  "Please specify a program" ),
    + CategoryInfo          : OperationStopped: (Please specify a program:String) [], RuntimeException
    + FullyQualifiedErrorId : Please specify a program

Answer 1

You are getting that error because the function Invoke-Admin() was designed to have parameters passed for the program you wanted to run with elevated privledges. If you want your powershell script SSRSScript.ps1 to use this Invoke-Admin() you could convert it to a standalone script.

Take the code without the function declartion and outer brackets. Save this a file called Invoke-Admin.ps1

param ( [string]$program = $(throw "Please specify a program" ),
    [string]$argumentString = "",
    [switch]$waitForExit )

$psi = new-object "Diagnostics.ProcessStartInfo"
$psi.FileName = $program 
$psi.Arguments = $argumentString
$psi.Verb = "runas"
$proc = [Diagnostics.Process]::Start($psi)
if ( $waitForExit ) {
    $proc.WaitForExit();
}

With that created then you could try to elevate your script with the following:

C:\*pathtoscript*\Invoke-Admin.ps1 -program "Powershell.exe" -argumentString "-file C:\SSRS_Script\SSRSScript.ps1"

You should get the elevation prompt at that point and then, once accepted, will run another window with your script using admin rights.

This is by no means the only way to accomplish this goal.

Scheduler

You have this in the title but dont really cover it in the question. Running this as a scheduled task will not work since it requires user input. You could however just make a task with your script as is assuming it works unattended.

General Tab

Run whether user is logged on or not
Run with highest privileges

Action > New...

Action: Start a program Program/script: %SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe 
Add arguments: -ExecutionPolicy Unrestricted -NoProfile -File C:\SSRS_Script\SSRSScript.ps1
Start in (optional): %SystemRoot%\system32\WindowsPowerShell\v1.0