Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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.

enter image description here

share|improve this answer
    
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
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 Polo Oct 17 '11 at 20:30
add comment

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.

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.