Link to home
Start Free TrialLog in
Avatar of Spikeuk30
Spikeuk30

asked on

SSH Tunnel to SQL - Windows / Ubuntu

Hi guys,

any help would be greatly appreciated....

We have two servers,

one local SQLBI server running Windows OS.

One external Ubuntu server running SQL....

The sqlbi server needs to speak with the SQL server over SSH so it can create reports from the sql data.

There is an odbc link from the SQLBI server to the Ubunutu server which works...im a little confused -  am i right in thinking currently the server is open to the world and its just fowarding on requests to the IP?
I think I need to create SSH port forwarding? and then reference localhost on the SQLBi server - how do I set this up?


thanks in advance
Avatar of David Todd
David Todd
Flag of New Zealand image

Hi,

I'm curious as to exactly which SQL you are referring to as to the best of my knowledge Microsoft SQL Server only runs on Windows.

Now if we're talking a recent version of MS SQL, it is possible to put a certificate on the SQL Instance, and then use SSH for the connection. I tried this some years back with SQL 2005, and a network monitor trace did show the difference before and after - after the data was encrypted.

HTH
  David
Avatar of Spikeuk30
Spikeuk30

ASKER

sorry its mysql on the ubuntu box.

Been reading some more and done some testing with Putty.

I can create a SSH tunnel, but obviously you need to keep putty open.. is there anyway to automate this connection and have it start auto upon reboot etc

can you turn this into a service?

When I connect to the tunnel in putty i need to specify the login credentials for the connection - should i be using the server root login details ? - or is that not best pratice?
SOLUTION
Avatar of Member_2_6582184
Member_2_6582184
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ok sounds like im on the right track.

I have generated a private and public key.

installed on the mysql ubuntu server and tested and got it working.

I can use plink and connect using ssh and the key etc and it maps the 3306 sql port across as local host.

The next bit is for me to secure the sql server, at the moment i can telnet to  publicipaddress:3306 and i get sql asking me for password.

I think if i change the my.cnf file to bind-address  127.0.0.1  this will allow SQL to only talk locally? - and so only my SSH connection will be able to talk to the SQL server.

is this correct? - is this what I should be doing to stop connections directly onto the serverip:3306?


I uncomented this line and tried to restart sql but getting an error:


InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process


Repeated, everntally it times out...   I tried commenting out the change and trying to restart sql again but same message...
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks that's perfect !!

Somehow its all working!!!

I am using putty for the SSH connection from W2k8 - the key I am using has a phassphrase.

Im guessing its best to use a passphrase for security?

The next step is for me to turn plink into a windows service?

I am using Pageant to handle the passphrase entry so its auto connect.

BUT.. its my understanding if the server reboots or pageant is restarted pageant will prompt for the initial passphrase?   how do I get round this?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial