SQL Server 2019 - Agent job PowerShell step issue

Question

Post reply

SQL Server 2019 - Agent job PowerShell step issue

Pete Bishop

SSCommitted

Points: 1503

December 30, 2025 at 10:05 am

Go to Answer

#4709445

I have a couple of SQL Agent job steps which run PowerShell commands of the form:

Invoke-Command -ComputerName REMOTE -ScriptBlock {& "E:\Scripts\PowerShellScript.ps1" -ServerName "REMOTE\INSTANCE"}

In each case, these steps have started failing with errors of the form:

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.AppendFormatHelper(IFormatProvider provider, String format, ParamsArray args)
   at System.String.FormatHelper(IFormatProvider provider, String format, ParamsArray 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, IList`1 listOfScriptConfigurationEntries)
   at Microsoft.SqlServer.Management.PowerShell.SqlPowerShell.Main(String[] args)

I've added a "Start-Transcript" to the top of the script but the output file doesn't get created, so I don't think the script is being executed.

If I run "powershell.exe" as the proxy account defined for this job step and run the same command, it runs without issue. So the script is fine and the permissions aren't an issue.

I'm wondering if there's something related to the version of PowerShell used by SQL but can't see why this would be different to a similar setup on another SQL 2019 instance - which works fine.

Pete Bishop

SSCommitted

Points: 1503
More actions
January 5, 2026 at 11:54 am
Answer
#4711917

No idea why, but turning the firewall off and on again at the target server solved the problem. Found this as a result of turning it off to see if that solved the problem - it did - but the problem didn't come back when I turned it on again.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16299 More actions · Answer 1

the server where it works is it a Named instance or default instance?

and my advise, for multiple reasons, is never to use the powershell from SQL Server and instead use a command prompt to execute the script.

Pete Bishop SSCommitted Points: 1503 More actions · Answer 2

All our instances are named. Never had problems with PS job steps previously (aside from the limited command set) and am unsure why this is not working on this particular instance.

Pete Bishop SSCommitted Points: 1503 More actions · Answer 3

As an additional test, I modified the job step to use:

powershell.exe -ExecutionPolicy Bypass -Command "& {Invoke-Command -ComputerName REMOTE -ScriptBlock {& E:\Scripts\PowerShellScript.ps1 -ServerName REMOTE\INSTANCE}}"

and ran it as an "Operating System (CmdExec)" step instead. This time I got the error message:

[REMOTE] Connecting to remote server REMOTE failed with the following error message : WinRM cannot complete the operation. Verify that the specified computer name is valid, that the computer is accessible over the network, and that a firewall exception for the WinRM service is enabled and allows access from this computer. By default, the WinRM firewall exception for public profiles limits access to remote computers within the same local subnet. For more information, see the about_Remote_Troubleshooting Help topic. + CategoryInfo : OpenError: (REMOTE:String) [], PSRemotingTransportException + FullyQualifiedErrorId : WinRMOperationTimeout,PSSessionStateBroken.

I've checked the firewall settings and they're all open as required. In addition, I've run the same command from within a cmd.exe window using the same login as the per the job step proxy and it works without any errors.

Consequently it's specifically related to running these commands from within SQL Agent jobs.

Steve Jones - SSC Editor SSC Guru Points: 740606 More actions · Answer 4

Thanks for the followup. My guess is WinRM needed more than one port, and/or the firewall had failed in some way. Turning it off and on reset the system (it's stateless) and that fixed things.