Open Source, ON WINDOWS!
I’m still stunned in this day and age that people pay obscene amounts of money on tools that are easily replaced in most cases with quality and stable open source alternatives. Part of the problem is that a lot of OSS tools don’t work well on Windows or require some fiddling which can scare off some people. The two tools I’ll be chatting about today don’t fall under that category. In one or two steps you have them installed and ready to rock. I’m constantly working with clients that require file movement in a secure manor. Ether upload or download.
cURL, World Wide Web Command Line
curl is a command line tool for transferring data with URL syntax, supporting DICT, FILE, FTP, FTPS, Gopher, HTTP, HTTPS, IMAP, IMAPS, LDAP, LDAPS, POP3, POP3S, RTMP, RTSP, SCP, SFTP, SMTP, SMTPS, Telnet and TFTP. curl supports SSL certificates, HTTP POST, HTTP PUT, FTP uploading, HTTP form based upload, proxies, cookies, user+password authentication (Basic, Digest, NTLM, Negotiate, kerberos…), file transfer resume, proxy tunneling and a busload of other useful tricks.
Holy cow, that’s a ton of stuff! I use cURL mostly for HTTP and HTTPS stuff. It comes as a command line tool and a library. To get all you can out of cURL you need to install a couple of bits on Windows. First of all our favorite redistributes in the form of vcredist_x86.exe. cURL website has a link to “The ones that work” but I ended up just using the latest from Microsoft.
Secondly, if you want the S part of HTTP you need to install the Win32 OpenSSL libraries. Once that is done you will need to go do some reading. cURL is very powerful and has very good documentation.
Here is an example of downloading a file from a secure HTTPS server.
curl https://<pass>:<user>@<mysecuresite.xxx>:443/ -o transfer.log -k -l -c CSX https://<mysecuresite.xxx>:443/<myfile.csv> -o “E:<localfolder><myfile.csv>” -s -S
Ow, that’s a mouth full. Lets break it down.
curl – the command line tool
https://<pass>:<user>@<mysecuresite.xxx>:443/ – the secure website we are talking to
-o transfer.log – a log of what the server returns, in this case it just sends a “login successful” message
-k -This allows us to accept self-signed certificates I use this if we are dealing with site to site transfers inside the company
-l – This tells cURL to follow 3xx http redirects
-c CSX – This tells cURL to setup a cookie store some sites need them some don’t.
https://<mysecuresite.xxx>:443/<myfile.csv> – The path and name of the file we want to grab
-o “E:<localfolder><myfile.csv>” – If you don’t tell cURL to save it to a file it will just echo it at the command prompt.
-s – This tells cURL to be silent. You can omit this and add the -v for verbose when you are troubleshooting issues.
-S – This tells cURL to NOT be silent on errors. If you are automating things trapping the error is important.
BAM!
Now I can grab the files I need secured via SSL over HTTP. Script them via batch file and call them from an SSIS package or SQL Server Agent job step.
WinSCP, Softer Than PuTTY
WinSCP is another tool for transferring files built specifically for Windows. If you wanted to do SFTP, SCP or FTPS you would usually fall back to using PuTTY’s psftp.exe command line tool. It does allow for robust automation but I’ve always had a headache dealing with importing SSH keys. Since the scripts will be executed under the service account that SQL Server is running under I would always have to log into the server as the service account, execute the script and accept the key. Not a huge deal just a PITA since the key is stored in the registry of he locally logged in profile. WinSCP allows you to add the SHA key in the script bypassing all that hassle.
Lets look at an example batch operation.
I’m calling this from a batch file
winscp.com /script=e:<batchdir><wsftpcommands.txt> -resumesupport /parameter “%1”
winscp.com – This is the command line executable
/script – This is the command script we want to execute
-resumesupport – This bit of awesomeness will allow restarting of failed downloads so, if you have a large file you can resume instead of starting over if it fails
/parameter “%1” -This passes a single parameter to the command script. So, I’d call winscp.bat “<filename>” and it will pass the file name into the command script.
wsftpcommands.txt internals.
# Automatically abort script on errors
option batch abort
# Disable overwrite confirmations that conflict with the previous
option confirm off
open sftp://<user>:<pass>@<ftp.mysecureftpserver.xxx> -hostkey=”ssh-rsa 2048 xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx”
# Change remote directory
cd /<mydir>/<mysubdir>
# Force binary mode transfer
option transfer binary
get “%1%” “<mydir><mysubdir>%1%”
# Disconnect
close
# Exit WinSCP
exit
Poof!
I’ve now got a bulletproof way to download files securely AND have the ability to restart failed downloads. Again, I can’t state how huge this is. I’ve got a backup file coming from France nightly and it is 1 terabyte. If it fails say at 999 gigabytes and I had to restart from the beginning it would be a real bummer, oh and miss our restore window completely.
Don’t Be Afraid To OSS
With these two tools I made these download processes automated and reliable for the client replacing a tool that costs thousands of dollars a year, and wasn’t as reliable as two command line tools and a batch file.
Sharing
Do you use OSS tools like these? I’d love to hear about them and if you can provide an example on how you use them and I’ll add them to this post!