Sql-server – Cannot run Sql Server Agent jobs with PowerShell script

powershellsql-server-2008sql-server-agent

The problem:
I have a customer that cannot run Sql Server Agent jobs with PowerShell script. It fails with this message:

Message
Executed as user: xxNC\SQLService. Unhandled Exception:
System.NullReferenceException: Object reference not set to an instance of an object.     
at System.Environment.GetEnvironmentVariable(String variable, EnvironmentVariableTarget target)     at 
System.Management.Automation.ModuleIntrinsics.GetExpandedEnvironmentVariable(String name, EnvironmentVariableTarget target)     at     
System.Management.Automation.ModuleIntrinsics.SetModulePath()     at
System.Management.Automation.ExecutionContext.InitializeCommon(AutomationEngine engine, PSHost hostInterface)     at 
System.Management.Automation.AutomationEngine..ctor(PSHost hostInterface, RunspaceConfiguration runspaceConfiguration, InitialSessionState iss)     at 
System.Management.Automation.Runspaces.LocalRunspace.DoOpenHelper()     at 
System.Management.Automation.Runspaces.RunspaceBase.CoreOpen(Boolean syncCall)
at Microsoft.SqlServer.Management.PowerShell .CustomRunspaceConfiguration.HandleAgentJob(RunspaceConfiguration config)     at 
Microsoft.SqlServer.Management.PowerShell .CustomRunspaceConfiguration.Main(String[] args).  Process Exit Code -1073741819.  The step failed.

As I see it, it not about the powershell script itself. The problem is to execute a PowerShell script at all.

To test I have created a new job with some simple PowerShell, that I have testet on another Windows Server 2008 (Windows Server 2008 R2 Enterprise, with powershell 3.0), where it works. I can also run it from a PowerShell command on the problematic server. But when I create a Sql Server Agent job and run it, it is fails with the same message. So to me it seems that there is a problem with how PowerShell is configured compared to SQL Server…

Systeminfo:

  • Sql Server 2008 (SQL Server 10.50.2500)
  • Windows Server 2008 R2 Standard

I have checked the following:

1) From PowerShell command line (and SQL Server PowerShell command, launched from sql management studio):
$PSVersionTable:

PS C:\Users\xxx> $PSVersionTable
Name                            Value
----                            -----
CLRVersion                      2.0.50727.5485
BuildVersion                    6.1.7601.17514
PSVersion                       2.0
WSManStackVersion               2.0
PSCompatibleVersions            {1.0, 2.0}
SerializationVersion            1.1.0.1
PSRemotingProtocolVersion   2.1

2) Also this seems fine:
$Get-ExecutionPolicy  RemoteSigned

3) If I run directly in SSMS a command like this:

xp_cmdshell 'PowerShell.exe $PSVersionTable'

I also get an error:

“The shell cannot be started. A failure occurred during initialization:
Object reference not set to an instance of an object.”
There is an error in the Event log, with this message: ‘ErrorMessage=Object reference not set to an instance of an object.’

So it must be the same error – I just do not have stacktrace from event log.

Now I am running out of ideas of where to look for the error. So any suggestions on where to look are very welcomed.
The only thing should be that PowerShell is only version 2.0. But I assume this is not that problem?
Maybe Sql Server Agent is not allowed to run powershell scripts? How do I check for that?
Or it could be a security issue? The owner of the job is 'sa'. While for the jobstep Run as entry it is "SQL Server Agent Service Account".

I have found very few posts on similar issue. Fx. Execute Remote Powershell script from SQL Agent but it does not solve my issue.

(I am mainly a .NET Developer so Sql Server and Powershell is not my strongest side).

Best Answer

It sounds like you have a good way to reproduce the problem:

xp_cmdshell 'PowerShell.exe $PSVersionTable'

The next step I'd take is to do some tracing to determine where the error is happening. Since you aren't getting a stack trace in your repro, I'd start by running Process Monitor.

I'd want to start by filtering on processes named Powershell.exe, and then adjust the filters based on the data captured. My suspicion is that this will turn out to be a problem with the PowerShell profile for the Windows service account, but hopefully with Process Monitor you'll get an idea of what error is leading up to the generic -1073741819, which is defined as being a default or unknown error in various Windows libraries.

Significant edit: According to the Visual Studio ERRLOOK feature, the error message of -1073741819 shown in the lower-right corner of the stack trace in your post translates to 0xC0000005. This is defined as STATUS_ACCESS_VIOLATION in ntstatus.h. It could be that some component is not correctly installed, or something is interfering with it (e.g. antivirus). If an access violation is occurring, there might also be further details in Windows' application log.

I'd suggest testing running Process Monitor in a non-production environment first until you feel familiar with it, and selecting the 'Drop Filtered Events' option to avoid consuming large amounts of memory from buffering all events.

Related Question