askolits
asked on
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."
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."
http://www.putty.org and look for tunnelier
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.
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
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
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
ASKER
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.
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.
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.
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.
This sets up a trusted connection so that you don't have to confirm if the connection is trusted each time
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Looks good especially if it works ;-)
Curious to know why you wouldn't want to store the certificate on the current machine?
Curious to know why you wouldn't want to store the certificate on the current machine?
ASKER
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.
The only hit I see is that it may take and extra second or two to build the tunnel. But a small sacrifice.
Sure it's the lesser of all evils. I like your approach.
ASKER
I found the answer myself. Posted code for future users
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