6

A test SQL Server job hangs when it executes a Powershell script. It does work using my login and a Powershell window, but not using the Agent account and a job. The SQL Agent user has read/write/execute permissions to the directory with the powershell script.

Example:

Run as: SQL Server Agent Service Account

powershell.exe -file \\Server02\test2\Test.ps1

The agent account has been given access also.

This test file currently does something very basic: displays a string.

When it runs, it does not show an error message, because it just does not respond. Do you have any idea about what is happening, or which security settings are missing?

Improve this question
1
  • 1
    It's very possible there's some sort of prompt that can't be seen. I encountered this before with a powershell step in a SQL agent job, but I would normally expect an error to be logged. The root cause is likely networking and/or permissions. You can test this by calling the powershell script from xp_cmdshell as well.
    – JNK
    Dec 2, 2014 at 20:35

2 Answers 2

Reset to default
4

JNK was correct in the comment above. If I ran it in the command shell, there was a prompt saying

Security Warning Run only scripts that you trust

In order to avoid this message, I added -ExecutionPolicy Bypass to the command

Example: powershell.exe -ExecutionPolicy Bypass -file \Server02\test2\Test.ps1

The job is now able to complete.

Improve this answer
1
  • 2
    Just as advice I also tend to add -noprofile to the command. This prevents any code from being loaded from a profile for the executing account that someone might have setup (maliciously or otherwise).
    – user507
    Dec 23, 2014 at 16:55
0

It sounds like you need to create a credential and then use that with a proxy account. I had that issue when I was running a remote script via the agent and the credential/proxy resolved the problem for me. You can refer to this post where someone had a similar issue/solution. Note the caveats about permissions, you could use your account for the credential, but if this is meant to run in the event you leave you would be well served to use a service account and grant it the needed permissions.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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