PowerShell directly vs. SQL Agent PowerShell Job Steps

Question

If I run this 3 line script in Powershell (Run As Administrator, and my domain account is a local administrator on MyRemoteServer), it works as expected (note that there is a dollar sign in the service name that I had to escape with a back tick):

Invoke-Command -ComputerName "MyRemoteServer" -ScriptBlock{Get-Service -Name "AOS60`$01" | Stop-Service -Force}
get-service -Name "AOS60`$01" -ComputerName "MyRemoteServer" | Format-Table -Property MachineName, Status, Name, DisplayName -auto
Invoke-Command -ComputerName "MyRemoteServer" -ScriptBlock{Get-Service -Name "AOS60`$01" | Start-Service}

[Step 2 is to provide proof that the service actually has a status of "Stopped" after step 1. Once successfully converted to an Agent Job, step 2 will actually invoke a stored procedure that does a series of database restores.]

If I put these statements into SQL Agent (SQL Server 2012 SP2) as job steps and start the job, the job crashes (it doesn't merely fail, it wants to invoke the debugger) at the first step. The domain service account that runs the SQL Agent Service was allegedly granted the necessary permissions on MyRemoteServer by our Operations group.

The job script encountered the following errors. These errors did not stop the script:

Unhandled Exception: System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument list. at System.Text.StringBuilder.AppendFormat(IFormatProvider provider, String format, Object[] args) at System.String.Format(IFormatProvider provider, String format, Object[] args) at Microsoft.SqlServer.Management.PowerShell.Res.GetString(String key, Object arg0) at Microsoft.SqlServer.Management.PowerShell.SqlPowerShell.GetErrorRecordMessage(ErrorRecord errorRecord) at Microsoft.SqlServer.Management.PowerShell.SqlPowerShell.HandleAgentJob(RunspaceConfiguration config) at Microsoft.SqlServer.Management.PowerShell.SqlPowerShell.Main(String[] args)

I scripted out the job below for reference. Note that it fails even if I leave out the @output_file_name, so the problem isn't writing to the output file but in executing the step itself.

USE [msdb]
GO

/****** Object:  Job [Test stopping and restarting AX]    Script Date: 6/21/2017 1:49:30 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 6/21/2017 1:49:30 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Test stopping and restarting AX', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'ROGUE\amfreeman', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Synchronously Stop AX Service on DEVAOS2]    Script Date: 6/21/2017 1:49:30 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Synchronously Stop AX Service on DEVAOS2', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'PowerShell', 
        @command=N'Invoke-Command -ComputerName "MyRemoteServer" -ScriptBlock{Get-Service -Name "AOS60`$01" | Stop-Service -Force}', 
        @database_name=N'master', 
        @output_file_name=N'E:\Microsoft SQL Server\MSSQL11.RELEASE\MSSQL\Log\Test_stopping_and_restarting_AX_$(ESCAPE_SQUOTE(STEPID))_Synchronously_Stop_AX_Service_on_DEVAOS2.txt', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Get status of  AX Service on DEVAOS2]    Script Date: 6/21/2017 1:49:30 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Get status of  AX Service on DEVAOS2', 
        @step_id=2, 
        @cmdexec_success_code=0, 
        @on_success_action=3, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'PowerShell', 
        @command=N'get-service -Name "AOS60`$01" -ComputerName "MyRemoteServer" | Format-Table -Property MachineName, Status, Name, DisplayName -auto', 
        @database_name=N'master', 
        @output_file_name=N'E:\Microsoft SQL Server\MSSQL11.RELEASE\MSSQL\Log\Test_stopping_and_restarting_AX_$(ESCAPE_SQUOTE(STEPID))_Get_status_of__AX_Service_on_DEVAOS2.txt', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Synchronously Start AX Service on MyRemoteServer]    Script Date: 6/21/2017 1:49:30 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Synchronously Start AX Service on MyRemoteServer', 
        @step_id=3, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'PowerShell', 
        @command=N'Invoke-Command -ComputerName "MyRemoteServer" -ScriptBlock{Get-Service -Name "AOS60`$01" | Start-Service}', 
        @database_name=N'master', 
        @output_file_name=N'E:\Microsoft SQL Server\MSSQL11.RELEASE\MSSQL\Log\Test_stopping_and_restarting_AX_$(ESCAPE_SQUOTE(STEPID))_Synchronously_Start_AX_Service_on_MyRemoteServer.txt', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

How I can get the SQL Agent Job to run properly?

I would verify that your script run in Powershell version 2.0 with the service account before putting it in the job. see http://www.travisgan.com/2013/05/sql-server-agent-job-and-powershell.html — SteveB, Jun 22 '17 at 13:38
@SteveB: Good idea! When I run Powershell as the service account, it doesn't have rights to see the service. So it must need to Run as Adminstrator, but I don't know how to Run as Another User and Run as Administrator at the same time. The machine I'm running it on is a 2008 R2 Server with SP1 that seems to be running PS 1.0. But I don't think that this is a 1.0 vs. 2.0 issue. — Mark Freeman, Jun 22 '17 at 14:40

score 1 · Answer 1 · answered Jun 22 '17 at 14:47

I would strongly advise against using PoSH job steps in SQL Agent. That's because the version of PoSH that's executed might not be the version that you expect to be executed.

For calling PoSH scripts, I only use CmdExec job steps that call the PoSH executable. My scripts also use PoSH remoting, and I don't have any issues.

score 1 · Accepted Answer · answered Jul 03 '17 at 19:48

There were two issues:

1) Note the word "allegedly" in this sentence in my original post: "The domain service account that runs the SQL Agent Service was allegedly granted the necessary permissions on MyRemoteServer by our Operations group." As it turns out, all of the required permissions had not been granted to the domain account that runs the SQL Server Agent service.

2) I took the advice of @NedOtter (and from various other blogs and Stack posts) and converted to using PowerShell through CmdExec. I don't know that this was necessary, but it sure seems to be the consensus on the way to go.

I used two job steps, with the following in the @command parameters:

C:\Windows\System32\WindowsPowerShell\v1.0\Powershell.exe "Invoke-Command -ComputerName ''MyRemoteServer'' -ScriptBlock{Get-Service -Name ''AOS60$01'' | Stop-Service -Force}"
C:\Windows\System32\WindowsPowerShell\v1.0\Powershell.exe "Invoke-Command -ComputerName ''MyRemoteServer'' -ScriptBlock{Get-Service -Name ''AOS60$01'' | Start-Service}"

These combine to synchronously stop and then start the service. I then inserted a job step in between that restores the databases in this test instance from my production backups.

The problem with the PowerShell job step is only partly related to the version of PS you are in, and this has been fixed if you are on the latest release of SQL Server. The main issue is the job step runs under the context of SQLPS, the provider for SQL Server. Which is not the same environment as the PowerShell.exe host, so you can get different results based on what you are doing. — , Jul 09 '17 at 00:25

PowerShell directly vs. SQL Agent PowerShell Job Steps

2 Answers2