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

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?

This worked well. It makes sense now, but unfortunately, I did not setup this SQL Server. Thank you for pointing this out, very helpful!
–
D3vtr0nOct 17 '11 at 20:25

1

On more thing @Devtron, dont use a traditional account here. Create what is called a Service Account on your Active Directory. That means an account who its password never expires and cannot be change. This is because if, a example, you use your administrator account here and some day you change the password SQL will fail.
–
Ricardo PoloOct 17 '11 at 20:30

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.