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?

-

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.

-
This worked well. It makes sense now, but unfortunately, I did not setup this SQL Server. Thank you for pointing this out, very helpful! –  D3vtr0n Oct 17 '11 at 20:25
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 Polo Oct 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. 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.

-
Ricardo Polo's answer fixed my problem. Apparently the credentials are sufficient for Powershell to use. Thanks for answer! –  D3vtr0n Oct 17 '11 at 20:24