connection to an oracle database though a SSH secure shell

Follow / 29 Jun 2010 at 7:08pm
Has anyone been able to access an oracle database with SSH using Toad for Oracle?

       

Replies

  • Follow / 30 Jun 2010 at 11:20am

    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

    --

    Cheers,

    Norm. [TeamT]

  • Follow / 30 Jun 2010 at 11:45am

    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

    --

    Cheers,

    Norm. [TeamT]

  • Follow / 30 Jun 2010 at 5:42pm

    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

    --

    Cheers,

    Norm. [TeamT]

  • Follow / 30 Jun 2010 at 5:42pm

    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

    --

    Cheers,

    Norm. [TeamT]

  • Follow / 30 Jun 2010 at 6:13pm

    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)

  • Follow / 30 Jun 2010 at 6:21pm

    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

    --

    Cheers,

    Norm. [TeamT]

  • Follow / 30 Jun 2010 at 6:31pm

    > 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...

  • Follow / 30 Jun 2010 at 6:51pm

    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

    --

    Cheers,

    Norm. [TeamT]

  • Follow / 30 Jun 2010 at 10:45pm

    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]

    --

    Cheers,

    Norm. [TeamT]

  • Follow / 6 Jul 2010 at 9:48pm
    image001.png

    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?

  • Follow / 6 Jul 2010 at 10:06pm

    > 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.

  • Follow / 18 Jul 2010 at 9:14pm

    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]

    --

    Cheers,

    Norm. [TeamT]