Using VBA to SSH to MySql

Ultimate Goal:
Using MSAccess VBA to connect to MySql through an SSH tunnel, and run SQL statements against a MySql database. Note: I will be redistributing this app.

Info:
I had a friend set up a remote virtual machine that host MySql.
He told me: “You should be able to SSH into this server and connect to the MySQL server locally. “

So, using VBA,  what’s the best way to setup an SSH connection and then provide SQL statements to the MySql. My guess is I first set up a SSH tunnel, then use an ODBC connection?
I was hoping to find an ODBC connector that would provide the SSH login feature built in but the latest version I found was ‘MySql ODBC 5.2’. it only  allows for SSL certificates but not an SSH connection.

This is the info my friend sent me (actual logins changed for this example):
OS: Ubuntu 13.04

SSH Server: www.Server.com 
SSH Port: 9950
Username: username
Password: Password1
 
MySQL Server: 127.0.0.1 (localhost)
MySQL Port: 3306
MySQL Username: root
MySQL Password: Password2

Note: I was successful connecting to MySql using “MySql Workbench” with the login info, so I know the he set everything up correctly on his end.

Any suggestions on the best approach?

Update:
He also told me : " It will do the port forwarding for you, no PuTTY needed."
askolitsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RobOwner (Aidellio)Commented:
Sounds like your friend is the best person to ask ;-)
They've said that putty is not required though they've expected you to use something to connect, did they give you any ideas?
Putty will connect and can forward ports for you. Then you connect to localhost to connect to the mysql db
0
RobOwner (Aidellio)Commented:
http://www.putty.org and look for tunnelier
0
Dave BaldwinFixer of ProblemsCommented:
Here are some simple directions from MySQL:  http://dev.mysql.com/doc/refman/5.5/en/windows-and-ssh.html  I do not know if they work.
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

RobOwner (Aidellio)Commented:
That's a good description of step by step what you need to do.
 I've done this before using putty and forwarding the ports then used the odbc to connect to localhost version of mysql.
I did all this external to work bench, excel out whatever was going to connect to the database. You run putty with the required config then you run your program you want to use top access the database such as workbench or excel
0
RobOwner (Aidellio)Commented:
Going back to my previous post you could run tunnilier from your vba code so that everything is controlled from vba but you would need to manually run it for work bench
0
askolitsAuthor Commented:
Actually I knew about Putty. But since my app will be distributed to various users and OS's,  I didn't want to include another app that the user would have to install. It just adds one more layer of possible compatibility issues with different OS's.

I checked out Tunnelier and also same issue as well as a limited licensing for distribution.

I did find one option that comes with Putty called PLink.exe. That I can distribute without licensing concerns. I can launch a command line through VBA and start the tunnel. I was able to get that to work.
For anyone else that reads this post, here is the command line that worked:

plink -L 3306:localhost:3306 -P 9950 -l <username> -pw <password>  <www.RemoterServerName.com>
(Note: The second port number '9950' is the remote server's port)


But I  still need to handle a few issues.
1) I need to figure out a way to monitor the tunnel in case it closes unexpectedly
2) Upon first time execution of PLink, it asks about whether to trust the connection. Since I plan to launch PLink 'hidden' through a VBA shell, it will get stuck waiting for a Yes/No answer because the user will never see the prompt.  Maybe I can send some Keystrokes to the PLINK command window. I many have to give that a try.

As Far as workbench is concerned, I am only using that for development and have no problem using their built in SSH connection parameters. So that works fine.

So, I've made progress. If anyone else has any other ideas , let me know. I will post my solution if I finally figure it all out.
0
RobOwner (Aidellio)Commented:
Sorry-.-it's been a while since I'd done this and I remember now using plink after extensive testing with putty as I also had the same issue with confirmation. I then used:
Pageant (an SSH authentication agent for PuTTY, PSCP, PSFTP, and Plink)
PuTTYgen (an RSA and DSA key generation utility).
This should step you through it http://the.earth.li/~sgtatham/putty/0.63/htmldoc/Chapter8.html#pubkey
I did find the message came up once but didn't after that once the certificate was confirmed. That may have to be part of your l installation process.
0
RobOwner (Aidellio)Commented:
This sets up a trusted connection so that you don't have to confirm if the connection is trusted each time
0
askolitsAuthor Commented:
I got it!

What I did was write code to create the tunnel using a shell command with Plink.exe. When asked if I want to store the key in Cache, I sent 'n' to the Plink window using "sendkeys".  Then the tunnel opened. This way the key is not stored in the registry.
I also wrote code to terminate the tunnel. In this way I can open and close the tunnel at will.  

When the tunnel is open, I open MSAccess and programmatically re-link the Access tables I need from MySQL using the ODBC driver. By linking programmatically, there is no DSN and I can hide the relinking parameters as well as password within a compiled module.

Here's the code I use to open and close the SSH tunnel using Plink. I didn't test it yet on Office 64 bit, but if there is a problem, I'll let everyone know. I will eventually modify it to handle multiple tunnels (plink.exe running twice - if there is such a thing)

If anyone see any issues, let me know.

John

Option Compare Database
Option Explicit

Public Const WM_CLOSE = &H10
Public Const INFINITE = -1&
Public Const SYNCHRONIZE = &H100000
Public Const STANDARD_RIGHTS_REQUIRED = &HF0000



#If VBA7 Then
    Declare PtrSafe Function lbf_ShellExecute Lib "shell32" Alias "ShellExecuteA" (ByVal hwnd As Long, _
    ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, _
    ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
    Declare PtrSafe Function SetForegroundWindow Lib "user32.dll" (ByVal hwnd As Long) As Long
    Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
    Declare PtrSafe Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
    Declare PtrSafe Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
    Declare PtrSafe Function TerminateProcess Lib "kernel32" (ByVal hProcess As Long, ByVal uExitCode As Long) As Long

#Else
    Declare Function lbf_ShellExecute Lib "shell32" Alias "ShellExecuteA" (ByVal hwnd As Long, _
    ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, _
    ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
    Public Declare Function SetForegroundWindow Lib "user32.dll" (ByVal hwnd As Long) As Long
    Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
    Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
    Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
    Declare Function TerminateProcess Lib "kernel32" (ByVal hProcess As Long, ByVal uExitCode As Long) As Long
#End If

Function TerminateTunnel()

Dim lngHandle As Long
Dim lngResult As Long

lngHandle = OpenProcess(SYNCHRONIZE Or STANDARD_RIGHTS_REQUIRED Or &HFFF, False, GetWindowHandle("PLINK.EXE"))
lngResult = TerminateProcess(lngHandle, 0)
lngResult = CloseHandle(lngHandle)

End Function
Function GetWindowHandle(strWindowName As String) As Long
Dim w As Object
Dim sQuery As String
Dim objAllProcesses As Object
Dim objProcess As Object

    Set w = GetObject("winmgmts:")
    sQuery = "SELECT * FROM win32_process"
    Set objAllProcesses = w.execquery(sQuery)

    For Each objProcess In objAllProcesses
        If objProcess.Name = strWindowName Then
            'Once you get the handle, you cannow exit to function and return the handle
            GetWindowHandle = objProcess.Handle
            'Debug.Print process.Name, process.Handle, process.Caption
            GoTo EXitThisFunction
        End If
    Next
    
EXitThisFunction:

    On Error Resume Next
    Set w = Nothing
    Set objAllProcesses = Nothing
    Set objProcess = Nothing
    
End Function


Function CreateSSHTunnelUsingPLink()

Dim strFilename As String, strCommandLine As String
Dim strCurrPath As String
Dim lngWindowHandle As Long
Dim strServerPOrt As Long
Dim strServerUser As String
Dim strServerPassword As String
Dim strServerAddress As String

strServerPOrt = 9900 'Sample POrt
strServerUser = "root" 'Sample User Name
strServerPassword = "MyPassword"  'Sample Password
strServerAddress = "www.MySqlServerLoaction.com"  'Sample web server


strCurrPath = CurrentProject.Path & "\"
strFilename = strCurrPath & "Plink.exe"
strCommandLine = "-L 3306:localhost:3306 -P " & strServerPOrt & " -l " & strServerUser & " -pw " & strServerPassword & "  " & strServerAddress

'The command line will look something like this
'  "-L 3306:localhost:3306 -P 9900 -l root -pw MyPassword  www.MySqlServerLoaction.com"

'Debug.Print strFilename
'Debug.Print strCommandLine


'Launch tunnel
Call lbf_ShellExecute(0, "open", strFilename, strCommandLine, "", 1)
'Change the last parameter from a one to a zero and the 
'connection window will be hidden. 

Sleep (1000)
SetForegroundWindow (GetWindowHandle("PLINK.EXE"))

'Send a 'n' (no) which means trusted certificte is not stored on users PC)
SendKeys "n + {ENTER}", True

'Tunnel is now created

End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RobOwner (Aidellio)Commented:
Looks good especially if it works ;-)
Curious to know why you wouldn't want to store the certificate on the current machine?
0
askolitsAuthor Commented:
I plan to sell my app to the masses and don't want to give anyone, any info, on anything about the coonection to the web server. Even though I know the key is encrypted and all, if I say yes to the cache quetion, it writes info to the registry. Which, btw, has my web server's address displayed. If I do it this way, no registry record. It's just one more level of security. My attitude is if someone wants in, they'll find away. But I'll make it as tough as possible.

The only hit I see is that it may take and extra second or two to build the tunnel. But a small sacrifice.
0
RobOwner (Aidellio)Commented:
Sure it's the lesser of all evils. I like your approach.
0
askolitsAuthor Commented:
I found the answer myself. Posted code for future users
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.