Run Powershell Script From Agent As Administrator

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"

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