DPY-6000: cannot connect to database. Listener refused connection. (Similar to ORA-12660)

[orafreak]

Hello,

I am trying to connect from a Deepnote notebook using:

! python -m pip install oracledb --upgrade
import oracledb
import os

with oracledb.connect(user="uname", password="pwd", dsn="localhost/servicename") as connection:
with connection.cursor() as cursor:
sql = """select sysdate from dual"""
for r in cursor.execute(sql):
print(r)

OperationalError: DPY-6000: cannot connect to database. Listener refused connection. (Similar to ORA-12660)

[orafreak]

I am working on a Deepnote Terminal window. From there, there is no issue in connecting via the SSH tunnel. This is evidenced by the fact that I am able to successfully connect from the Deepnote Terminal to my Oracle SQL Database and query the data (via the SSH tunnel) in my Oracle Cloud database (this is protected behind a private VPN).

The issue is also NOT with the cx_Oracle library. I am successfully able to use the cx_Oracle library when using python in the Deepnote terminal:

However, this does not work from within the Deepnote Notebook. This is because the Deepnote notebook does not recognize the environment variable LD_LIBRARY_PATH which is essential and points to the location of libclntsh.so

I set LD_LIBRARY_PATH as an environment variable as shown below:

I continue to get the error as the Deepnote Notebook is not able to recognize the LD_LIBRARY_PATH environment variable.

How do I set LD_LIBRARY_PATH before the process even starts?

[orafreak]

I tried using the oracledb method (thin mode) but I get the DPY-6000: cannot connect to database. Listener refused connection. (Similar to ORA-12660)

[orafreak]

I am using python-oracledb Thin mode and trying to connect to a database where the sqlnet.ora file is as under:

SQLNET.ENCRYPTION_SERVER=REQUIRED
SQLNET.CRYPTO_CHECKSUM_SERVER=REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER=(AES256,AES192,AES128)
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER=(SHA1)
SQLNET.ENCRYPTION_CLIENT=REQUIRED
SQLNET.CRYPTO_CHECKSUM_CLIENT=REQUIRED
SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256,AES192,AES128)
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA1)

I found a reference to the DPY-6000 at https://github.com/oracle/python-oracledb/blob/main/doc/src/user_guide/appendix_b.rst
which states that if the native network encryption is set to REQUIRED in the sqlnet.ora file the connection will fail with the DPY-6000 error.

What line of the sqlnet.ora file do I need to change? Would there be any other security impacts?

[anthony-tuininga]

You simply need to comment out the lines that say REQUIRED. I believe this would allow non-encrypted traffic between the client and the database to occur (assuming you don't use TLS) but @cjbj can provide more detail.

[cjbj]

• Commenting out the REQUIRED lines will mean no network encryption occurs. With Thin mode you could alternatively use SSL/TLS, but configuring it is a specialist topic you should ask about on DB and security forums.
• Similarly a Deepnote forum might be a better place to ask questions about setting LD_LIBRARY_PATH or running ldconfig if you have to use Thick mode because you need the 'native' network encryption that you have currently got configured.

[noa-no-h]

@anthony-tuininga and @cjbj, Could I jump in here to ask whether this is safe? I'm having the same problem and I'm not working with a sensitive DB at all but I still don't want to be doing something really stupid from a security point of view.

[anthony-tuininga]

If you're asking whether commenting the requirement for network encryption is "safe" -- that will depend on the network between the client and the server and whether you consider unencrypted traffic on it to be "safe". Everyone has their own definition of this so I can't answer that for you! If the data in the database can safely be shared with everyone on the network between the client and the server than unencrypted traffic should be just fine!

[orafreak]

I made the updates to the sqlnet.ora file (on the Oracle database server) as under:

ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/opt/oracle/dcs/commonstore/wallets/tde/$ORACLE_UNQNAME)))

#SQLNET.ENCRYPTION_SERVER=REQUIRED
#SQLNET.CRYPTO_CHECKSUM_SERVER=REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER=(AES256,AES192,AES128)
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER=(SHA1)
#SQLNET.ENCRYPTION_CLIENT=REQUIRED
#SQLNET.CRYPTO_CHECKSUM_CLIENT=REQUIRED
SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256,AES192,AES128)
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA1)

Now I get a different erorr:

DPY-6005: cannot connect to database. Connection failed with "[Errno 99] Cannot assign requested address"

[cjbj]

Sounds like your connect string isn't valid, or there is a network configuration problem. You can google 'Cannot assign requested address' for many comments and tips.

[orafreak]

The SSH tunnel was not up. Once I re-started the tunnel, it worked fine. Thank you Anthony and Chris for your prompt inputs.