Jump to content

SSHed MySQL tunneling with pub/priv keys


zerosignal0
 Share

Recommended Posts

Hey everyone,

I have a question about the process of creating a secure ssh tunnel between 2 servers to connect the localhost:3306 of the remote server to 3306 of our mysql database. I am familiar with ssh tunneling however I am not familiar with the process of creating the pub/priv keys and also getting the tunnel to be automatically brought up when the server reboots. Is there a chance that anyone could point me in the direction of a descent tutorial or maybe walk me through some of the process? Basically I have gotten SSH tunneling working on both servers with no problems but the idea of using pub/priv keys and scripting the process up on the remote end so that its secure is really what I could use help with. If it helps this remote server is actually going to end up being a appliance on a customers network so we are trying to keep it as secure as possible so that we dont have any prying eyes trying to sniff out the traffic on it.

Thanks,

G

Link to comment
Share on other sites

First off: http://sial.org/howto/openssh/publickey-auth/

Let's set up public key authentication. You'll need a copy of the key on each client to authenticate with the server, or in this case on your SQL server.. Basically with public key authentication, the client and server negotiate a key exchange, and if the public and private key modulus/sum up, then access is granted.

As for getting it started automatically, there are several ways to do that. Crontab usually supports the @reboot syntax, and this is included in the tutorial specified. you could add a script to /etc/rc.local for it to be executed upon startup. You might even be able to set it as a service in /etc/init.d/.

Hope this helps.

Link to comment
Share on other sites

First off: http://sial.org/howto/openssh/publickey-auth/

Let's set up public key authentication. You'll need a copy of the key on each client to authenticate with the server, or in this case on your SQL server.. Basically with public key authentication, the client and server negotiate a key exchange, and if the public and private key modulus/sum up, then access is granted.

As for getting it started automatically, there are several ways to do that. Crontab usually supports the @reboot syntax, and this is included in the tutorial specified. you could add a script to /etc/rc.local for it to be executed upon startup. You might even be able to set it as a service in /etc/init.d/.

Hope this helps.

Thanks for the response and the link. I had already seen the page but it has been one of the better tutorials that I have came across so far. I believe your on the same wavelength as me in terms of using cron or possibly /etc/rc.local for starting the task on boot as well. So now my next question is do you believe for security sake it may be a safer bet to allow for these clients to connect to another server on my dmz using this method and then forwarding through to the MySQL server or do you believe that it wouldnt really benefit me all that much?

EDIT: I have another question as well. Using the method that you mentioned within the link how would I go about using a passphrase for me keys and still allow for the system to sustain and start the ssh session securely? Is there a way to script up the passphrase entry from an encrypted file or how would that work?

G

Link to comment
Share on other sites

So let's get this straight, you have a setup like this:

__________________________________________---Client 1

Remote.SQL -----(webbernet)--- CompanyServer <

__________________________________________---Client 2

...etc.

I would think that your information is pretty safe over the webbernet, but your LAN may have prying eyes, so you'll probably at least want encryption from all the LAN clients to the server. If you need to keep secrets from the government or the Remote.SQL server is not forward facing (directly connected to the internet, no router's inbetween) then you could setup seperate ssh keys for the two servers to use. If possible, I'd setup the client side encryption and then use wireshark to see if there's any SQL traffic anywhere. Then use that to decide on server-to-server encryption.

Password-less login is the purpose of public key authentication: http://linuxproblem.org/art_9.html And http://wp.uberdose.com/2006/10/16/ssh-automatic-login/.

Link to comment
Share on other sites

So let's get this straight, you have a setup like this:

__________________________________________---Client 1

Remote.SQL -----(webbernet)--- CompanyServer <

__________________________________________---Client 2

...etc.

I would think that your information is pretty safe over the webbernet, but your LAN may have prying eyes, so you'll probably at least want encryption from all the LAN clients to the server. If you need to keep secrets from the government or the Remote.SQL server is not forward facing (directly connected to the internet, no router's inbetween) then you could setup seperate ssh keys for the two servers to use. If possible, I'd setup the client side encryption and then use wireshark to see if there's any SQL traffic anywhere. Then use that to decide on server-to-server encryption.

Password-less login is the purpose of public key authentication: http://linuxproblem.org/art_9.html And http://wp.uberdose.com/2006/10/16/ssh-automatic-login/.

Yea after taking into account what you mentioned and doing some more thinking I dont believe we need to worry about a "ssh proxy" for the time being but maybe later in the future. Anyways I have now gotten the priv/pub keys installed on both servers respectively and can connect to the mysql servers ssh from the client system at a remote facility. Now the question is setting up ssh to automatically bring up the tunnel on the client when the system boots. I have another question in regard to connecting mysql on a local port on the "client" server. I cant seem to connect through the mysql CLI on my client machine even after ssh connection using the "-L 3306:localhost:3306" switch. Any ideas?

Link to comment
Share on other sites

Yea after taking into account what you mentioned and doing some more thinking I dont believe we need to worry about a "ssh proxy" for the time being but maybe later in the future. Anyways I have now gotten the priv/pub keys installed on both servers respectively and can connect to the mysql servers ssh from the client system at a remote facility. Now the question is setting up ssh to automatically bring up the tunnel on the client when the system boots. I have another question in regard to connecting mysql on a local port on the "client" server. I cant seem to connect through the mysql CLI on my client machine even after ssh connection using the "-L 3306:localhost:3306" switch. Any ideas?

NVM I believe I got it now. Apparently when you are forwarding mysql connections you have to actually specify the same address as you assigned the mysql server in the network bindings within the main config file.

Link to comment
Share on other sites

For automatic startup, I would go ahead and at least try editing /etc/rc.local to point to the login script, as it's already there and there's no additional software to install. The script shouldn't need root privileges, but I'm not sure if rc.local gives it that or not. Just trying not to abuse it too much. ;)

By the way, what software are we talking about here? And I would still go and wireshark as many places on the network you can think of to check, just to be safe. You might end up with a monkey pirate on your LAN.

Link to comment
Share on other sites

  • 2 weeks later...
For automatic startup, I would go ahead and at least try editing /etc/rc.local to point to the login script, as it's already there and there's no additional software to install. The script shouldn't need root privileges, but I'm not sure if rc.local gives it that or not. Just trying not to abuse it too much. ;)

By the way, what software are we talking about here? And I would still go and wireshark as many places on the network you can think of to check, just to be safe. You might end up with a monkey pirate on your LAN.

Just to end this topic with some info I actually ended up changing my application abit and just have my data encrypt through a ssled web service instead of utilizing the tunnel now. This ended up being easier from an approach of minimalizing ports that needed to be opened on customer networks. However I did still employee this at some level for tunneling control of these appliance through a single, secured port and it seems to be working great so far. Thanks for the help!

-Z

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...