I am able to run get-services from powershell command window/ise but when I schedule the same script in SQL agent job and execute, it is failing with cannot open service control manager.Can anyone share your experience on this. Am I missing something?
I know I'm late to the party, but I wanted to chime in.
I can actually get the same error if I pass an alias (CNAME or HOSTS) instead of the NetBIOS name of the server. In my case, sqlserver2014a is aliased to sqlserver
The result is "Get-Service : Cannot open Service Control Manager on computer 'sqlserver'. This operation might require other privileges."
If I use the NetBIOS name or the IP, however. It will work. This is expected behavior because of Kerberos (probably.)
I was able to get the above code using SQL Server Agent with a PowerShell step. Prior to running the step, I confirmed that the SQL Agent account on the source server was an account that has access to the destination server. As an alternative, I could have created a Credential and used that instead. (I've outlined how to do this in PowerShell within this post, scroll down to "Create credential, proxy account, and job to run local PowerShell scripts".)
Here's how I setup my job. The first time I ran it, I used the proper code, and it ran successfully. Then I tested it using code that I knew would fail.
Then click on Advanced, so you can set the job output to show up in the Steps
Now check the job history, and look at
Another way to test is to run the PowerShell window as the SQL Agent user. That will help you better troubleshoot. Shift+Right click a PowerShell shortcut, then Run as User.
Hope that helps.