SSH tunneling with VBA

perlfan

Registered User.
Local time
Today, 10:41
Joined
May 26, 2009
Messages
192
I'd like to give the users of my app the possibility to connect to their MySQL database via SSH tunnel. Yes, Putty is helpful, but also not very handy. Thus, it'd be great to build the SSH tunnel with VBA. Is that possible? How?? :-)

Thanks for help - FRANK
 
Given that this is a thread that has been idle for 10 years, I'm guessing "NO" on the answer.

I don't think it will be easy, but here is an article about using some sort of API that might give you a lead. It mentions manual tunnel setup but also suggests a more programmed approach.


You can continue looking for this answer by doing a web search on "ssh tunnel vba" or "ssh tunnel without putty"

I found this link which MIGHT help. Not entirely sure as it may involve a 3rd-party library.


This is an EXCEL solution:


Somewhere in there you might find a nugget you can keep.
 
Given that this is a thread that has been idle for 10 years, I'm guessing "NO" on the answer.

I don't think it will be easy, but here is an article about using some sort of API that might give you a lead. It mentions manual tunnel setup but also suggests a more programmed approach.


You can continue looking for this answer by doing a web search on "ssh tunnel vba" or "ssh tunnel without putty"

I found this link which MIGHT help. Not entirely sure as it may involve a 3rd-party library.


This is an EXCEL solution:


Somewhere in there you might find a nugget you can keep.
Thank you soooo much! I actually found a lead right here on this website. Going through the forums I saw a lead.
Take a look:

 
Here's some code I used to use for opening/closing a SSH tunnel for MySQL:
Code:
Option Compare Database
Option Explicit

Const PLINK         As String = "C:\Program Files (x86)\plink\plink.exe"
'Const PLINK         As String = "C:\Users\username\Documents\kitty\klink.exe"
Const SSH_SWITCH    As String = " -ssh "
Const PORT_SWITCH   As String = " -P "
Const USE_PRIV_KEY  As String = " -i "
Const PW_SWITCH     As String = " -pw "
Const SWITCHES      As String = " -N -L "
Const LOCALHOST     As String = ":localhost:"
Const DQ            As String = """"
'3307:localhost:3306"

Function OpenSSHTunnel( _
           username As String, _
           PW As String, _
           SSHServer As String, _
           SSHPort As Integer, _
           PortForward As Long, _
           PortLocal As Long, _
           Optional PrivateKey As String _
         ) As Boolean

  Dim strShell As String

  strShell = DQ & PLINK & DQ
  strShell = strShell & SSH_SWITCH & username & "@" & SSHServer
'  If SSHPort > 0 Then strShell = strShell & ":" & SSHPort
  If SSHPort > 0 Then strShell = strShell & PORT_SWITCH & SSHPort
  strShell = strShell & IIf(Len(PrivateKey), USE_PRIV_KEY & DQ & PrivateKey & DQ, PW_SWITCH & DQ & PW & DQ)
'  If Len(PrivateKey) Then
'    strShell = strShell & USE_PRIV_KEY & PrivateKey         ' Path to private key
'  End If
  strShell = strShell & SWITCHES
  strShell = strShell & PortLocal & LOCALHOST & PortForward
  Debug.Print strShell

  TempVars("PLINK_PID") = Shell(strShell, vbHide)
  OpenSSHTunnel = TempVars("PLINK_PID") > 0

End Function

Function CloseSSHTunnel() As Boolean

  Dim blRet As Boolean

  If Not IsNull(TempVars("PLINK_PID")) Then
    blRet = KillProcByPID(TempVars("PLINK_PID"))
    If blRet Then TempVars("PLINK_PID") = Null
  End If
  CloseSSHTunnel = blRet
   
End Function

Function KillProcByPID(lPid As Long) As Boolean

  Dim colProcList As Object, objProc As Object, strComputer As String

  strComputer = "."
  With GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    Set colProcList = .ExecQuery("Select * from Win32_Process Where ProcessID = " & lPid & "")
    For Each objProc In colProcList
      objProc.Terminate
    Next
  End With
  Set objProc = Nothing
  Set colProcList = Nothing
  KillProcByPID = Err = 0

End Function
You will need the plink.exe from Putty(, or klink.exe from Kitty) - adjust the constant paths as required.
 
Here's some code I used to use for opening/closing a SSH tunnel for MySQL:
Code:
Option Compare Database
Option Explicit

Const PLINK         As String = "C:\Program Files (x86)\plink\plink.exe"
'Const PLINK         As String = "C:\Users\username\Documents\kitty\klink.exe"
Const SSH_SWITCH    As String = " -ssh "
Const PORT_SWITCH   As String = " -P "
Const USE_PRIV_KEY  As String = " -i "
Const PW_SWITCH     As String = " -pw "
Const SWITCHES      As String = " -N -L "
Const LOCALHOST     As String = ":localhost:"
Const DQ            As String = """"
'3307:localhost:3306"

Function OpenSSHTunnel( _
           username As String, _
           PW As String, _
           SSHServer As String, _
           SSHPort As Integer, _
           PortForward As Long, _
           PortLocal As Long, _
           Optional PrivateKey As String _
         ) As Boolean

  Dim strShell As String

  strShell = DQ & PLINK & DQ
  strShell = strShell & SSH_SWITCH & username & "@" & SSHServer
'  If SSHPort > 0 Then strShell = strShell & ":" & SSHPort
  If SSHPort > 0 Then strShell = strShell & PORT_SWITCH & SSHPort
  strShell = strShell & IIf(Len(PrivateKey), USE_PRIV_KEY & DQ & PrivateKey & DQ, PW_SWITCH & DQ & PW & DQ)
'  If Len(PrivateKey) Then
'    strShell = strShell & USE_PRIV_KEY & PrivateKey         ' Path to private key
'  End If
  strShell = strShell & SWITCHES
  strShell = strShell & PortLocal & LOCALHOST & PortForward
  Debug.Print strShell

  TempVars("PLINK_PID") = Shell(strShell, vbHide)
  OpenSSHTunnel = TempVars("PLINK_PID") > 0

End Function

Function CloseSSHTunnel() As Boolean

  Dim blRet As Boolean

  If Not IsNull(TempVars("PLINK_PID")) Then
    blRet = KillProcByPID(TempVars("PLINK_PID"))
    If blRet Then TempVars("PLINK_PID") = Null
  End If
  CloseSSHTunnel = blRet
  
End Function

Function KillProcByPID(lPid As Long) As Boolean

  Dim colProcList As Object, objProc As Object, strComputer As String

  strComputer = "."
  With GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    Set colProcList = .ExecQuery("Select * from Win32_Process Where ProcessID = " & lPid & "")
    For Each objProc In colProcList
      objProc.Terminate
    Next
  End With
  Set objProc = Nothing
  Set colProcList = Nothing
  KillProcByPID = Err = 0

End Function
You will need the plink.exe from Putty(, or klink.exe from Kitty) - adjust the constant paths as required.
Wow you're here! Thanks so much! I'll try this out asap and let you know if I have any issues. Thanks soooo much!
 
Haha! I didn't realise you linked to an old post of mine where I posted the (almost) same code!

Let us know how it goes
 
You can have issues if you need to use a private key to login to the ssh server if your host is on Linux. The key from Windows must be in putty format and not native open ssh format.
 

Users who are viewing this thread

Back
Top Bottom