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?
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.