Using python-oracledb / cx_oracle to connect to AWS RDS Oracle with TLS / mutual TLS

[tomwaldnz]

I'm trying to use the Python oracledb library to connect from AWS Lambda to RDS Oracle using TLS. I may want to do mutual TLS, but even one-way TLS would be fine. I could use some help. I'm technical and have plenty of experience, but both Python and Oracle are outside my core skillset - I'm good with Java and I'm full time AWS architect.

What I can do:

• I can connect to RDS Oracle using TLS with SQL Developer on my local PC. I had to add the AWS RDS 2019 root cert to the Java Keystore. I didn't have to use an Oracle wallet. The direct link to the AWS self signed root cert is here.
• I can connect unencrypted from Lambda Python 3.9 to Oracle RDS using python-oracledb in thin mode
• I can connect unencrypted from Python on my PC to Oracle RDS using python-oracledb in thin mode

Noting that this is not a firewall issue, I have opened up the correct ports in AWS.

Code

Here's the basics of the code for one way TLS, which is very similar to the Oracle example.

import oracledb
import sys
import boto3
        
# When creating the Lambda function, ensure the following setting for LD_LIBRARY_PATH
def lambda_handler(event, context):

    ssm_parameter = boto3.client('ssm')
	
	# Unencrypted connection works fine
	# oracleDSN = "dbname.accountstring.ap-southeast-2.rds.amazonaws.com:2484/servicename"
	
	oracleDSN = '''(description= (retry_count=3)(retry_delay=1)(address=(protocol=tcps)
(port=2484)(host=dbname.accountstring.ap-southeast-2.rds.amazonaws.com))(connect_data=(service_name=servicename))
(security=(ssl_server_cert_dn="C=US,ST=Washington,L=Seattle,O=Amazon.com,OU=RDS,CN=dbname.accountstring.ap-southeast-2.rds.amazonaws.com")))'''

    connRds = oracledb.connect(user=database_user, password=database_password, dsn=oracleDSN)

Errors

Here's the error message output from lambda (# replaces sensitive information)

Response
{
  "errorMessage": "DPY-6005: cannot connect to database. Connection failed with \"[SSL] internal error (_ssl.c:2633)\"",
  "errorType": "OperationalError",
  "requestId": "",
  "stackTrace": [
    "  File \"/var/lang/lib/python3.9/importlib/__init__.py\", line 127, in import_module\n    return _bootstrap._gcd_import(name[level:], package, level)\n",
    "  File \"<frozen importlib._bootstrap>\", line 1030, in _gcd_import\n",
    "  File \"<frozen importlib._bootstrap>\", line 1007, in _find_and_load\n",
    "  File \"<frozen importlib._bootstrap>\", line 986, in _find_and_load_unlocked\n",
    "  File \"<frozen importlib._bootstrap>\", line 680, in _load_unlocked\n",
    "  File \"<frozen importlib._bootstrap_external>\", line 850, in exec_module\n",
    "  File \"<frozen importlib._bootstrap>\", line 228, in _call_with_frames_removed\n",
    "  File \"/var/task/TableDailyCount.py\", line 50, in <module>\n    connRds = oracledb.connect(user=database_user, password=database_password, dsn=oracleDSN)\n",
    "  File \"/opt/python/oracledb/connection.py\", line 1000, in connect\n    return conn_class(dsn=dsn, pool=pool, params=params, **kwargs)\n",
    "  File \"/opt/python/oracledb/connection.py\", line 128, in __init__\n    impl.connect(params_impl)\n",
    "  File \"src/oracledb/impl/thin/connection.pyx\", line 345, in oracledb.thin_impl.ThinConnImpl.connect\n",
    "  File \"src/oracledb/impl/thin/connection.pyx\", line 163, in oracledb.thin_impl.ThinConnImpl._connect_with_params\n",
    "  File \"src/oracledb/impl/thin/connection.pyx\", line 129, in oracledb.thin_impl.ThinConnImpl._connect_with_description\n",
    "  File \"src/oracledb/impl/thin/connection.pyx\", line 247, in oracledb.thin_impl.ThinConnImpl._connect_with_address\n",
    "  File \"/opt/python/oracledb/errors.py\", line 103, in _raise_err\n    raise exc_type(_Error(message)) from cause\n"
  ]
}

What I've tried

• Reading all the docs. The answers are probably there, but because I'm fairly new to Python and Oracle I may not understand it fully.
• I've tried using orapki to create a wallet to use with the oracledb thin driver with the RDS root cert, including creating the oracle keystore as p12 and converting to ewallet.pem - that didn't work. I tried the create_pem.py script from this page and OpenSSL.
• Copying the RDS pem root certificate into the deployment package and referencing that as the keystore - that didn't work
• I've added the RDS CA Certs to three different cacerts files within the python39 folder ( C:\Users\Tim\AppData\Roaming\Python\Python39) as described on this page (you need to register for a free account to see the page)
• I've read everything I can find on the internet about this that seems relevant
• Noting the AWS certs page, ap-southeast-2-bundle.pem.

Question

• How would I get single direction TLS working?
• How do you create the ewallet.pem in the format that's required for mTls, from the AWS RDS Sydney root certificates?

[cjbj]

The connect() call doesn't pass the PEM file location so I assume you are showing an attempt with TLS. I don't know about the AWS configuration settings for 1-way vs mTLS, so can you verify that what you have above is for 1-way TLS configuration? Since you can connect with Java and TLS from your local PC, why not try the same with Python?

One way to add certificates for Python is to install the Python 'certifi' package.

[tomwaldnz]

Thanks for your reply Chris. Yes, this code is TLS, as there's no pem file being passed. I don't really need mTLS, it would be nice to have but not essential. I don't know the proper way to create the PEM file, the documentation isn't clear enough for me to follow, and the Python program didn't work for me - I've have to run it again to check the error. Is there detailed documentation how to create the PEM file suitable for python-oracledb to read it? That's lower priority for me than getting standard TLS working.

I've already tried the same with python as Java / SQLDeveloper, which is to add the RDS root certificate to the keystore. I edited the file C:\Users\Tim\AppData\Roaming\Python\Python39\site-packages\certifi\cacert.pem and added the RDS Root CA Cert at the bottom of the file. There were two other cacert.pem files in the C:\Users\Tim\AppData\Roaming\Python\Python39 folder structure so I added it to both others, just in case, and I also put it into the Windows root store. Is this what is required? Or should I do something else?

C:\Users\Tim\AppData\Roaming\Python\Python39\site-packages\botocore
C:\Users\Tim\AppData\Roaming\Python\Python39\site-packages\pip_vendor\certifi

I would be happy to send a PM with user, password and URL to someone reputable to try out. I would need to whitelist their IP, even though the database and the whole AWS account it's is entirely disposable.

[anthony-tuininga]

Tim, thanks for posting the packet output. The fact that you are getting packets at all tells me that the initial TLS negotiation is proceeding without difficulty. We recently became aware of the fact that TLS renegotiation is not being performed in some instances -- and it looks like you might be bitten by that.

[tomwaldnz]

Thanks Anthony. I've created you a new dedicated Oracle 19 database in AWS in a new isolated VPC with a whilelist for your IP address.

[anthony-tuininga]

With the database you provided I was able to verify that it was indeed the fact that TLS renegotiation was not being performed. I've just pushed changes to the repository that ensure that TLS renegotiation is performed when needed. If you are able to build python-oracledb yourself you should be able to connect now. I was able to connect to the database you provided in any case! Thanks for providing that!

[anthony-tuininga]

The patch I referenced in the previous comment was included in python-oracledb 1.0.2 which was just released. It should now work properly for you! Thanks for your help.

[tomwaldnz]

I can confirm that v1.0.2 works from both my PC and AWS Lambda to connect to an RDS database over TLS :) Thanks again Anthony, really appreciate the super quick response and release :)

A suggestion: a little more information in the docs about exactly how to create a wallet suitable for mutual TLS as pem and how to get it going would be awesome. I'm probably not going to do mutual TLS, but right now it's a bit tricky for people who aren't Oracle experts.

[gamerf]

would it be possible to share how you've created the ewallet.pem file to be able to connect using the thin client? I've tried different methods of creating this file from ewallet.p12 (with both the rootCA and the intermediate eu-west-1 trusted certificate) and I'm consistently getting this error

File "src/oracledb/impl/thin/connection.pyx", line 163, in oracledb.thin_impl.ThinConnImpl._connect_with_params
File "src/oracledb/impl/thin/connection.pyx", line 129, in oracledb.thin_impl.ThinConnImpl._connect_with_description
File "src/oracledb/impl/thin/connection.pyx", line 249, in oracledb.thin_impl.ThinConnImpl._connect_with_address
File "/usr/local/airflow/.local/lib/python3.7/site-packages/oracledb/errors.py", line 103, in _raise_err
    raise exc_type(_Error(message)) from cause
oracledb.exceptions.OperationalError: DPY-6005: cannot connect to database. Connection failed with "[SSL] PEM lib (_ssl.c:3932)" 

I'm using oracledb 1.0.2 too

thanks

[tomwaldnz]

Suggest you start a new thread / question about this. I never worked that out, I was ok with single direction TLS. The docs assumed too much existing Oracle knowledge for me to be able to achieve mutual TLS.

[vinod51]

Hi gamerf, I'm facing same issue, if you resolved the issue please let me know how you created the ewallet.pem file. thank you.

[ptekelly]

Hello
Sorry to jump into an old discussion - however I am having exact same issue (connection to OCI ATP database - always free tier)
I get same error - the difference is this WAS working in March. The same problem exists if I use windows local pc - or OCI hosted linux server
The same code worked on both systems prior to beginning of April.

I can connect via SQL dev - i have confirmed connection details are same for both

This is thin client and no wallet (TLS) connection

The only change since then is oracle have been reclaiming unused resources however the linux server and the database are still there and accessible

I have already raised this in #162 but as of yet I have not found a solution

I have posted here in hope that people who had and resolved the problem might be able to help me

thanks

Paul

[tomwaldnz]

I never got mutual TLS working, just one way. I did this as a project for a customer, it was deployed, and handed over to our support team. I think it's still working fine, using the version I used at the time which from memory was 1.0.1 or 1.0.2. So unfortunately I can't help. I found Anthony super helpful, he'll probably be able to help you.

[ptekelly]

hi - thanks for response - just for context i am not using mTLS - i wanted the simplest connection method - i have not tried using the wallets (apart from in sql dev) - i might have to try that route as this is very frustrating now

glad to hear you got it all working and no further issues

[tomwaldnz]

Looking at your question, it's quite different from this one. You're using Windows / Linux, I was connecting from Lambda. You're connecting to Oracle Cloud, I was connecting to RDS. So not much in common. Best keep working on it in the other question thread you have. I'll have a read of that and contribute if I can, but you're probably best off with the Oracle people who know the solution / area better than me.

[ptekelly]

i guess because the error was the same error it would be similar underlying issue
but yeah - will keep on the other discussion - thanks again