Invoke-Command in sql agent job step

Hi ,

I was trying to collect the SSL certificate details in remote servers and writing in sql server table.

when I was trying to execute the command


$details =(Invoke-Command -ComputerName ‘servername’ -ScriptBlock {

Get-ChildItem Cert:\LocalMachine\My } --( No error when I executed in powershell console.)

$details =@($detailsarr | select-object -Property Issuer,Subject,NotBefore,NotAfter,PSComputerName -ExcludeProperty RunspaceId )


inside the sql agent job step I am getting the error.

 

FYI - I executed with same user as I executed in powershell console

"Executed as user: NT Service\SQLSERVERAGENT. 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 System.String.Format(IFormatProvider provider, String format, Object args) 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). Process Exit Code 255. The step failed. "
Thanks in advance!

Mohan

$detailsarr is not defined. The variable for the results is set to $details and then you overwrite $details with $detailsarr, which from what you posted would be null.

$detailsArr =(Invoke-Command -ComputerName ‘servername’ -ScriptBlock {

Get-ChildItem Cert:\LocalMachine\My }  –( No error when I executed in powershell console.)

$details = $detailsarr | 
           Select-Object -Property Issuer,
                                   Subject,
                                   NotBefore,
                                   NotAfter,
                                   PSComputerName 

Also, if you you are specifying the properties, there is no need to exclude a specific property. That would only be used if you wanted all properties with the exception of the exclude properties, but you are specifying the properties that are returned with Property.

Hi Simmer

Thanks for your reply.

Let me short my script

(Invoke-Command -ComputerName ‘servername’ -ScriptBlock {Get-ChildItem Cert:\LocalMachine\My })

 

I ran only the above line in the MSSQL sever agent job in step1 , it was failed and got the same error .

The same above line is running fine in power shell console

note: servername is remote server and not local server name.

Error:

Executed as user: NT Service\SQLSERVERAGENT. 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 System.String.Format(IFormatProvider provider, String format, Object args) 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). Process Exit Code -532462766. The step failed.

 

The command is being executed in the context of ‘NT Service\SQLSERVERAGENT’, which most likely does not have access to the remote computer. You need to specify credentials to Invoke-Command.