Help Needed: PowerShell Script Not Functioning Correctly When Run Via SQL Server Agent

Copper Contributor

 Hello, I am facing an issue with a PowerShell script that is critical for our data operations. This script successfully retrieves data from an API and loads it into a SQL database when executed manually, but encounters problems when run via SQL Server Agent.

 

Problem Description:

  • Normal Execution: The script functions flawlessly when executed manually from Visual Studio Code or a terminal logged in with our service user.
  • Issue with SQL Server Agent Execution: However, when the same script is executed through SQL Server Agent, using the same service user, it fails to operate as expected. The specific issue arises at the command $response = Invoke-WebRequest -Uri $Endpoint -Method Get, where the script seems to freeze.
  • Anomalous Behavior: Intriguingly, adding the -UseBasicParsing argument to the Invoke-WebRequest command seems to temporarily resolve the issue. However, I am very curious to know why that resolves the issue.

Troubleshooting Steps and Observations:

  • We suspect that a group policy or a similar network-level restriction might be hindering the script's functionality when executed via SQL Server Agent, but we are not sure which privilege we need to grant to whom. The service user has sufficent privileges to execute the script manually. 
  • The script executes successfully - also through the SQL Server Agent - when a terminal session is initiated by the service user on the server.

Assistance Requested:

  1. Root Cause Analysis: Insights into what might be causing this behavior, especially focusing on the differences in the execution environment between manual runs and SQL Server Agent.
  2. Understanding -UseBasicParsing: Why does the inclusion of this argument temporarily solve the problem? What does it imply about the script's execution context?
  3. Permanent Solution: Recommendations for any changes in system setup, configurations, or script modifications to resolve this issue without relying on the -UseBasicParsing argument.
  4. Preventive Measures: Advice on how to anticipate and prevent such issues when running similar scripts in the future.

Conclusion: Thank you in advance for your time and assistance. I am happy to provide any additional details or clarifications that might be helpful in resolving this issue.

2 Replies
Hello.
I got a similar problem and I have fixed it by setting the sql agent job step as Command line to call a .ps1 file located on the file system
Regards
Javier

@Javier Villegas Thank you for your reply. In my setup I call the script as part of a SSIS-package and it is not feasible to run the script directly as a commandline call in the SQL server agent.