CODEWITHSUNDEEP
javaoracle-databaseportforwarding
MadBrad
MadBrad

Reputation: 31

How do I use port forwarding to connect to an Oracle database using netsh?

I have a machine(A) that has access to the host running the Oracle listener. I can connect to it fine using SQL Developer.

I have another machine(B) that I would also like to make a connection to the same database but its doesn't have direct access to the host running the listener. However, it does have access to machine (A).

I would like to forward a port on machine A to the Oracle listener host which is listening on port 1521.

Then I should be able to make a database connection from machine B to machine A and then forward a port to the host that has the Oracle listener. Sort of a man in the middle.

However, I'm running into issues. I can't even connect to the Oracle database from machine B using the forwarded port. I have experience using ssh tunnels to do the same thing. In this case, I'm not using ssh and I don't understand why this will not work. Below is the procedure I'm going through: Log into machine B - Oracle client is installed. SQL Developer is installed. Open SQL Developer - Select TNS connection. Enter in credentials. Connection Successful TNS Names entry:

CRYSTAL=(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=yes)(FAILOVER=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=testhost.com)(PORT=1521)))(CONNECT_DATA=(service_name=svc1)))

Now setup forwarding:

C:\Windows\system32>netsh interface portproxy add v4tov4 listenaddress=127.0.0.1
 listenport=1521 connectaddress=testhost.com connectport=1521
protocol=tcp

If I telnet localhost 1521 , it is able to make a connection. As a simple test, I make sure I can connect on Machine B using SQL Developer. But this time, I set the connection type to advanced so I can enter the jdbc url.Note, I use 127.0.01 instead of testhost.com because it will be forwarded to testhost.com

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=yes)(FAILOVER=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))(CONNECT_DATA=(service_name=svc1)))

This connection fails with "ORA-12516, TNS: listener could not find available handler with matching protocol stack"

I've checked for firewall issues and made sure port 1521 is open.

Any ideas what I might be missing or has anyone else had success forwarding a port to an Oracle listener?

Upvotes: 1

Views: 5944

Answers (2)

MadBrad
MadBrad

Reputation: 31

Thanks to the comment from psaraj12, I knew my approach should work. The problem I was having was due to the host name I was given to connect to the database. It works fine if you enter it into SQL Developer as is. However, when I used it in the port forwarding, I could not connect to the database using SQL Developer using the host name localhost. The host name I was given had "scan" in the name. I think this is a Virtual IP. We use Oracle RAC which is a clustering technology. So I suspected to that the VIP selected a node on the cluster and the forwarder could not handle it. So this is how I solved the problem:

  • Installed Wireshark.
  • Start recording packets.
  • Used SQL Developer To Connect with the VIP - Success
  • In Wireshark, find the TCP Stream that contains something unique about the connection. In this case, the Oracle service name. You should see the IP of the VIP. Something like:

    .........6.,.A ...O........:..............................(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=11.3.68.171)(PORT=1521))(CONNECT_DATA=(CID=(PROGRAM=SQL Developer)(HOST=jdbc)(USER=adpc))(SERVICE_NAME=svc1)(CID=(PROGRAM=SQL Developer)(HOST=jdbc)(USER=adpc)))).................6.,.A ...O........:..............................(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=11.3.68.171)(PORT=1521))(CONNECT_DATA=(CID=(PROGRAM=SQL Developer)(HOST=jdbc)(USER=adpc))(SERVICE_NAME=svc1)(CID=(PROGRAM=SQL Developer)(HOST=jdbc)(USER=adpc)))). .......6.A ........ ..........................

  • Now, go back to the packet and find out what the "real" address is. Here is an example: Wire shark packet information. This is the destination IP where Oracle listener is.
  • Now use this to forward the port. netsh interface portproxy add v4tov4 listenport=1521 connect address=11.3.68.135 connectport=1521 protocol=tcp
  • Now, when you log in using SQL Developer Basic Connection Type, you can use localhost instead of the VIP and you should be able to connect.
  • Now, go to another machine that has access to the machine you just set up and you should be able to connect to the database from there as well using the IP of the machine where you have port forwarding setup.

Upvotes: 2

psaraj12
psaraj12

Reputation: 5072

The same forward connection is working fine in both basic and advanced connection in SQL developer in my machine.

The only difference is machine A,B and Database are in the same network in my case where B can directly connect to the database also

Steps

1)You have to set up forwarding in machine A with machine A ip address as the listen address

2) Use the machine A ipaddress in TNS to connect to Database in machine B

If still, you are facing problems then you can try enabling log and trace files to identify the issue as mentioned in these articles

Oracle Database 10g Debugging Connection problems 1

Oracle Database 10g Debugging Connection problems 2

Upvotes: 0

Related Questions