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?
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.
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.
User contributions licensed under CC BY-SA 3.0