1

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

  • Is SQLPS installed? Does this command run without error: Add-PSSnapin *SQL*? – Shawn Melton Oct 20 '15 at 13:05
  • I think Add-PSSnapin SQL will install all sql snap-ins? So I tried to run codeGet-PSSnapincode instead. It runs without error, but no sql snap-ins are installed. If I enter SQLPS from a powershell command, it enters to another kind of promt: 'PS SQLSERVER:\>' it says. But it takes time before entering. – jma73 Oct 20 '15 at 13:57
  • If you can get into SQLPS.exe then it ensures it is installed properly. This is what your SQL Agent PowerShell step runs under. Just a note as well but when you execute scripts under this type of step in 2008 your are running under PowerShell version 1.0. It does not matter if 2.0 or 3.0 is on your server. – Shawn Melton Oct 20 '15 at 16:44
1

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.

  • Thank you for your input. Seems like a useful program Process Monitor. So far I have tried it on my own machine, but I do not have permission to download it on the customers server... I will see what to do. Concerning the error code; it maybe also points to that the user account running Sql Agent do not have sufficient permissions? – jma73 Oct 20 '15 at 14:03
  • @jma73 It might be worth verifying permissions, but an access violation should be related to invalid use of memory rather than permissions (access denied). – James L Oct 20 '15 at 14:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.