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
Spikeuk30Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior DBACommented:
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
0
Spikeuk30Author Commented:
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?
0
Daniel HelgenbergerCommented:
So I hope I get this right: Your SQL server is on Ubuntu and Windows wants to connect.
If so, I recommend creating a windws service with plink from the putty download page. Plink works like the ssh command on Linux basically. Don't forget to add the SSH key for the windows host to root's 'authorized_keys' on Ubuntu.

1. Test the plink command/settings from an interactive session on windows
2. If they work well use srvany.exe from Windows Resource Kit or something similar to create a custom service: http://support.microsoft.com/kb/137890

Here the man pages for plink, see section 7.2.2 Using Plink for automated connections:
http://the.earth.li/~sgtatham/putty/0.63/htmldoc/Chapter7.html#plink

You can of course use putty command line for the service; but IMHO plink is much more stable since you can define all the options via command line and it is designed exactly for this purpose.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Spikeuk30Author Commented:
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...
0
Daniel HelgenbergerCommented:
This is correct, I do this as common practice. I do not know for ubuntu in particular, but on other distros the default is to bind mysqld to localhost only after install. There the bind addess may be commented out to allow binding on any interface.
bind-address = 127.0.0.1

Open in new window


It seems to me there is still another mysql process runnung?
make sure with:
service mysql stop
ps axf|grep mysql

Open in new window

the last command should only display grep running and no mysqld.

If it is there, it is maybe dead? Kill it with:
kill <mysqldpid>
# or force-kill it:
kill -9 <mysqdpid>
service mysql start

Open in new window

0
Spikeuk30Author Commented:
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?
0
Daniel HelgenbergerCommented:
Though a pass phrase adds security, it is best practice not to use it to make this more stable. Use file system permissions to lock the key down so only 'system' and 'administrators' can read it.

Then use sc.exe to create the service:
sc create "ssh-tunnel" "c:\plink.exe -your -arguments"

Open in new window

This will add the new service in the registry. Just use regedit to modify parameters:
http://support.microsoft.com/kb/251192/EN-US

When the service is registerd, use the services MMC snap in to further set the service:
- set the restart behavior to restart at fist, second and other tries, and the startup behavior to 'automatic (delayed)' and start the service in the MMC, witch will start hopefully.

Check in task manager for plink and the local port forward.

And don't worry, ssh can handle short connection drops fine. If the ssh server goes down, plink will exit. Then windows service error handling should kick in and forever try to restart the service as you did setup earlier.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.