Run Powershell Script From Agent As Administrator

Question

I need to recycle an AppPool in IIS on my server when the SQL Server Service starts.

The route I am taking is to have a startup stored procedure which runs an Agent job that has a Powershell job step.

I have created / grabbed from the Internet a Powershell script which recycles the app pool:

# Load IIS module:
Import-Module WebAdministration
# Set a name of the site we want to recycle the pool for:
$site = "Default Web Site"
# Get pool name by the site name:
$pool = (Get-Item "IIS:\Sites\$site"| Select-Object applicationPool).applicationPool
# Recycle the application pool:
Restart-WebAppPool $pool

This works at the OS level but only when Powershell is run as an administrator (Even though the account I am logged into Windows as is in the Admin group)

Sure enough, if I create the job in agent with a Powershell step containing the above code, upon execution I get the error

A job step received an error at line 6 in a PowerShell script. The corresponding line is '$pool = (Get-Item "IIS:\Sites\$site"| Select-Object applicationPool).applicationPool '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot retrieve the dynamic parameters for the cmdlet. Filename: redirection.config Error: Cannot read configuration file due to insufficient permissions '. Process Exit Code -1. The step failed.

So I need a way to "Run as Administrator" from SQL Server Agent

I had success at the OS level creating a new script which runs the original script with elevated permissions:

If (-NOT ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole]::Administrator))
{
  # Relaunch as an elevated process:
  Start-Process powershell.exe "-File",('"{0}"' -f $MyInvocation.MyCommand.Path) -Verb RunAs
  exit
}
# Now running elevated so launch the script:
& "d:\path\to\my\script.ps1"

which allows me to run the new script from either a non elevated CMD prompt or non elevated Powershell prompt. However, when i try to run from agent as either a Powershell step or CMD step I get

The machine-default permission settings do not grant Local Activation permission for the COM Server application with CLSID {longguidhere} and APPID {longguidhere} to the user NT SERVICE\SQLSERVERAGENT SID (longguidhere) from address LocalHost (Using LRPC) running in the application container Unavailable SID (Unavailable). This security permission can be modified using the Component Services administrative tool.

I have also tried

Start-Process powershell -Verb runAs -FilePath "d:\path\to\my\script.ps1"

which again, at the OS level worked but when run from Agent gives me the same error.

How can I get either

a: recycle the app pool without requiring elevated priveleges

or

b: run the script to recycle the app pool with elevated priveleges from SQL Server Agent?

Answer 1

Create a PowerShell proxy in SQL Server agent that uses a privileged Windows account via a SQL Server credential.

CREATE CREDENTIAL docs.

How to create a proxy

That will allow you to run PowerShell as an administrator, directly from SQL Server.

Creating a credential is the first step, and consists of running a T-SQL statement like this:

CREATE CREDENTIAL MyPowerShellCredential   
WITH IDENTITY = 'DOMAIN\AdministratorAccount'  
    , SECRET = 'AdministratorAccountPassword'  

Once the credential is created, you need to create a SQL Server PowerShell Proxy. This should work:

USE msdb;
EXEC dbo.sp_add_proxy @proxy_name = N'MyPowerShellProxy'
    , @enabled=1
    , @description = N'Proxy used to run PowerShell as an administrator'
    , @credential_name = N'MyPowerShellCredential';

After creating the proxy, you need to enable it for use by the PowerShell subsystem. This code does that:

USE msdb;
EXEC dbo.sp_grant_proxy_to_subsystem @proxy_name = N'MyPowerShellProxy'  
     , @subsystem_name = N'PowerShell'

Once you have the proxy created, you'll need a SQL Server Agent Jobstep configured to run PowerShell, which will automatically make use of the PowerShell proxy.

This is a sample SQL Server Agent Job that will run your code:

USE [msdb]
GO
DECLARE @jobId binary(16);

EXEC  msdb.dbo.sp_add_job @job_name = N'ResetAppPool'
        , @enabled = 1
        , @notify_level_eventlog = 0
        , @notify_level_email = 2
        , @notify_level_page = 2
        , @delete_level = 0
        , @category_name = N'[Uncategorized (Local)]'
        , @owner_login_name = N'sa'
        , @job_id = @jobId OUTPUT;

EXEC msdb.dbo.sp_add_jobserver @job_name = N'ResetAppPool'
    , @server_name = N'(LOCAL)';

EXEC msdb.dbo.sp_add_jobstep @job_name = N'ResetAppPool'
    , @step_name = N'Step1'
    , @step_id = 1
    , @cmdexec_success_code = 0
    , @on_success_action = 1
    , @on_fail_action = 2
    , @retry_attempts = 0
    , @retry_interval = 0
    , @os_run_priority = 0
    , @subsystem = N'PowerShell'
    , @command = N'# Load IIS module:
Import-Module WebAdministration
# Set a name of the site we want to recycle the pool for:
$site = "Default Web Site"
# Get pool name by the site name:
$pool = (Get-Item "IIS:\Sites\$site"| Select-Object applicationPool).applicationPool
# Recycle the application pool:
Restart-WebAppPool $pool'
    , @database_name = N'master'
    , @flags = 0;

EXEC msdb.dbo.sp_update_job @job_name = N'ResetAppPool'
    , @enabled = 1
    , @start_step_id = 1
    , @notify_level_eventlog = 0
    , @notify_level_email = 2
    , @notify_level_page = 2
    , @delete_level = 0
    , @description = N''
    , @category_name = N'[Uncategorized (Local)]'
    , @owner_login_name = N'sa'
    , @notify_email_operator_name = N''
    , @notify_page_operator_name = N''
GO

You could modify the job to send an email to you when it runs, just so you know it worked.

Regarding this error you're seeing:

The machine-default permission settings do not grant Local Activation permission for the COM Server application with CLSID {longguidhere} and APPID {longguidhere} to the user NT SERVICE\SQLSERVERAGENT SID (longguidhere) from address LocalHost (Using LRPC) running in the application container Unavailable SID (Unavailable). This security permission can be modified using the Component Services administrative tool.

That indicates User Account Control is running in Admin Approval Mode, requiring every action that requires elevation to ask the user for consent. Since SQL Server Agent is running as a service, there is no chance for the elevation prompt to run, hence the failure. You can assign the NT SERVICE\SQLSERVERAGENT account rights to locally activate PowerShell in elevated mode via the Component Services applet in Control Panel without requiring the prompt.

Alternately, you can configure Windows so it never requires elevation for members of the local Administrators group. Do that via the Local Security Policy applet. Set the following settings:

1. Open the Security Settings\Local Policies\Security Options folder.
2. Set "User Account Control: Admin Approval Mode for the Built-in Administrator Account" to "Disabled"
3. Set "User Account Control: Behavior of the elevation prompt for administrators in Admin Approval Mode" to "Elevate without Prompting"
4. Set "User Account Control: Run all administrators in Admin Approval Mode" to "Disabled"

Answer 2

It was a bit of a long winded work around but I managed to do this as follows:

• Create a scheduled task in Windows Task scheduler to run the Powershell script file which recycles the app pool. Ensure it is marked as "Run with the highest priveleges"
• Create a credential with an administrators credentials
• Create a proxy account and associate with CMD subsystem
• Create a CMD agent job which runs the scheduled task (ensure the job step runs as the proxy account)
• Create a startup procedure which executes the job. I had to add a while loop which checks if the agent is running and if not, waits for 5 seconds and try again to start the job