November 14, 2008 at 1:22 pm
Can someone explain specifically how to create the proxy account so that the fingerprint is stored? I'm going to be running this from SQL Server Agent scheduled jobs, but the Putty program is installed on my local PC. I'd rather install it on the server, but my network admin won't let me. In any case, how can I do this proxy account thing? Any help is very much appreciated.
November 14, 2008 at 3:06 pm
You're not creating a proxy account per se, you're just tricking the system account (that scheduled jobs run under) into saving the fingerprint.
First, create a file called Y.DAT consisting of the letter Y and a carriage return, that's it. We'll assume it's in D:\.
Second, and this is off the top of my head so I don't expect it to be perfect, but it should give you enough info to solve it yourself:
--create a batch file for downloading your data. It should invoke SFTP, open the connection, and supply the account and password info. We'll call it SFTPDOWN.CMD for purposes of example, we'll further assume it's also in D:\.
--In SQL Server, create a scheduled job with an operating system step with the following command:
D:\SFTPDOWN.CMD <Y.DAT
--Finally, run the job.
By making it a job and running it, you are running it as the system service account and piping the Y into the SFTP command from the batch level says "yes, store the key for this connection."
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
November 14, 2008 at 4:00 pm
lduvall (11/14/2008)
Can someone explain specifically how to create the proxy account so that the fingerprint is stored? I'm going to be running this from SQL Server Agent scheduled jobs, but the Putty program is installed on my local PC. I'd rather install it on the server, but my network admin won't let me. In any case, how can I do this proxy account thing? Any help is very much appreciated.
If I understand this, you have SQL Server Agent running on a server and on your workstation. You want to call the pscp or psftp from a scheduled job on you workstation.
You can save the fingerprint by using PSEXEC on your workstation.
PSEXEC -u mydomain\myproxyuser CMD.EXE
That will open a command prompt as your proxy account;open Task Manager and you can see that the User name for that command prompt has changed. From there launch the program. The fingerprint will be saved with the myproxyuser's profile.
November 14, 2008 at 4:04 pm
Todd Engen (11/14/2008)
lduvall (11/14/2008)
Can someone explain specifically how to create the proxy account so that the fingerprint is stored? I'm going to be running this from SQL Server Agent scheduled jobs, but the Putty program is installed on my local PC. I'd rather install it on the server, but my network admin won't let me. In any case, how can I do this proxy account thing? Any help is very much appreciated.If I understand this, you have SQL Server Agent running on a server and on your workstation. You want to call the pscp or psftp from a scheduled job on you workstation.
You can save the fingerprint by using PSEXEC on your workstation.
PSEXEC -u mydomain\myproxyuser CMD.EXE
That will open a command prompt as your proxy account;open Task Manager and you can see that the User name for that command prompt has changed. From there launch the program. The fingerprint will be saved with the myproxyuser's profile.
I'll have to try that, Todd. I'm going to have to do another PuTTY fingerprint addition probably by the end of the year and another one a couple of months after that.
I just realized that I misunderstood Duvall's original post of running SFTP from his PC against a scheduled job, that changes my answer a bit.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
November 14, 2008 at 4:30 pm
Hey Wayne, I'm not sure I understand what the question was either.
When I read this it struck me as a "build your own adventure" for the answer.
November 17, 2008 at 7:10 am
I apologize if my question wasn't clear. I thought I made it obvious that I don't know what I'm doing when it comes to FTP. There's no need to tell me I don't know what I'm doing since I readily admitted it.
I'm using SSIS to connect to my student information system db, extract 4 csv files of information and then FTP those csv files to a third-party vendor. The vendor will only allow a connection from a secure FTP site, so I can't use the regular FTP task from inside SSIS. I downloaded and installed PuTTY on my local client PC. I have never used it and don't understand it. I'm learning what I can from the help files. I've been given a username and password as well as an IP address and port number for the vendor's FTP site. I'm trying to use the "Execute Process Task" in SSIS to use a command line prompt to use PuTTY that way. I've tried using PFTP, SFTP, and pscp. None of them will work. The last time I tried, I directed the Executable line to the executable that's present on my local PC. In the arguments line, I tried this:
pscp IPAddressHere -p Port#Here -l UsernameHere -PasswordHere P:\XEI\*.csv Username@IPHere
That didn't work either.
I need some help figuring out how to call it from the cmd line and also in determining how best to deploy the solution. I don't want to rely on my PC having the copy of PuTTY. My PC is going to be replaced soon. Do I install PuTTY on the SQL Server where my student info system db resides? Is that safe?
Again, I freely admit I don't know what I'm doing. If anyone is will to help a newbie, I appreciate it.
November 17, 2008 at 7:29 am
Hi ludvall,
First thing you might want to do is get the Secure FTP working outside of SSIS from a command prompt. And where you finally install PSFTP or PSCP depends on where the SSIS pacakge will be run from.
You said the other side gave you a port number to connect to? You may need to check if your firewall allows traffic to that port.
November 17, 2008 at 7:34 am
If I understand what I'm reading correctly, PuTTY purposely doesn't allow passing the password in via cmd prompt. When I connect from the cmd prompt, it prompts me for my password. When I put it in, it readily accepts it and I'm able to put files and get files. However, I don't know how to get this to work without the cmd line tool prompting me. Can anyone give me an example of the correct syntax? Or do I really have to build some sort of "session"? I tried launching the GUI version of PuTTY and seeing if I could get it to create a session variable, but I cannot get it to work.
November 17, 2008 at 8:27 am
I use the following batch file, which uses a command file:
f:
date /t
time /t
F:\FileDownloads\psftp.exe -pw [mypassword] -b F:\FileDownloads\get.script [mylogin] -bc
The file get.script has the following:
mget *.txt
bye
Don't include the brackets in the password or login.
I do the date and time command so I have a current timestamp in my log for troubleshooting, you get a log if you specify an output file under the advanced tab in SS2000, it might be a little different in 2005. The mget command is multiple gets, so it downloads all files with a txt extension, then signs off.
Hope this helps!
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
November 17, 2008 at 9:35 am
Thank you so much, Wayne! That worked beautifully. I really appreciate your help!
Do you have any idea how to keep the PSFTP piece from opening up the command line window to show what it's doing? It doesn't hurt anything while I'm testing on my box, but I'm not sure if it's a problem when it runs on the server.
November 17, 2008 at 9:38 am
I'm glad we finally worked out what you needed and what I had helped!
If you do a scheduled task, as far as I know, it won't open a window, it'll run silently in the background.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
November 17, 2008 at 9:51 am
I'd test it running from a job to be sure. The software we are using for SFTP worked fine in development and died when it was ran as a job. I had to review the command line options and found one that kept the software from opening a window. I'd double check the PuTTY command line options to be sure if there is (or isn't) one for the same reason.
November 17, 2008 at 9:55 am
Interesting, Lynn. On my 2000 box, it runs silently in the background as a scheduled job. Are you using PuTTY's PSFTP?
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
November 17, 2008 at 10:09 am
Nope. That's why I said test it to be sure. The biggest thing is no surprises.
Last time I looked at PuTTY was as a terminal emulator to connect over SSH.
We are using CoreFTP. They have a 64-bit SFTP client we are using to transfer data to a MSP. The only real issue I have with it, is that I had to develop (and therefore maintain) the SSIS package that uses it on the actual server running the client. It wouldn't work with UNCs.
Other than that, it has worked like a charm.
November 17, 2008 at 10:51 am
Interesting. The people who wrote PuTTY also wrote an SFTP program called PSFTP, they have a few other useful TTY family programs. I dug it off their web site at http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html. But you're absolutely right, test everything!
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 15 posts - 16 through 30 (of 40 total)
You must be logged in to reply to this topic. Login to reply