How to gain direct access to SQL Server at Garching via SSH 1) Who and what is required
2) Getting through the Firewall 3) Setting up the ssh client 4) Register SQL server locally
4.1) If you have SQL Server Enterprise Manager installed
4.2) If you only have the sql server 7 ODBC driver and use another database manager program
1) Who and what is required
This document is written for the persons who need to be able to place data on the sql server efdasql.ipp.mpg.de at Garching. Members of the ITPA working groups can also be granted permission to retrieve data directly from this server. All others will have to use the web-based interface to retrieve data from the sql server.
Each of the persons that have been granted direct access to the sql server has been issued a personal userid (xxxxx in the following) and 2 personal passwords, a firewall ( PWf ) and a sql server (PWsql) password. This userid will allow a tunnel via ssh2 to be set up from the users computer to the server on which the sql server is running, ie. for the user it will look as if the sql server is local to his/her computer. The personal userid and the PWsql password has been given specific rights on the sql server that depend on the user.
The user needs to have the following: a) Preferably a Windows computer b) Web browser
c) SSH client that supports the ssh2 protocol d) SQL Server Enterprise Manager or/and
2) Getting through the Firewall
First of all make sure that you have permission to set up a SSH connection to the server efdasql.ipp.mpg.de server in Garching and that you are allowed to authenticate yourself on the web page https://194.59.170.1:950/
On this web page you type in your personal userid xxxxx and press Submit
The Firewall in Garching will respond with
The Firewall responds with
Press Submit once more and the response is
3) Setting up the ssh client
This example shows how to set up the SSH Secure Shell client, other ssh clients will need the same input.
a. Create a new Profile (here named efdasql - arbitrary) b. Under Connection:
Enter Host Name (efdasql.ipp.mpg.de)
Enter User Name (sshuser ), password to be obtained from DB-manager Port Number (22)
Authentication (Password)
Now switch to the X11 tunneling settings Check the box to enable secure X11 tunneling
Then click the Outgoing Tunneling page and create a new Outgoing Tunnel with these settings: Type (TCP), Listen Port (5100), Destination Host (localhost) and Destination Port (1433)
This how it looks when the Outgoing Tunnel has been created
You are now ready to connect with your new profile. When you try to connect the response will be
You have now established a tunnel through which you can communicate with the sql server efdasql.ipp.mpg.de. The name of the sql server is now localhost on your own computer. In order to use this connection you need to register the sql server localhost on your own computer. This is the subject of the next section.
Notice if the above window is closed the tunnel is terminated so remember to keep it running while you are working with the sql server.
4) Register SQL server locally
4.1) If you have SQL Server Enterprise Manager installed
In the same program group where the Enterprise Manager has been installed you will also find the Client Network Utility ( default Programs/Microsoft SQL Server 7.0/ ).
Pressing Add and entering the info shown below have created the highlighted line.
Now you are ready to start up the Enterprise Manager.
On the Console Root click on Action and choose New SQL Server Registration to get this screen
On the resulting screen you press Next
You now enter your personal userid and PWsql password and click next
You are now ready to click Finish
The Server responds with this message and you click Yes
4.2) If you only have the sql server 7 ODBC driver and use another database manager program
By setting up an ODBC Data Source you will also be registering the SQL Server. Start up the ODBC Data Source Administrator by double clicking on Data Sources
Select SQL Server driver and click Finish
Choose SQL Server authentication and type in your personal userid and password. Click the Client Configuration button.
You can now specify the default database you want to connect to, here Pedestal. Click Next
You can now click the Test Data Source button
