SQL Server Agent Access Denied on remote Powershell registry command

Question

I am having trouble executing a Powershell script during a SQL Server Agent Job step. My powershell command(s) basically connect to a remote machine (within the same domain) to write a single value to the registry. From the SQL machine, I am able to manually run the Powershell script locally (from the Powershell ISE interface) successfully, so I know it works. The problem lies somewhere in the SQL Server Agent's permissions on the remote box, but I am clueless in this area.

This Powershell command(s) will work in my SQL Agent Job step if I use the root machine ".".

# Access the DBQ registry setting for pausing
$HKLM = 2147483650 #HKEY_LOCAL_MACHINE
$reg = [wmiclass]'\\.\root\default:StdRegprov'
$key = "SOFTWARE\MySoftwareApplication"
$name = "PauseModule"
$value = "1"
$reg.SetStringValue($HKLM, $key, $name, $value)

My problem is that I get an exception when I specify the remote target machine like so:

$reg = [wmiclass]'\\XXX.XX.XXX.XXX\root\default:StdRegprov'

Here is the exception:

Executed as user: DB-MAIN\SYSTEM. A job step received an error at line 4 in a PowerShell script. The corresponding line is '$reg = [wmiclass]'\XXX.XX.XXX.XXX\root\default:StdRegprov''. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot convert value "\XXX.XX.XXX.XXX\root\default:StdRegprov" to type "System.Management.ManagementClass". Error: "Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))" '. Process Exit Code -1. The step failed.

I have tried to enable firewall ports and programs (on both target and source servers) with no luck. It works manually but not automated in SQL, what gives?

Answer 1

This is because the script is running with the SQL Agent service and this service is running with a Local Account.

You should change the account of the service whih goint to run the script, using services.msc, to one from your domain with privileges on the other servers.

Answer 2

According to exeception description text your script runs from build-in "Local System" account (NT AUTHORITY\SYSTEM). This is powerful account that has full access to the computer, but does not have any rights to access the network.

Your problem: [WMIClass]“\$computername\root\default:StdRegProv” doesn’t return a Wmi-Object but a Wmi-Class + while using the type-accelerator [WMIClass] it’s not possible to provide credentials for the remote-machine.

In Powershell v.2 Microsoft improved WMI support and you can try to change script with some additional parameters to specify necessary credentials for remote access (something like this: $reg = get-wmiobject -list -namespace root\default -computername $computer -credential domain\user | where-object { $_.name -eq "StdRegProv" }

That return’s the WMI-class StdRegProv in namespace root\default like the WMI-type-accellerator-command.