Toad World® Forums

connection to an oracle database though a SSH secure shell


#1

Has anyone been able to access an oracle database with SSH using Toad for Oracle?


#2

Morning Steven,

Has anyone been able to access an oracle database with SSH
using Toad for Oracle?
Yes, only this very morning in fact!

Assuming Windows as you wish to run Toad and assuming that you use
Putty, do the following:

  1. Open putty and on the session page, enter the name of a server and
    make sure SSH is checked. The server can be any server that you have a
    username and password to login with. I use one here called BLUEBIRD as I
    own it!

  2. On the connection->ssh->tunnels page, uncheck both options at the top
    (“Local ports accept …” and “Remote ports do the same”).

  3. Enter 9999 (or any port above 1024 as the Source Port.

  4. In the destination, enter the database host and port as per tnsnames.
    In my case, this is a server called GREENBIRD and a port of 1521. Enter
    this as server:port.

  5. As the port being forwarded is on your desktop, check the “Local”
    option. Leave “Auto” checked as well for the IP version.

  6. Click the Add button. You will see L9999 greenbird:1521 (your will
    differ) in the list of forwarded ports.

  7. Go to the session page again, Enter a name for your saved session and
    click save.

  8. Click open. Supply a username and password for the server (BLUEBIRD
    in my case). You will login a normal ssh session to the server named
    BLUEBIRD.

However, you also have an SSH Tunnel from localhost:9999 to
greenbird:1521 - in my case - so anything going to localhost:9999 will
be tunnelled over to greenbird:1521, where the normal listener is
waiting expectantly!

In your ORACLE_HOME/network/admin/tnsnames.ora file, add an entry for
your database. Mine is called perf10g, so I add one called perf10g_ssh,
as follows:

perf10g_ssh.WORLD =
(DESCRIPTION =
(SOURCE_ROUTE = OFF)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 9999))
)
(CONNECT_DATA =
(SID = perf10g)
(SRVR = DEDICATED)
)
)

Note the host and port, they are the ones I’ve used in the putty
session.

As long as your putty session is running, you can connect with Toad or
SQLPlus etc to username/password@perf10g_ssh and get forwarded to the
real database. Once you are connected, you can actually logout the putty
session. Putty will not exit until Toad subsequently logs out as well -
which is handy as you are not leaving an open ssh session to a server
running unattended.

Assuming you are using some other form of ssh system, then you need to
do the equivalent of:

ssh -L 9999:localhost:1521 greenbird

in order to open up a tunnel.

If you see a message about “no listener” when you try and connect, then
your tunnel is either not open, or has gone down for some reason.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk


#3

Sigh,

Assuming you are using some other form of ssh system, then
you need to
do the equivalent of:

ssh -L 9999:localhost:1521 greenbird

in order to open up a tunnel.

Please ignore the above. It’s most likely incorrect, wrong and complete
and utter nonsense! Sorry.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk


#4

Right, fixed it!

ssh -L 9999:localhost:1521 greenbird

As I mentioned, the above is rubbish! I have tested this out now and
using my previous explanation, if I run this at the DOS prompt, I get a
tunnel to my database listener:

ssh -L 9999:greenbird:1521 bluebird

I think Putty supplies a program called plink.exe which is just a
command line version of putty (and runs putty anyway!), so this also
works:

plink -L 9999:greenbird:1521 bluebird

You can, if desired add the username to the front of “bluebird” as
follows:

plink -L 9999:greenbird:1521 dba@bluebird

Now you only get prompted for the password. And finally, if you already
have ssh keys set up on the “bluebird” server under the “dba” user, then
you will authenticate automagically without being prompted for either a
user or password.

You could add the -N option for “no remote logon” before the -L option,
but it seems that putty/plink simply ignores it and attempts a login
anyway. It does, however, prevent the remote ssh session that opens from
accepting any user input.

9999 = the local port that you wish to forward to ssh.

greenbird:1521 = the remote server:port that all traffic to
localhost:9999 will be sent.

bluebird, or dba@bluebird is simply something to make ssh open a
session.

HTH

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk


#5

Right, fixed it!

ssh -L 9999:localhost:1521 greenbird

As I mentioned, the above is rubbish! I have tested this out now and
using my previous explanation, if I run this at the DOS prompt, I get a
tunnel to my database listener:

ssh -L 9999:greenbird:1521 bluebird

I think Putty supplies a program called plink.exe which is just a
command line version of putty (and runs putty anyway!), so this also
works:

plink -L 9999:greenbird:1521 bluebird

You can, if desired add the username to the front of “bluebird” as
follows:

plink -L 9999:greenbird:1521 dba@bluebird

Now you only get prompted for the password. And finally, if you already
have ssh keys set up on the “bluebird” server under the “dba” user, then
you will authenticate automagically without being prompted for either a
user or password.

You could add the -N option for “no remote logon” before the -L option,
but it seems that putty/plink simply ignores it and attempts a login
anyway. It does, however, prevent the remote ssh session that opens from
accepting any user input.

9999 = the local port that you wish to forward to ssh.

greenbird:1521 = the remote server:port that all traffic to
localhost:9999 will be sent.

bluebird, or dba@bluebird is simply something to make ssh open a
session.

HTH

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk


#6

Norm!

Right, fixed it!

Awesome stuff! I’ve looked on Asktoad.com before for something like this,
but couldn’t find it.

I suppose if some sort of knowledgeable TeamT member were to add it there,
that the search indexer could then find it.

Eh. Whatdya gonna do?

Rich – [TeamT]

Disclaimer: (nudge nudge)


#7

Rich,

Awesome stuff! I’ve looked on Asktoad.com before for
something like this, but couldn’t find it.

I suppose if some sort of knowledgeable TeamT member were to
add it there, that the search indexer could then find it.

Eh. Whatdya gonna do?
I suppose it would look good on the “HowTo” page? Hmm, I’ll see what I
can do. Seeing as I’m actually updating our own Wiki here at the moment,
I’m pretty certain that the format is identical …

Later!

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk


#8

I suppose it would look good on the “HowTo” page? Hmm, I’ll see what I
can do. Seeing as I’m actually updating our own Wiki here at the moment,
I’m pretty certain that the format is identical …

You Da Man, Norm! Thanks!!!

Rich – [TeamT]

Disclaimer: I’ve been drinking tea for years, preferring strong
(oversteeped) black tea breakfast varieties. But don’t tell Mark that I’ve
slipped back to the prepacked tea bags of silt instead of loose leaf…


#9

Rich,

http://asktoad.com/DWiki/doku.php/howto_use_toad_over_an_ssh_tunnel

It’s a new HOWTO!

Enjoy.

Cheers,
Norm. [TeamT]

Disclaimer: I’ve been drinking tea for years, preferring strong
(oversteeped) black tea breakfast varieties.
What we call builders tea - stewed to begorrah and beyond!

But don’t tell Mark that I’ve slipped back to the prepacked tea bags
of

silt instead of loose leaf…
Ok, my lips are sealed.

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk


#10

On 30/06/10 16:51, Dunbar, Norman wrote:

Rich,

http://asktoad.com/DWiki/doku.php/howto_use_toad_over_an_ssh_tunnel

It’s a new HOWTO!
And now it’s a new fixed HowTo. I missed a step out. Corrected it.

Cheers,
Norm. [TeamT]


#11

Rich, Rich, Rich.

If only a keyboard weren’t so poor an instrument to express the sorrow bordering
on pathos I could otherwise convey with my penmanship. I’m left with only the
crude tools of the emoticon, which any man over 30 must never use, according to
the Radio Shack team leader.

The past couple of months have seen my own switch, but at least Caribou’s are
large loose leaves in a large bag -

Disclaimer: Is it still a hijack if you reply to a disclaimer?
image001.png


#12

Rich, Rich, Rich.

Yes, yes, and yes?

If only a keyboard weren’t so poor an instrument to express the sorrow
bordering on pathos I could otherwise convey with my penmanship. I’m left
with only the crude tools of the emoticon, which any man over 30 must never
use, according to the Radio Shack team leader.

LOL ;o

The past couple of months have seen my own switch, but at least Caribou’s
are large loose leaves in a large bag -

The extra strong tea from my local grocer claims it’s from England – does
that help? Although I suspect that at the same price most charge for 18-20
bags, this 50-bag box from Taylors of Harrogate gets it’s strength by
maximizing surface area. And the easiest and cheapest way to do that is to
pack dust.

But it shore do pack a punch!

Disclaimer: Is it still a hijack if you reply to a disclaimer?

Yes. Disclaimers are not part of the body of an email, but instead
constitute a legal addendum whose purpose is to reduce the ratio of trial
lawyers to productive people. See “Burnbaum vs. Yahoo”.

Rich – [TeamLoopy]

Disclaimer: I’m not a laywer and I don’t play one on TV. Some content
altered for effect. Names may or may not have been changed. Viewer
discretion advised. Don’t start none, won’t be none.


#13

Evening Rich,

I’m back!

On 06/07/10 20:06, Rich Jesse wrote:

The extra strong tea from my local grocer claims it’s from England – does
that help? Although I suspect that at the same price most charge for 18-20
bags, this 50-bag box from Taylors of Harrogate gets it’s strength by
maximizing surface area. And the easiest and cheapest way to do that is to
pack dust.
Taylors of Harrogate is about 20 miles from where I live. it is indeed
famous in many parts of the world for its fine Teas and indeed, coffees.

Cheers,
Norm. [TeamTea]