Sql-server – Run Powershell Script From Agent As Administrator

powershellsql server 2014windows-server

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

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

Best Answer

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:

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

Related Solutions

Sql-server – Powershell-type SQL Agent Job failing intermittently trying to get Disk Space on Server via WMI-query

Don't know if you're still looking for a solution to this but I'd change the Powershell for the insert command just a little. Add this after the line setting $size = $_.Size

   $sql = "INSERT dbo.DiskSpace (drive, [free(bytes)], [total(bytes)]) VALUES ('$Name', $FreeSpace, $Size)"
   $sql | Out-File "C:\tmp\debug.sql" –Append # to wherever is appropriate for your system
   $cmd.CommandText = $sql

To me using the extra variable but getting an easier to read insert command is a good tradeoff and the debug.sql file makes finding sql errors much easier. HTH

Sql-server – SQL Agent – PowerShell step “syntax error”

This is a not very intuitive and I was never able to find anything concrete on the explanation [e.g. no exact BOL or white paper was found].

The syntax error in the SQL Agent job is a T-SQL syntax error based on User Tokens. So that basically means that a PowerShell Sub-Expression Operator is treated as a Token to SQL Server Agent. So in PowerShell this $( ) appears to be treated as reserved character sequence for SQL Server Agent. So SQL Agent would be looking for something like this T-SQL example from the BOL article referenced: PRINT N'Current database name is $(A-DBN)' ; .

So in my script when it reached ,@DriveLetter = '$($c.DriveLetter)', the "$c.DriveLetter" is not one of the tokens allowed.

The workaround to this is simply to not use sub-expression statements in PowerShell. I would make the adjustments in my script so this:


$qAddServerDiskSpace = @"
EXEC [dbo].[StoredProcInsert]
    @ServerName = '$($c.ServerName)'
    ,@DriveLetter = '$($c.DriveLetter)'
    ,@DiskSpaceCapacityGB = $($c.DiskSpaceCapacityGB)
    ,@DiskFreeSpaceGB = $($c.DiskFreeSpaceGB)
"@

would have to be modified to something like this:


$severName = $c.ServerName
$driveLetter = $c.DriveLetter
$capacityGB = $c.DiskSpaceCapacityGB
$freeGB = $c.DiskFreeSpaceGB

$qAddServerDiskSpace = @"
EXEC [dbo].[StoredProcInsert]
   @ServerName = '$serverName'
   ,@DriveLetter = '$driveLetter'
   ,@DiskSpaceCapacityGB = $CapacityGB
   ,@DiskFreeSpaceGB = $freeGB
"@

Made the above adjustments to my script and it runs perfectly now.

Best Answer

Related Solutions

Sql-server – Powershell-type SQL Agent Job failing intermittently trying to get Disk Space on Server via WMI-query

Sql-server – SQL Agent – PowerShell step “syntax error”

Related Question