Hello !
i’m going to start working on a rackspace cloud database, problem is that the database is not directly accessible from a remote point, not even with SSL, guys from the support team told me to do a SSH connection to our server and from that talk directly to the database, but i don’t know how to do that !
Does someone have a hint ?
Thanks.
Mac or Windows?
If they allow SSH Tunnel, you should be able to do this: see attached link: http://quintagroup.com/services/support/tutorials/mysql-linux
That’s where I was heading. On Mac/Linux, you can use ssh, but on Windows the user will have install plink.exe, or you’ll have to include it with the app.
This works, but remember to tear down the tunnel when you’re done. And it should be considered a last resort if the provider can’t implement the native MySQL encryption which is much, much cleaner and easier.
Hello guys, thank you so much for your help and sorry if i reply only now… had to deal with a terrible flu !
Think i have missed a very important piece of information unfortunately… i need this within Xojo, so i should be able to do a ssh connection within the app and then (in some way) tell the database class to use that specific tunnel to connect to the MySQL server…
Within Xojo, use a Shell to open the tunnel. With the tunnel open, all traffic on that port will go through it until you close the tunnel again. The MySQL object doesn’t have to know anything about the tunnel.
Are there any examples of how to do this anywhere? I’m a bit stuck here too. I’m pretty good at SQL and programming in general but this kind of low-level networking stuff is a bit over my head I’m afraid to say.
I have my app working great on a copy of the company’s MySQL DB running on XAMPP but conecting to the ‘live’ one (on AWS) has me a bit stumped. For day to day stuff I usually connect to it with Sequel Pro on OSX with SSH and a .pem key.
Ask Kem Tekinay, Mike Cotrone or Jon Ogden - they all have published classes on this.
https://forum.xojo.com/5175-ssh-telnet-control
I don’t remember where to find the other examples, you must contact the gentlemen directly
Once you have ssh access to your server then you can use the mysql command line utility:
So, if i understand everything correctly, once you have your shell working you have to go with MySQL commands to use the database… you cannot use the built-in MySQLCommunity class of Xojo.
You will use the MySQLCommunity class as you always do. You would setup your ssh tunnel to connect the remote MySQL port ( usually 3306 ) to local port 7777 ( this can be any port not in use on your computer ).
Your MySQL class would the connect to:
Host = 127.0.0.1
Port = 7777
Once connected proceed as usual.
Here’s a fairly decent tutorial on SSH Tunneling
http://www.augustcouncil.com/~tgibson/tutorial/tunneling_tutorial.html
The implementation that I saw used ssh keys rather than a password to establish credentials.
Matteo (and anyone else who may be looking to solve this) :
This (partial) solution is probably very specific to my requirements but if it gives someone else a pointer in the future too I’ll be happy with that :
So, I have my pem key in my user Downloads folder in OSX, so in Terminal I enter
“ssh -v -N -L 7777:127.0.0.1:3306 -i ~/Downloads/mydomain.pem ubuntu@mydomain.com”
Then as Johnny Harris said just use a normal MySQLCommunity class and use port 7777 to connect
Although I don’t need to do it just now where I am in developing what I’m doing, I guess I could run this as a shell command in Xojo at app startup?
I should add as a “hold my hands up admission of ignorance” that I don’t fully understand this in its minutest detail, only on a broader level. My new years resolution is to learn more Unix/bash - that can only be a good thing.
Thanks a lot for your example @Richard Brown , right now i have almost solved my problem using the OS shell and then connect to MySQL with Xojo as always… last step with i’m currently struggling is understanding how to pass the ssh password in the shell command within Xojo 
Is setting up keys to avoid the password an option? That’s how we did it.
Currently looking into that with the provider… hope it will be an option !
Hi all, and Ciao Matteo
I know a long time has passed since this thread has started, but just today I had the same problem and found this thread…
anyway, at least I solved this way:
make the shell interactive ( type 2 )
in the DataAvailable event of the shello, check for the password request string, and send the password.
dim s as string
s = me.ReadAll
if left(s,8) = “Password” then
me.Write(“put_here_the_password”)
me.Write(Chr(13))
end if
always in the DataAvailable event, check for the ssh welcome message, and once got the welcome I’m ready to go on with the database connection.
maybe not the most elegant solution, but it’s working for me… ( on Mac OS X )
regards,
GiulioGiulio - you might want to have a look at this thread too https://forum.xojo.com/18967-shell-novice-help-needed-osx
William Yu spotted a glaring error of mine that had me stuck for ages.