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?