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

  • Could be a matter of permissions. What account is the sql agent job running under? And how are you trying to execute PowerShell commands?

  • SQL agent job is running SQL Server service account and Yes, I am running PowerShell commands like

    get-services -computername NASCEST | Where-object name -like 'SQL*'

     

     

  • What errors does the scheduled script return?

  • try this:

    get-services -computername NASCEST | Where-object name -like '*SQL*'

  • Hi Brahma,

     

    A couple of questions:

     

    1. When you are executing the script are you on the SQL box or on your domain pc?

     

    2. From your domain PC RIGHT CLICK on powershell and open the ISE console as an administrator.

     

    3. Verify that you have permission to that SQL BOX. 

    - put your AD usernamein the POWER USER GROUP

    - put your AD username in the Remote Desktop GROUP

    - verify that you can run scripts against that SQL BOX

     

    4. What is the error that you are getting?

     

    5. Can you log into that SQL box using your AD credentials?

     

    6. I would rewrite the command like so:

    get-services -comp nascest | ?{$_.name -like "*SQL*"}

    OR

    invoke-command -comp nascest -scriptblock {get-services | ?{$_.name -like "*SQL*"}}

     

    Advise and let us know.

     

    ty.

    AM

  • Is there a reason you're using the SQL agent job rather than task scheduler to run powershell scripts?

  • I am getting error "cannot open service control manager"

  • I am running same command

    get-services -computer hostname | where-object -name like '*sql*'

  • Hi Brahma,

     

    A couple of questions:

     

    1. When you are executing the script are you on the SQL box or on your domain pc?

    I tried both. Once sql box and once on my domain PC.

     

    2. From your domain PC RIGHT CLICK on powershell and open the ISE console as an administrator.

    Yes, I am running like this only.

    RIGHT CLICK on powershell and open the ISE console as an administrator.

     

     

    3. Verify that you have permission to that SQL BOX. 

    - put your AD usernamein the POWER USER GROUP

    - put your AD username in the Remote Desktop GROUP

    - verify that you can run scripts against that SQL BOX

     I am in local admin group

    4. What is the error that you are getting?

     cannot open service control manager

     

    5. Can you log into that SQL box using your AD credentials?

    Yes

     

    6. I would rewrite the command like so:

    get-services -comp nascest | ?{$_.name -like "*SQL*"}

    OR

    invoke-command -comp nascest -scriptblock {get-services | ?{$_.name -like "*SQL*"}}

     Tried no luck

     

     

  • 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.