Unable to connect to remote oracle instance

nalsram commented 7 months ago

Connection to local oracle db (19c) instance works perfectly fine. However, when i try to connect to remote oracle instance (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production), Jamdb timeouts. However, using the same credentials as i am giving to jamdb, i am able to connect to remote oracle db via db_beaver

I went through all the relevent issues i could find, before creating this issue. Following is what i've tried

Added connection parameters parameters => [{sdu, 16384}, {read_timeout, 3000}]
Increased timeout to 3000
Tried connecting to Host/Port using gen_tcp:connect/3, it returns an ok tuple

Added io:format to src/jamdb_oracle_tns_decoder.erl file.

decode_packet(Err,Len) ->
    io:format("~ts~n", [Err]),
    erlang:display(Len),
    erlang:display("Error Match"),
{error, socket}.

I am not sure if i am doing anything wrong here, but the io:format prints an empty line, and only the above error match works. Also added erlang:display at other places, i believe jamdb gets stuck at handle_login, as i dont see the auth function being called.

I was unable to capture wireshark logs, as the remote db is behind a vpn and the traffic is https.

This is the error log i get, it says socket close.

00:05:58.159 [error] Jamdb.Oracle (#PID<0.907.0>) failed to connect: ** (DBConnection.ConnectionError) {:socket, :closed}                                                 
00:05:58.161 [error] Jamdb.Oracle (#PID<0.912.0>) failed to connect: ** (DBConnection.ConnectionError) {:socket, :closed}                                                 
00:05:58.162 [error] Jamdb.Oracle (#PID<0.910.0>) failed to connect: ** (DBConnection.ConnectionError) {:socket, :closed}                                                 
00:05:58.162 [error] Jamdb.Oracle (#PID<0.911.0>) failed to connect: ** (DBConnection.ConnectionError) {:socket, :closed}                                                 
00:05:58.199 [error] Jamdb.Oracle (#PID<0.909.0>) failed to connect: ** (DBConnection.ConnectionError) {:socket, :closed}                                                 
00:05:58.200 [error] Jamdb.Oracle (#PID<0.906.0>) failed to connect: ** (DBConnection.ConnectionError) {:socket, :closed}

Also, one thing i observed, in db_beaver, the role for user is set to Normal, I even tried passing the role parameter, setting it to 0. 1, 2, as in test/jamdb_oracle_dba.erl the value for role is an integer (i asssume 1 is for sysdba), but had no fruitful results.

How can i debug this further?

vstavskyi commented 7 months ago

Add erlang:display to src/jamdb_oracle_conn.erl after L139 Resend usually is the first server response.

Did you try to connect with JDBC?

nalsram commented 7 months ago

Right, let me try this. Will update here. Yes, I am able to connect successfully, with dbeaver as client, it uses oracle jdbc drivers for connecting. Also, as I mentioned the instance is behind a VPN, could it be the cause of issue. I tried wireshark but could not find the TNS packet, maybe I did something wrong there, will try that again too.

vstavskyi commented 7 months ago

Try different filters in wireshark for jdbc and jam. There must be something.

vstavskyi commented 7 months ago

Also see my reply about SSL connection

nalsram commented 7 months ago

@vstavskyi in this the file you have mentioned for the patch does not exist. I tried other branches too but could not find this ssl_gen_statem.erl file.

vstavskyi commented 7 months ago

OTP link

jaman commented 3 months ago

I have a similar issue with connecting to oracle instances. Trying to work out why. In my setup, I connect to say, a handful of connections in QA, as well as connections in Prod The QA connections are of the format: QADB [hostname: hostname.domain, database: database.uri.com, username: qadb_ro] sqlplus -> qadb_ro/password@database.uri.com The Prod connections are PRODDB [hostname: hostname.domain, database: PRODDB, username: proddb_ro] sqlplus -> proddb_ro/password@proddb

Sqlplus works, and OraLixir works (has other, show-stopping, issues like not being able to disconnect/doesn't always release cursors). When I attempt with Jamdb.Oracle, the QA instances work fine, while the Prod connections get the "failed to connect" message.

Wondering if there is any logging I can add to dbconnect, or anywhere else to get more insight into why the same connection that works in OraLixir fails in Jamdb. The only change between jamdb.Oracle and Oralixir is that 'database' in jamdb is 'service_name' in oralixir. Otherwise, all other passed in parameters are identical. The only visible difference I can see between QA and PROD has to do with 'database': database.uri.com vs PRODDB.

Thank you.

kjellwinblad commented 3 weeks ago

Do you know of any successful attempt to make SSL work together with OTP 26 or later?

I unsuccessfully tried the patch for OTP 25 on:

OTP-25.2.3 OTP-25.3.2.12

The following command gets stuck after the patch has been applied:

ssl:connect("google.com", 443, [{verify, verify_none}]).

so the patch is unlikely to work on OTP 26.

Is the patch needed because OTP's SSL implementation is buggy or is it because Oracle is using SSL in a strange way? If it is the former, would it be possible to send a PR with a fix to Erlang/OTP so we don't need to keep patching this forever?

(I also commented on this in another issue https://github.com/erlangbureau/jamdb_oracle/issues/98 but that issue was closed so I thought it was better to write here as well.)

erlangbureau / jamdb_oracle

Unable to connect to remote oracle instance #167