I am experiencing the same issue in relation to the Servers host key. Unfortunately I have not been able to get James’ fix to work.
My set up is as follows:-
SQL Server and SQL Server Agent both run under Local\System
The code I am trying to run is as follows:-
DECLARE @sDosCommand VARCHAR(90)
SET @sDosCommand = 'D:\Putty\pscp -q -batch -scp -C -pw Password -l user UserName@IPAddress:F:/Testfile.txt F:/ < D:\Putty\Important.txt'
Note Important.txt has Y and the carriage return lines in it.
If I run the above form the command line it works perfectly (due to the host key for the logged in User already been generated). However when I run it from within a ISQL session (logged in as the SA account), ISQL hangs as it waits for the relevant response to its Hash prompt (required as it wants to create one based on SQL Server Agents, Service account ie. Local\System)
As you can see I have tried James’ trick by using the input pipe reference, but to no success.
Can anyone show me where I am going wrong.