Solved

Remote MySQL using SSH

Posted on 2016-09-10
11
60 Views
Last Modified: 2016-09-10
Hi,

Can anyone please provide some guidance notes on how to setup secure remote access to a MySQL server running on a private Windows Server 2012 R2 server?  I think SSH is the way.  I'm using Navicat to administer.

Thanks
0
Comment
Question by:wayneinuk
  • 5
  • 5
11 Comments
 
LVL 51

Assisted Solution

by:Julian Hansen
Julian Hansen earned 250 total points
ID: 41792798
You want to read up on SSH Tunnelling. I am not familiar with Navicat but a quick search brought up this page which discusses how to setup an SSH tunnel on Navicat
https://www.navicat.com/manual/online_manual/en/navicat/rv_manual/SSHSettings.html
0
 
LVL 76

Expert Comment

by:arnold
ID: 41792869
ssh -t -L 3306:localhost:3306 user@remotehost


This establishes a connection without opening terminal, -t
-L setup local tunnel, Localport:remotehostname:remoteport
In the example I used localhost for the remote host since you indicated the MySQL is running on the server.

If you are using an ssh gateway to and through which, then the remote host will need to be the remote hostname that the ssh gateway will be establishing/completing the tunnel to.

MySQL localhost:3306 will go through the ssh tunnel, and will connect to the localhost on the remotehost port 3306.
Ssh, could mean any ssh application most follow the same
.......

You've provided very little detail, do you already have an ssh server configured/running on the Windows 2012?

You could look at Cygwin to setup in tge server with running ssh server........
0
 

Author Comment

by:wayneinuk
ID: 41792870
Hi,

Thanks for your comments but wont I need some SSH software installed on the server and port 22 open?
0
 
LVL 76

Expert Comment

by:arnold
ID: 41792878
Yes, this is why I suggested cygwin.

The other option you initiate an ssh connection from the server reversing the tunneling from local, to remote which has the same effect as far as the remote user is concerned.
Initiated from the Windows server to the remote Linux gateway
Using -R instead of -L.
0
 

Author Comment

by:wayneinuk
ID: 41792910
Hi,

Sorry I missed that bit.  I simply have a MySQL server in the office running on Windows 2012 R2 connected to the Internet via broadband and I want to be able to connect using Navicat, Toad or MySQL Workbench from my Windows 7 PC at home to administer the DB and make changes to the schema.

I do not have a Linux server.

Thanks
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 76

Expert Comment

by:arnold
ID: 41792980
To connect from your home to anything at work, you have to have a VPN connection to your work.
If at work you have a gateway server running SSH to which you can connect remotely, you could use this server with SSH tunnel to setup a VPN like connection.

HOme computer <=> ssh connection with Tunnel <=> office Linux Server

In this scenario, you should look at whether your office router has a VPN functionality, failing that, you could setup VPN services (NPS) on your windows Server 2012 and configuring the firewall to pass the ports to the windows 2012 server.
Once the VPN connectio (VPN, SSL VPN) are established from your computer to the remote server, you will be able to access your office LAN from your home computer.........
0
 

Author Comment

by:wayneinuk
ID: 41792989
Hi Arnold,

Thanks for the info, the only reason I wanted to go SSH was that I also wanted to open up the MySQL server for a couple of developers without giving them access to the whole network which the VPN route would give.

Thanks
0
 
LVL 76

Expert Comment

by:arnold
ID: 41793014
Actually, ssh would give a wider access than a VPN that can be configured on a per user basis allowing specific IPs,ports to ve accessed.
Once one has an ssh connection, they can using tunneling .......access .......
You could setup Cygwin or the like on the server. Then in the configuration make sure to disable tunneling such that anyone connecting via the ssh connection can only connect to localhost which means your MySQL int ce must be available on the localhost......

On the external firewall you would open a port that they would use which will be forwarded to this server..

For more security, you might want to consider using ssh keys, no password authentication.....
0
 

Author Comment

by:wayneinuk
ID: 41793024
Thanks Arnold,

I don't suppose there are any handy guides you know of to help me?

Thanks for all your help :-)
0
 
LVL 76

Accepted Solution

by:
arnold earned 250 total points
ID: 41793028
Help you with?   "Ssh tunneling" "ssh configuration" ref OpenSSH FAQ could provide insight.......

Get Cygwin onto your computer and tinker/practice on that.......
0
 

Author Closing Comment

by:wayneinuk
ID: 41793031
Thanks for all your help.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Viewers will learn how to properly install and use Secure Shell (SSH) to work on projects or homework remotely. Download Secure Shell: Follow basic installation instructions: Open Secure Shell and use "Quick Connect" to enter credentials includi…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now