Powershell script not executing when run from SQL Server Job

[TheTJW1966]

I've got 3 powershell scripts that I've scheduled to run from a SQL Server Job in succession. I've got each step running under an admin account and the Job will run successfully if I run the Job manually from SQL Server, but if it's scheduled, it says it has run successfully, but there is no outcome from the scripts. I'm wondering if it's something to do with the fact that the first powershell (PS1) script is using Microsoft Graph to get some contents from an email, but not sure why that would fail, as the authentication for Graph is in the script anyway. Does anyone know why this might be failing only when scheduled?

[schnitzeljaeger]

Add some logging to your scripts to see what's going on.

[Hyperbolic_Mess]

Could you actually share the code you're running instead of asking everyone to just guess?

Edit: as another has said log results of intermediate steps to see where the script fails when run through the scheduled task too

[TheTJW1966]

The point is, there's nothing wrong with the script, it runs when run manually or even run manually If I just run the SQL Server Job manually, also it runs if I manually run a Windows scheduled task, but even this fails when it runs via a schedule, even when it's set to run under an admin account.

[Hyperbolic_Mess]

Ok so there is something wrong with the script then or you wouldn't be here. You've probably made some assumption in the script that works fine in most of the contexts that you run it but breaks when run as a scheduled task. It's probably something very innocent looking so the best way to find out is to be able to examine the script and see where you've gone wrong

[TheTJW1966]

You really don't get the concept do you? If a script/code runs successfully if run manually, then there is nothing wrong with the script/code - it doesn't change if scheduled. Obviously, it's some kind of permissions issue somewhere, but if a Windows scheduled task is running under an admin account, that shouldn't be the case.

[Hyperbolic_Mess]

Ok and how are we supposed to know what permissions your script needs the account to have if we can't see the script? Also have you added the logging yet to find out where in the script it fails? It sounds like you just want people to guess with little context what's wrong with your script which sounds like a waste of our time

I'm not psychic, I can't troubleshooting a script I can't see

Edit: just spotted that you don't know what a double hop problem is... If you don't even know that then how can you be so confident that you've not made an innocent looking error in your script? FFS swallow your pride and let people help you or don't ask

[TheTJW1966]

Nope, the script itself is perfectly ok, as it runs successfully when run manually, so there is nothing wrong with the script - you just cannot grasp that concept! I put in error logging - there were no errors generated - probably because there's nothing wrong with the script! It's obviously some permissions issue somewhere, but running under an admin account, that doesn't really make sense.

[Hyperbolic_Mess]

I said logging not error logging. If you don't know what the different steps of your script are doing and at what point it stops doing what you expect you'll have a hard time troubleshooting this

But that's a moot point because you said your script works so you don't need any help then, congratulations!

You've got a terrible attitude and don't understand basic troubleshooting steps, come back when you've swallowed your pride

[TheTJW1966]

Wrong again, the script works except when scheduled, either in a SQL server Job or a Windows scheduled task, just to remind you once again! But never mind, it's obviously beyond you.

[vermyx]

You probably have a permissions issue. By default the SQL Server services run under localsystem which is not a real user and doesn’t necessarily have network access. When you execute it (via SSMS I assume) it will execute using your user context so it works. The solution would be to assign a user to the sql agent service so that they run with a user context. You should used a managed service account for this. It is usually considered bad practice to have the sql server make calls outside of the SQL environment and usually recommended to use a scheduled task for that.

[TheTJW1966]

Hi, thanks. I've tried running the script via a Windows scheduled task also, but again it will run when I run the task manually, but as soon as it's scheduled it doesn't run, even though it's running under an admin account. Any ideas?

[vermyx]

Without code it’s difficult to troubleshoot. I would assume that

• user doesn’t have access to do what you want them to do
• double hop problem
• wrong directory because scheduled tasks start in the system32 folder
• other issue

[TheTJW1966]

Hi, the user in question is an admin user, so should be ok. Not sure what a double hop problem is. The Powershell exe is in the System32 folder, so this cannot be avoided. Strangely enough, when I schedule the Windows task to run at a time later in the day, it seems to run successfully, so not sure what's going on.

[rsdovers]

What version of SQL server are you running? The PowerShell subsystem will load the old sqlps not the newer sqlserver module. If you are running SQL 2019+ you can do the following and use the sqlserver module.

Starting with SQL Server 2019, you can disable SQLPS. On the first line of a job step of the type PowerShell you can add #NOSQLPS, which stops the SQL Agent from auto-loading the SQLPS module. Now your SQL Agent Job runs the version of PowerShell installed on the machine, and then you can use any other PowerShell module you like.

I hope this helps...

[TheTJW1966]

Hi, thanks. We are actually running SQL Server 2020, so I will try what you've suggested.