Sunday, 3 May 2015

SQLcl - Cloud connections via Secure Shell tunnels

We're always trying to make SQLcl easier to connect to your database, whether its at your place or in the cloud.  So, one other thing we have added to enable you to drill into your cloud databases is an SSHTUNNEL command.  Lets take a look at the help for it, which you can get as follows.

SQL> help sshtunnel
SSHTUNNEL
---------

Creates a tunnel using standard ssh options 
such as port forwarding like option -L of the given port on the local host 
will be forwarded to the given remote host and port on the remote side. It also supports
identity files, using the ssh -i option
If passwords are required, they will be prompted for.

SSHTUNNEL <username>@<hostname> -i <identity_file> [-L localPort:Remotehost:RemotePort]

Options

-L localPort:Remotehost:Remoteport

Specifies that the given port (localhost) on the local (client) host is to be forwarded to 
the given remote host (Remotehost) and port (Remoteport) on the remote side.  This works by 
allocating a socket to listen to port on the local side.
Whenever a connection is made to this port, the connection is forwarded over 
the secure channel, and a connection is made to remote host & remoteport from 
the remote machine.

-i identity_file
Selects a file from which the identity (private key) for public key authentication is read. 


SQL> 


So for this to work we need to decide which ports locally we are going to use and which remote machine and port we want to use to map our ports from local to remote.  We also need a RSA file from the target host.  In this example, we have created one with the default name of id_rsa.  

The format of the flags follow the standard ssh rules and options, so -i for identity files and -L for port forwarding.  Heres an example connecting to a remote host via a tunnel.

(bamcgill@daedalus.local)–(0|ttys000|-bash)–(Mon May 04|12:16:46)
(~/.ssh) $sql /nolog

SQLcl: Release 4.1.0 Release Candidate on Mon May 04 00:16:58 2015

Copyright (c) 1982, 2015, Oracle.  All rights reserved.


SQL> sshtunnel bamcgill@gbr30060.uk.oracle.com -i ./id_rsa -L 8888:gbr30060.uk.oracle.com:1521

Password for bamcgill@gbr30060.uk.oracle.com ********
ssh tunnel connected

SQL> connect barry/oracle@localhost:8888/DB11GR24
Connected

SQL> select 'test me' as BLRK from dual weirdtable

BLRK  
-------
test me


SQL> 


You can download SQLcl from OTN here and give this a try when the next EA is released.

5 comments:

martin c said...

just a note for 12.0.1.2 developer day vm from here:

http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html

everything is already set up (vm network is nat with 1521 port forwarding) , except you need to enable networking inside vm linux, which is on top right corner – just click on network icon and enable connection – then you’ll be able to connect from the host

took me while, playing with linux firewall and vm networking which was not needed

martin c said...

sorry wrong post :)

Enrico Spinielli said...

It is very interesting how SQLcl is evolving...
On the SSH side I have a use case which is baffling me:

* Oracle DB running on SERVER, listening on TCP 1521

* Connection to PE5 from WINDOWS_PC to SERVER via SQLcl/SQL Developer using
USER/PWD@//SERVER:1521/PE5
works fine.

Now comes the SSH-thing:
* from WINDOWS_PC SSH remote port forwarding via cloud host, CHOST, using PUTTY, equivalent to
ssh -R 4567:SERVER:1521 CHOST

* from MAC_LAPTOP SSH local port forwarding via CHOST:
ssh -L 7654:localhost:4567 CHOST

* SQLcl from MAC_LAPTOP:
$ sql USER/PWD@//127.0.0.1:7654/PE5
does not work

The whole SSH port forwarding setup with initiation a shell session works so it looks like the DB protocol is the issue.

Any suggestions? (searching for similar problems didn't bring any answer)

Thanks a lot in advance
Best regards
Enrico

Sridevi Koduru said...

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

Blogger said...

DreamHost is ultimately one of the best hosting company for any hosting services you require.