So, after digging a bit, turns out that PSFTP doesn't recognize the keys to the ftp server. I opened cmd as
SYSTEM (which can be a bit tricky!) and connected via PSFTP, accepted trusting the host (sFTP server), and afterwards the script worked as expected! It didn't cause problems for other users as they were used in testing and had the prompt earlier!
FYI the key is also stored in Registry under
HKEY_CURRENT_USER\Software\SimonTatham\PuTTY\SshHostKeys, so it could be added there for other users if you don't want to login manually (I added that check in my script to add it if missing for any user running the script)..
EDIT: I believe my biggest problem was getting cmd as
SYSTEM so I can debug what is happening, once I could (see link above for how I did it) it became clear to me. For reference and better clarity, below is the output of the command before adding the key, just masked the sensitive parts:
C:\Users\myuser>echo ls | psftp -l myftpuser -pw mycomplexpswd sftp_server
The server's host key is not cached. You have no guarantee
that the server is the computer you think it is.
The server's rsa2 key fingerprint is:
ssh-rsa 2048 SHA256: thecomplexfancyhostkey
If you trust this host, enter "y" to add the key to
PuTTY's cache and carry on connecting.
If you want to carry on connecting just once, without
adding the key to the cache, enter "n".
If you do not trust this host, press Return to abandon the
Store key in cache? (y/n, Return cancels connection, i for more info)
Using username "myftpuser".
Pre-authentication banner message from server:
| Company FTP Login - Please enter valid credentials to continue
End of banner message from server
Keyboard-interactive authentication prompts from server:
End of keyboard-interactive prompts from server
Remote working directory is /