Publishing data from datawarehouse to HTTPS

Hi all,

Last week, I had to publish a datafile to a HTTPS location, for one of our clients here in France.
Easy to do with gui, but how to automate and embed in a DTS or ETL process under Windows ? Not easy.
What did I use ? Open source cURL !!
Here is the recipe I followed.

1 - Install OpenSSL on your machine, Windows compilation of course.
2 - Take cURL, a famous Unix / Linux tool. Take a Windows compilation with SSL support and forget the cygwin version (beurk).
3 - The certificate part, the moste tricky :
Windows / IE use certificates in the form P12 (PKCS12), which is different from the cURL Unix certificate : PEM.
So you have to convert the certificate and extract the userkey file, the usercertificate file and the ca file !
To do the conversion, use the following commands, with OpenSSL. I did it on a Linux box :

First, extract the userkey.pem from the certificate :
vince@fc: openssl pkcs12 -nocerts -in cert.p12 -out userkey.pemEnter Import Password: (insert your certificate password)MAC verified OKEnter PEM pass phrase: (insert your Enter PEM pass phrase)Verifying - Enter PEM pass phrase: (reinsert your Enter PEM pass phrase)

Then extract the usercert.pem file:
vince@fc: openssl pkcs12 -clcerts -nokeys -in cert.p12 -out usercert.pemEnter Import Password: (insert your certificate password)MAC verified OK

Finally, extract the ca.pem file :
vince@fc: openssl pkcs12 -cacerts -nokeys -in cert.p12 -out ca.pemEnter Import Password: (insert your certificate password)MAC verified OK

4 - Then you can type your cURL command :
curl -u toto:tata -E certif.pem:passphrase --cacert ca.pem --proxy-ntlm -U FRLAR01\toto:tata -x proxyserver:8080 https://213.41.176.116/quix -v -T D:\FLATFILES\Client\Export\File_to_export.txt

Explanation of the command :
curl is the command, of course
-u is for user:password to the HTTPS site. Here I anonymised of course ...
-E is for the new converted client certificate in pem format
--cacert is for giving the ca certificate in pem format
--proxy-ntlm is for allowing proxy to connect in case of a proxy usage
-U is the credentials for the proxy. Here again I anonymised the data
-x is the proxy:port adress
-https ... is the destination adress on https
-v is for verbose mode. Usefull.
-T is for indicating the file to transfert. Here the file is name File_to_export.txt and is a txt datafile

You can see here I choose to use the proxy on each command, instead of setting up the proxy on my server. This is why I don't need any ISA proxy client on my datawarehouse server, so by using a direct proxy connection when needed, I save some server resources.

I hope this will help the community for any next HTTPS data push.