I have a SQL Server 2014 Agent Job (Type: "Operating system (CmdExec)", Run as: "SQL Server Agent Service Account", Owner: sa) that executes the following command:
powershell -ExecutionPolicy Bypass -Command "$objServiceManager = New-Object -ComObject 'Microsoft.Update.ServiceManager'; $objService = $objServiceManager.AddService2('7971f918-a847-4430-9279-4a52d1efe18d',2,''); $objService.PSTypeNames.Add('PSWindowsUpdate.WUServiceManager');"
The service account that SQL Server Agent is running under has sysadmin privileges to the instance, but does NOT have Admin access to the server.
If I RDP into the server as the Agent's service account, I can execute the command successfully from a command prompt:
But, if I execute the SQL Server Agent Job, it fails with an "Access Denied" error:
Exception calling "AddService2" with "3" argument(s): "Access is denied.
(Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))"
At line:1 char:79
+ $objServiceManager = New-Object -ComObject
'Microsoft.Update.ServiceManager'; $o ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : ComMethodTargetInvocation
You cannot call a method on a null-valued expression.
At line:1 char:170
+ ... efe18d',2,'');
$objService.PSTypeNames.Add('PSWindowsUpdate.WUServiceManager');
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull
I have found that if I set the "Run as" for the job to a proxy account that has sysadmin privileges on the instance AND Admin access to the server, it succeeds, but that's not really relevant to my question.
EDIT: I'm executing everything as CmdExec, and not Powershell, because I need to execute an unsigned Powershell script (not included in my code, since it's not causing a problem). I get the same error if I change the Type to Powershell.
My question: Why does the same command fail as a job, but succeed from the console when both executed from the same user account on the same server?
Best Answer
Potential Workarounds
Since you're running PowerShell through SQL Server
CmdExec
from an agent job, you could try calling a PowerShell script with your saved PS logic in it rather than running raw PowerShell commands to see if that makes a difference for a potential workaround.Try explicitly putting the full OS path on the server to point to
PowerShell.exe
and then pass your PowerShell commands after that in case the issue has to do with environmental variables not working correctly for PowerShell within theCmdExec
shell.Potential Reasons
PowerShell.exe
thru the SQL Server Agent job with theCmdExec
shell and how it interprets security context of the account it's run from or as (e.g. the service account) when it tries to runPowerShell.exe
that way so it's not able to authenticate the execution access to the EXE due to this.A working way I run PowerShell from SQL Agent Jobs
You will need to:
<DomainName>\<ServiceAccount>
)<DomainName>\<ServiceAccount>
)<DomainName>\<ServiceAccount>
) account credentials,Below is a script of the T-SQL part of this process which I run after doing the above 4 steps, and it always works in my environment to accomplish what seems to be similar to what you've explained that you're trying to accomplish. I also have to ensure the AD account has a strong/complex password, and set it to never expire.