Connect to Oracle on an encrypted port using sqlalchemy-CodePudding

we have been using sqlalchemy successfully to connect to Oracle. Just now our organization is moving to encrypted Oracle database and we have been asked to switch to the encrypted database.

The sample code given to me by the database engineering team is which uses cx_Oracle directly is:

import cx_Oracle
dsn = """(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcps)(HOST=test_host)(PORT=1531)))
(CONNECT_DATA=(SERVICE_NAME=test_service)))"""
connection = cx_Oracle.connect(user="test", password="test", dsn=dsn, encoding="UTF-8")

However, when I try to connect to the database using sqlalchemy using : oracle cx_oracle://test:test@test_host:1531/test_service

I get an error : (cx_Oracle.DatabaseError) ORA-12547: TNS:lost contact\n(Background on this error at: http://sqlalche.me/e/13/4xp6)

I suspect that it is the protocol tcps that needs to be set.

I tried the following connection string : protocol_url = 'oracle cx_oracle://test:test@test_host:1531?service_name=test_service&protocol=tcps'

I get the following error : ValueError: invalid literal for int() with base 10: '1531?service_name=test_service&protocol=tcps'

Is there a way to use sqlalchemy to connect to Oracle on an encrypted port?

EDIT : I went through the steps listed in Python connect to Oracle database with TCPS I still get the error : sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-12547: TNS:lost contact

NOTE: I know that I can successfully connect to Oracle with encryption using a direct cx_Oracle connection.

CodePudding user response：

In order to use oracle encryption with sql alchemy the following needs to be done :

a) Follow the instructions to set up your client machine as described in the answer to the question : Python connect to Oracle database with TCPS

b) Then use the DSN as specified in the answer to this question : Using Oracle Service Names with SQLAlchemy

For my part, I needed to do both a) and b). Some of you using sqlalchemy may already have specified the DSN in the needed format. engine = create_engine("oracle cx_oracle://:@(DESCRIPTION = (LOAD_BALANCE=on) (FAILOVER=ON) (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = devdb)))")

The main thing here is that the DSN format I was using :
oracle cx_oracle://:@:/test_db does not allow the protocol to be specified as TCPS which is also required.

CodePudding user response：

Since you're using Python/SQLAlchemy, you can use create_engine. My Python code constructs the connection string then calls create_engine like this:

dsnStr = cx_Oracle.makedsn(config[args.tier]['oracle_host'], config[args.tier]['oracle_port'],config[args.tier]['oracle_sid'],
    config[args.tier]['oracle_service'])
dsnStr = dsnStr.replace('TCP', config[args.tier]['oracle_protocol'])
oracle_conn_str = "oracle://"   config[args.tier]['username']   ":"   config[args.tier]['password']   "@"   dsnStr
engine = create_engine(oracle_conn_str, connect_args={ "encoding": "UTF-8", "nencoding": "UTF-8"})

In my code, I read a config file (via configparser) that specified the oracle_protocol to be TCPS.

dsnStr for me was:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=REDACTED)(PORT=1923))(CONNECT_DATA=(SID=REDACTED)(SERVICE_NAME=)))

In the end, oracle_conn_str for me was:

oracle://rumali:REDACTED@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=REDACTED)(PORT=1923))(CONNECT_DATA=(SID=REDACTED)(SERVICE_NAME=)))