SQL Job, Impersonation and Process.Start

[mikep]

Hi,

I've posted this in the SQL Server programming group as well but thought I'd
try here as its C# involved.

At a client I'm working at, we need to send files via SFTP to a server on
completion of several steps of a SQL Job. The client has a command line SFTP
program which requires when invoking, to impersonate a particular user
otherwise the SFTP connection is not accepted.
I have developed a small C# console app which uses
System.Diagnostics.Process.Start to invoke the command line SFTP program as
the required user and pass in the correct arguments (Windows RunAs does not
allow a password to be passed in). The console app also waits for the SFTP
program to complete. I have a DTS package which puts together an appropriate
batch file and is then executed using the console RunAs app at the end of the
DTS . i.e. NewRunAsConsoleApp.exe SFTPCommand.bat /U:User /Password etc

If I run the DTS from the designer, everything works fine. I see the batch
file run using the NewRunAs console app, the SFTP command window open, the
SFTP works and the window closes and then the batch file window closes.

If I then add the DTS as a step in the schedule the job, it does not work.
The process hangs waiting for the SFTP command window to shut. i.e. The
window that was invoked using Process.Start, does not seem to be able to get
a handle on the SFTP window that opened and hence the parent window just sits
there waiting for its completion (this is my assumption)

If I remove the wait for the window to shut, the SFTP does not occur i.e.
The parent window seems to shut immediately without executing the batch file.

I realise that the SQL Job somehow supresses the window being visible as in
when just running the DTS package but does anyone have any ideas on how to
get this to work?

What is the SQL Job scheduler doing that interferes with the Process.Start
and the WaitForExit calls?

Any ideas gratefully received

Thanks in advance

Mike

 

[Andy O'Neill]

Hi,

I've posted this in the SQL Server programming group as well but thought
I'd
try here as its C# involved.

At a client I'm working at, we need to send files via SFTP to a server on
completion of several steps of a SQL Job. The client has a command line
SFTP
program which requires when invoking, to impersonate a particular user
otherwise the SFTP connection is not accepted.
I have developed a small C# console app which uses
System.Diagnostics.Process.Start to invoke the command line SFTP program
as
the required user and pass in the correct arguments (Windows RunAs does
not
allow a password to be passed in). The console app also waits for the SFTP
program to complete. I have a DTS package which puts together an
appropriate
batch file and is then executed using the console RunAs app at the end of
the
DTS . i.e. NewRunAsConsoleApp.exe SFTPCommand.bat /U:User /Password etc

If I run the DTS from the designer, everything works fine. I see the batch
file run using the NewRunAs console app, the SFTP command window open, the
SFTP works and the window closes and then the batch file window closes.

If I then add the DTS as a step in the schedule the job, it does not work.
The process hangs waiting for the SFTP command window to shut. i.e. The
window that was invoked using Process.Start, does not seem to be able to
get
a handle on the SFTP window that opened and hence the parent window just
sits
there waiting for its completion (this is my assumption)

If I remove the wait for the window to shut, the SFTP does not occur i.e.
The parent window seems to shut immediately without executing the batch
file.

I realise that the SQL Job somehow supresses the window being visible as
in
when just running the DTS package but does anyone have any ideas on how to
get this to work?

What is the SQL Job scheduler doing that interferes with the Process.Start
and the WaitForExit calls?

Any ideas gratefully received

Thanks in advance

Mike

First off. I don't think running a console app from a job is a good idea
mate.
Having said that, there's a classic gotcha in running packages etc from a
console directly and they work but they fail in a job. That's because the
job uses different security credentials.
I recommend you take a look at this article though:
http://www.codeproject.com/KB/database/SSIS_SFTP.aspx