I'm learning how to use SSIS to create auto scheduled tasks. Everything went smoothly until the security policy has changed. Before whenever I wanted to connect to database, I just type in IP address, username and password in SSIS connection manager. Now I need to open up OpenVPN first which is still a manual process. So I really would like to put this bit into an SSIS task flow.
I had a search on Stackoverflow and over some other websites. Here is a link in stackoverflow. This example gives the idea that we can use VBA to setup the link. However that example shows we need a VPN connection name and VPN username which I don't really have and don't know where to find. Everytime I just right click on a .ovpn file and choose start openvpn on this config file. Also when I open that .ovpn file there is no command indicating which is my connection name or user name. Here is what it looks like when I open it up (there is no -auth-user-pass thing in this file and I can use this file without having to specify it):
client dev tun proto udp remote a website here 1111 resolv-retry infinite nobind persist-key persist-tun comp-lzo verb 3 ca ca.crt cert office.crt key office.key askpass login.txt Is it possible to find my connection name and user name? If I don't need connection name and user name, can I still write up VBA to build up an auto connection process?
I also notice there is an execute process task in SSIS. Is it possible that I can make use of it (because in my understanding obviously I use openvpn.exe to execute a .ovpn file)?
Please correct me if I'm saying something wrong since I'm new to both VPN and SSIS. And much appreciate for whoever can help me with this.
1 Answers
Answers 1
I don't know anything about OpenVPN, but I ran into a similar problem a while back with a server that insisted on an SSH tunnel using PuTTY as the only allowed connection method.
People who put these policies in place never think of the impact on SSIS, especially for unattended processing.
The solution I found was:
- Find the command-line equivalent to the PuTTy client (which is called plink)
- Have the detailed documentation to hand, because this is really difficult.
- Have the SSIS package start plink.exe, passing various parameters (Note: don't use the Execute Process task, as that waits for the process to exit: launch the process in a Script task, and get the handle of plink.exe's ProcessID).
- After a wait, have another Script task in SSIS connect to the plink process' InputBuffer, "typing in" a password. Yes, insecure and clunky, though you can store SSIS project parameters encrypted (this obviously requires SSIS 2012 or later)
- The SSIS package can now connect to the server and do data stuff, as if it was a normal server without stupid access requirements.
- At the end of the package, kill the plink.exe process.
Bear in mind, also, that on unattended execution (e.g. when scheduled in a job), your package will be running in a different security context from the one you work in to develop the package (depending on the SSIS service settings). If your access to OpenVPN has any dependency on ActiveDirectory, you may have to set up another OpenVPN account for the SSIS service's Logon user.
If this sounds horrible, nightmarishly complicated and unstable, it's because it is. Really, the TL;DR version is: tell whoever administers the server that if they really want you to access it using SSIS, they should put some more sensible access policy in place.
0 comments:
Post a Comment