SQL agent job failing with cannot open service control manager

Dear Experts,

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?

 

Thanks

Brahma

Parents
No Data
Reply
  • Hey Brahma,

    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

    Get-Service -ComputerName 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.)

    Get-Service -ComputerName sqlserver2014a | Where-Object { $_.Name -like '*sql*' }

    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.

Children
No Data