SSH Tunnel from Access (General questions)

Hello,
Just downloaded the trial and I'm trying to SSH tunneling working from within Microsoft Access in order to connect to MySQL ODBC connections within the same database. I have the code working if I use another instance of Access but I can't seem to get it working with the instance I want. I'm using the ActiveX EXE but I think it might be getting stuck on the UserConnecting event. From another instance, I can see this go through if I set Allow = True. However, it does not seem to work from the current instance to tables in the same database.

Any ideas on how to work around this?

Thanks,
Russell Sinclair

Russel,

I must say I'm not sure what exactly your problem is. At first, I thought it's threading/blocking issue just like in previous post

http://www.weonlydo.com/index.asp?forum=1&action=view&topic=1142637489

but now I see it could be something else. Did you set Allow = True inside UserConnecting event? Then what happened? What does it mean 'does not seem to work', you have to explain what's happening.

Just for a test - if you move either wodSSHTunnel or MySqlOdbc to another app, does it work? If it does, then you have blocking issue like in mentioned post.

Kreso

I did set Allow = True inside the UserConnecting event and put a Debug.Print to print a message to the immediate window. If I have another instance of Access running that uses the tunnel, it works great. I can see the message coming to the immediate window. However, it doesn't work within the current instance. No message is posted to the immediate window and Access seems to hang.

Basically, I'm using a startup form to setup the tunnel when the database is opened. This form contains all of the code to setup the tunnel and handle the UserConnecting event. However, it seems to get blocked on something as the application just hangs when I try to open one of the ODBC linked tables that is using the tunnel.

Does that help at all? I'd be happy to send a sample if you have a MySQL connection you can access over SSH.

Thanks for the quick reply.

Yes, this is blocking problem. ODBC drivers and wodSSHTunnel live in same thread. Even if they are put to separate threads, you have a problem, since VBA can accept events only in main thread, so wodSSHTunnel couldn't be able to fire them from different one.

When ODBC tries to connect, it blocks the thread. This causes wodSSHTunnel to freeze, because it cannot process (socket related) messages, and app completely hangs.

I tried to suggest possible workarounds in previous reply. This will NOT be easy for you - I think best would be if you could spawn new process for wodSSHTunnel or for ODBC. Or, if you have async ODBC drivers.

Kreso

Am I correct in saying that the only reason it is being blocked is because of the callback to the event for UserConnecting? If so, is there some way that you could enhance the product to bypass this event and just allow all connections through?

Of course, using an event-driven model may proclude this, in which case I understand and will have to figure something else out.

Nope. Usage of drivers that block the thread cause this - ODBC in this specific case. You need to move something to different thread, or to different process. This is almost exactly the same problem like in previous post

http://www.weonlydo.com/index.asp?forum=1&action=view&topic=1142637489

Hope I helped.
Kreso

Sorry to keep on about this but isn't this why I'm supposed to use the out of process EXE? Does it not run as a separate thread?

Yes, I agree, that was the idea why ActiveX EXE was made.

Perhaps you can send your code overhere, we'll try it out and see if we can figure something. Plz send it to techsupport@weonlydo.com

Kreso

Yes, I agree, that was the idea why ActiveX EXE was made.

Perhaps you can send your code overhere, we'll try it out and see if we can figure something. Plz send it to techsupport@weonlydo.com

Kreso

Whoops! Pardon me for jumping into this thread, I am working on similar problems, I too am trying to connect to Access over the weonlydo SSH tunnel. Do the problems you are talking about, with the ODBC drivers, also apply if instead of that I am using a Jet 4.0 provider for ADO.NET? Thank you, I will have to come back to this thread and continue looking into the problem tomorrow.

Rich Forman
Cove Hill Group

I can't say for sure, but I'm guessing that you will run into the same problem. The problem is that you need to establish the tunnel out-of-process and you can't do this if you're running the connection from within Access. One solution is to create an application that establishes the connection then opens access, or what we did is a shell to putty.exe passing necessary parameters to startup the ssh tunnel from a startup form.

I can't say for sure, but I'm guessing that you will run into the same problem. The problem is that you need to establish the tunnel out-of-process and you can't do this if you're running the connection from within Access. One solution is to create an application that establishes the connection then opens access, or what we did is a shell to putty.exe passing necessary parameters to startup the ssh tunnel from a startup form.

I have to admit I don't quite understand all this. I am using two separate apps on my client side, one a variation on the weonlydo sample apps for SSHTunnel, that creates the tunnel, and that seems to work. I use the channels.add method as described in the various code samples to create the port-forwarding to the Windows file share, so now the remote machine should be forwarded to my client's machine file-sharing port 139 if I understand correctly. Then I am attempting to actually USE the tunnel in another application that I am writing in VB.NET using ADO.NET. My idea, and it should work according to my understanding, is to open an ADO.NET connection object using a connection string where the path to the database is specified using the path: \127.0.0.2c$pathdbname.mdb. But it always bombs on the open statement ( Disk or network error ) - it is a separate app, therefore a separate thread from the one that created the tunnel; so I don't see why it encounters the thread-blocking problem being talked about.

Question: has anybody, including you guys at weonlydo, done this exact thing - use a tunnel, port-forwarding, file-sharing, and ADO.NET to connect to a remote .mdb - and can point me towards the exact code that will make it work? If so and I can see it work my boss will purchase the product right away!

Thanks
Rich Forman
Cove Hill Group

I just spoke with one of our Rails guys and he pointed out that what you are doing is actually different than the problem described here. You are trying to open a port to the database but Access is a file-based database system. You need to use SFTP to get to the file and work with it not SSH. And I'm afraid that at this point, my knowledge ends :(. Best of luck with this

Hi Rich,

First we need to find out what is cosing your problem and we will try than to resolve it.

I presume you are able to establish connection from wodSSHTunnel to wodSSHServer where Access database is.
After connection is establish you could have problem with permissions on servers machine , if you are limited user or firewall i turned on. Please try to check this.
Other problem could be with port 139. You will found explanation how to turn it on here (this link has explanation with picture and it will help you more that I can try to explain):
http://www.petri.co.il/what's_port_445_in_w2k_xp_2003.htm

Next problem could with binding to any loopback address other than 127.0.0.1.
This bug was introduced by XP SP2. For information on a fix, visit:
http://support.microsoft.com/default.aspx?scid=kb;[LN];884020

If all is done, here is code that you need to use in wodSSHTunnel:
---------------------------------
Option Explicit
Dim WithEvents SSH_1 As wodTunnelCom

Private Sub Command1_Click()
SSH_1.Hostname = your_server_name
SSH_1.Login = your_login
SSH_1.Password = your_password
SSH_1.Connect
End Sub

Private Sub ssh_1_Connected()
AddToList CONNECTED
SSH_1.Channels.Add LocalListen, 127.0.0.2 , 139, your_server_name , 139
SSH_1.Channels.StartAll
End Sub

Private Sub ssh_1_ChannelStart(ByVal Chan As wodSSHTunnelCOMLib.IChannel)
AddToList Channel start , remote port & Chan.RemotePort
End Sub

Private Sub ssh_1_ChannelStop(ByVal Chan As wodSSHTunnelCOMLib.IChannel, ByVal ErrorCode As Integer, ByVal ErrorText As String)
AddToList Channel stop & ErrorText
End Sub

Private Sub SSH_1_UserConnected(ByVal Chan As wodSSHTunnelCOMLib.IChannel, ByVal User As wodSSHTunnelCOMLib.IUser, ByVal ErrorCode As Integer, ByVal ErrorText As String)
AddToList User from & User.Hostname & connected
End Sub

Private Sub ssh_1_UserConnecting(ByVal Chan As wodSSHTunnelCOMLib.IChannel, ByVal Hostname As String, ByVal Port As Long, Allow As Boolean)
' allow anyone
Allow = True
End Sub
---------------------------------

Let us know how it goes.

Regards,
Drazen

I have to admit I don't quite understand all this. I am using two separate apps on my client side, one a variation on the weonlydo sample apps for SSHTunnel, that creates the tunnel, and that seems to work. I use the channels.add method as described in the various code samples to create the port-forwarding to the Windows file share, so now the remote machine should be forwarded to my client's machine file-sharing port 139 if I understand correctly. Then I am attempting to actually USE the tunnel in another application that I am writing in VB.NET using ADO.NET. My idea, and it should work according to my understanding, is to open an ADO.NET connection object using a connection string where the path to the database is specified using the path: \127.0.0.2c$pathdbname.mdb. But it always bombs on the open statement ( Disk or network error ) - it is a separate app, therefore a separate thread from the one that created the tunnel; so I don't see why it encounters the thread-blocking problem being talked about.

Question: has anybody, including you guys at weonlydo, done this exact thing - use a tunnel, port-forwarding, file-sharing, and ADO.NET to connect to a remote .mdb - and can point me towards the exact code that will make it work? If so and I can see it work my boss will purchase the product right away!

Thanks
Rich Forman
Cove Hill Group

OK, here's the simple answer: The netbios server listener on your local PC will listen to to every single available IP address on port 139. So you can't just use 127.0.0.2, because your server listener is already on that port.

Stop the server service on your local machine, and then open the tunnel. See if it works then.