Solved check ssh tunnel status for live connection in vba (1 Viewer)

DKoehne

Registered User.
Local time
Today, 02:56
Joined
Apr 10, 2017
Messages
49
Hello. I am in need of finding a solution to the following problem: I am running a batch file to create a secure tunnel SSH in command line upon login via code. Once the tunnel is established, I want to check that connection. If that connection is "established" or live for that user, I want then to show a locked image icon that represents that user's live connection. Of course if it breaks for some reason, then it will show the opposite as unlocked or "not live" to throw an error message for the user to reconnect their tunnel session. I am wondering what the best practices are for interfacing with the CLI and maybe netstat to determine the status via the code. This will be on a network and should be scalable to set as many sessions as we like. The code has to identify the user (users are identified via login by user id's like abc001) and I am thinking the pc ipaddress and the port - meaning user 1 uses port 2021 tunnel established, user 2 uses port 2022 tunnel established, etc. they each have a separate session but the code is doing the same thing for each user on a different box to identify their live session. I have access front end that is portable and a mysql backend on a network. Thanks for any help on this.
 

vba_php

Forum Troll
Local time
Today, 04:56
Joined
Oct 6, 2019
Messages
2,884
first of all, have you read about this stuff? here is a section relevant to your process: https://en.wikipedia.org/wiki/Tunneling_protocol#Common_tunneling_protocols

that section is something you already know, but there are other resources on the net like this. what do you mean you want to *check* the connection? to make sure that it's secure? SSH in itself is a pretty secure method anyway. I use a few different programs to access servers. Putty being one of them even though it's ancient history at this point.

as for identifying a user, IP addresses of network machines can be captured by VBA and you can look that up online. you can also use the windows system variables I believe, to capture distinctiveness. what I mean is typing "%" in the CMD prompt.

in terms of displaying an image, that's easy. that's a simple conditional statement after checking the validity of your connection, once you get that figured out.
 

cheekybuddha

AWF VIP
Local time
Today, 09:56
Joined
Jul 21, 2014
Messages
2,237
Here's some VBA code I wrote to create a tunnel from within Access.

You need to have plink.exe residing somewhere on the system.

It uses Shell() to open the link and grab the PID of the process. It uses that PID to kill the plink process when no longer needed.

You can write (or adapt) some code to query the winmgmts object to check the PID still exists to check whether the tunnel process is still running (my code does this when closing connection). However, it doesn't check that tunnel is still connected, just that plink process is still running.

Code:
Option Compare Database
Option Explicit

Const PLINK       As String = "C:\Program Files (x86)\plink\plink.exe"
Const SSH_SWITCH  As String = " -ssh "
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 _
         ) As Boolean

  Dim strShell As String

  strShell = DQ & PLINK & DQ & SSH_SWITCH & username & "@" & SSHServer
  If SSHPort > 0 Then strShell = strShell & ":" & SSHPort
  strShell = strShell & PW_SWITCH & DQ & pw & DQ
  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

hth,

d
 

vba_php

Forum Troll
Local time
Today, 04:56
Joined
Oct 6, 2019
Messages
2,884
good to see you on this board, cheeky. I thought you were strictly on Utter Access, which is where you found me initially.
 

DKoehne

Registered User.
Local time
Today, 02:56
Joined
Apr 10, 2017
Messages
49
Here's some VBA code I wrote to create a tunnel from within Access.

You need to have plink.exe residing somewhere on the system.

It uses Shell() to open the link and grab the PID of the process. It uses that PID to kill the plink process when no longer needed.

You can write (or adapt) some code to query the winmgmts object to check the PID still exists to check whether the tunnel process is still running (my code does this when closing connection). However, it doesn't check that tunnel is still connected, just that plink process is still running.

Code:
Option Compare Database
Option Explicit

Const PLINK       As String = "C:\Program Files (x86)\plink\plink.exe"
Const SSH_SWITCH  As String = " -ssh "
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 _
         ) As Boolean

  Dim strShell As String

  strShell = DQ & PLINK & DQ & SSH_SWITCH & username & "@" & SSHServer
  If SSHPort > 0 Then strShell = strShell & ":" & SSHPort
  strShell = strShell & PW_SWITCH & DQ & pw & DQ
  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

hth,

d

Hi Cheeky. Thanks again for this - it's working out great. I do have a follow up question I was hoping you or someone could answer with confidence. The question is: does it matter if access is connected to a database before the tunneling or should tunneling be done first always. I know this works in layers but does one have to be done before the other to ensure that the data is secure.
 

cheekybuddha

AWF VIP
Local time
Today, 09:56
Joined
Jul 21, 2014
Messages
2,237
Hi,

Yes, you need to open the tunnel before connecting to the database. But presumably you couldn't connect unless the tunnel is open because the database will be on a remote IP and you will be trying to connect to localhost where you have mapped the remote port?
 

DKoehne

Registered User.
Local time
Today, 02:56
Joined
Apr 10, 2017
Messages
49
I am connection to remote:22 and port forwarding back to my 3307:localhost:3306. Using Access as frontend/mysql backend; my odbc connector is using port 3306 for traffic while inside the tunnel running in the background thread. Do you see any issues with that?
 

cheekybuddha

AWF VIP
Local time
Today, 09:56
Joined
Jul 21, 2014
Messages
2,237
Hi,

I'm not sure that's correct,

If your remote MySQL instance runs on port 3306 and you forward it to port 3307 on your local machine (via the tunnel) then you want the MyODBC connector in Access to connect to localhost:3307.

Do you have any other instance of MySQL running on your local machine on port 3306?

Is the remote instance truly remote, or is it just running on your local machine?
 

DKoehne

Registered User.
Local time
Today, 02:56
Joined
Apr 10, 2017
Messages
49
Hi,

I'm not sure that's correct,

If your remote MySQL instance runs on port 3306 and you forward it to port 3307 on your local machine (via the tunnel) then you want the MyODBC connector in Access to connect to localhost:3307.

Do you have any other instance of MySQL running on your local machine on port 3306?

Is the remote instance truly remote, or is it just running on your local machine?

Hey There! I have come across a question I am hoping you can answer. I have the process almost completed for the Tunnel and I am at the clean up stage for the last two functions CloseSSHTunnel and KillProcByPID. I am getting a compile error for the KillProcByPID function when called. Can you enlighten me as to how you passed the argument (I am guessing it is the 1Pid that is missing) to the function and from where. Thanks much.
 

cheekybuddha

AWF VIP
Local time
Today, 09:56
Joined
Jul 21, 2014
Messages
2,237
Hi,

In my code in Post#3, look at the line that calls Shell()

It sets the return value (the PID) to a TempVar.

The CloseSSHTunnel() function passes that TempVar to the KillProcByPID() function.

You can use a global variable if you don't like to use TempVars, but you risk losing the value if your app has an unhandled error.

Alternatively you can create a public property, but TempVars seem pretty handy in this instance.

hth,

d
 

DKoehne

Registered User.
Local time
Today, 02:56
Joined
Apr 10, 2017
Messages
49
Hi,

In my code in Post#3, look at the line that calls Shell()

It sets the return value (the PID) to a TempVar.

The CloseSSHTunnel() function passes that TempVar to the KillProcByPID() function.

You can use a global variable if you don't like to use TempVars, but you risk losing the value if your app has an unhandled error.

Alternatively you can create a public property, but TempVars seem pretty handy in this instance.

hth,

d

When stepping through it I found an extra call in the logoff not needed. Thanks for your help on this one - worked out great!
 

Users who are viewing this thread

Top Bottom