ORA-00542: Failure during SSL handshake

user4784967

Hello,

I have to change my c# project the Oracle-Database access by SSL (wallet). 

I get the certificate for the wallet and create the wallet by myself:

orapki wallet create -wallet walletpath -pw PASSWORT -auto_login

orapki wallet add -wallet walletpath -trusted_cert -cert certificate.pem

mkstore -wrl walletpath -createCredential tnsalias username password

Without SSL all works fine. But with SSL and a wallet I get the error "ORA-00542" (see the Oracle trace file). It sounds like a problem with the certificate. Is the certificate wrong or the creation of the wallet?

I have tried ODP.NET 21.6.1 and 19.5.1 (Oracle.ManagedDataAccess)

Thanks for any help

OracleTrace.txt

Alex Keh-Oracle

Can you provide all the trace files generated? You only provided the pool manager trace file, not the actual connection attempt trace file (different threads) that will indicate what in the attempt process failed.

I want to verify a couple other things:

1. The wallet file was not in a zip file when loaded. SQL Developer can accept wallets in a zip file, but ODP.NET does not.
2. Did you add the public key for the SERVER's cert to the Root/LocalMachine?

SQL Developer being able to access Oracle DB is a good sign. However, it uses a C implementation whereas managed ODP.NET uses C#.

Alex Keh-Oracle

It looks like you are getting an "X509ChainStatusFlags.UntrustedRoot".

What are the exact commands used to create the client and server wallet files?

If you are using self-signed certs, the client and server must use the SAME root, and both must have a "user" cert pointing to that root, as defined in Christian Shay's blog post.

You said you were checking if the servers CA/root public was added to the root/LocalMachine. Did you find that occurred?

Alex Keh-Oracle

It looks like the inner exception is reporting "The remote certificate is invalid according to the validation procedure" as translated from German.

The ODP.NET doc has some step-by-instructions for setting up SSL configuration, which I linked to below. Further down in the same documentation section, there are some ODP.NET SSL troubleshooting recommendations.

https://docs.oracle.com/en/database/oracle/oracle-database/21/odpnt/featConnecting.html#GUID-3B4B12E5-767F-4956-A3E4-EBD501A5365C

user4784967

I had already found this website and I have tested different configuration. Any other idea?

Alex Keh-Oracle

You got an untrusted root error.

Can you try adding that CA pub certificate to the root/localmachine store? You should also try removing the following from app.config AND sqlnet.ora:

SSL_SERVER_DN_MATCH : ON

SSL_CIPHER_SUITES : (SSL_RSA_WITH_AES_256_CBC_SHA,SSL_RSA_WITH_AES_256_CBC_SHA256)

If you still see an error after these changes, please provide the trace.

Are you trying to connect to Oracle Autonomous DB with a wallet or with one-way TLS?

Alex Keh-Oracle

Here are instructions how to load a CA public certificate into the root/LocalMachine.

http://cshay.blogspot.com/2017/01/

user4784967

Sorry that I answer so late. But I was some week on holidays. And after that I got help from another side.

I have succeeded datbabase access with the SQL Developer using the wallet. So the configuration of the ora-files and the wallet are ok.

In the tnsnames.ora are two entries. One entry without TLS and one with TLS. They looks like

myalias=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost)(PORT=6777))(CONNECT_DATA=(SID=mysid)))

myalias_TLS=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=myhost)(PORT=2484))

  (CONNECT_DATA=(SID=mysid))

  (SECURITY=(SSL_SERVER_CERT_DN=C=xx,ST=xx,L=xx,O=xx,OU=xx,CN=xx)))  

I use following c# code

OracleConfiguration.TnsAdmin = pathToTnsAdmin;

OracleConnection dbConnection = new OracleConnection();

dbConnection.ConnectionString = myConnectionString;

using (dbConnection)

{

dbConnection.Open(); // here has an exception using tls

}

If use myConnectionString = "Data Source=myalias;User Id=myuser;Password=mypassword;" all works fine.

But if use myConnectionString = "Data Source=myalias_TLS;User Id=myuser;Password=mypassword;" or only "Data Source=myalias_tls;" I get the error ORA-00542. (I tried both because in the SQL Developer I need to use also user id and password)

I have provide two trace. One with the not modified sqlnet.ora and one with modified sqlnet.ora (what you mentioned)

trace.zip

Alex Keh-Oracle

Can you provide all the trace files generated? You only provided the pool manager trace file, not the actual connection attempt trace file (different threads) that will indicate what in the attempt process failed.

I want to verify a couple other things:

1. The wallet file was not in a zip file when loaded. SQL Developer can accept wallets in a zip file, but ODP.NET does not.
2. Did you add the public key for the SERVER's cert to the Root/LocalMachine?

SQL Developer being able to access Oracle DB is a good sign. However, it uses a C implementation whereas managed ODP.NET uses C#.

user4784967

1. The wallet is not in a zip file
2. This I have to clarify. But I think so (customers machine)

trace.zip

trace_mod.zip

Alex Keh-Oracle

We looked at the trace. A couple of issues we see:

• You have "wallet_override" set to "true". I don't you are using SEPS. If not, remove the config (both sqlnet.ora and app.config) or set it to FALSE.
• The trace is heavily redacted with respect to the connect string (both in config trace and pooling trace) and tcp/tcps connect iterations. That makes it impossible to tell what is going on. Instead of removing whole lines, try just redacting the SPECIFIC DATA within the line that needs redaction, leaving the REST of the line.

If the worry is that this is a public forum, you can also open up an Oracle Support service request and share the trace that way.

user4784967

Sorry I not delete any data. I only replace data which need to hide.

Anyway I make again and set "wallet_override" to FALSE. This time I replace each data which need to hide with an unique name and mark it with a # sign. For example: John Smith I replace with #username#. I hope this more clear now.

The tracemod.zip contains the trace files when I delete SSL_SERVER_DN_MATCH and SSL_CIPHER_SUITES.

trace.zip

tracemod.zip

Alex Keh-Oracle

It looks like you are getting an "X509ChainStatusFlags.UntrustedRoot".

What are the exact commands used to create the client and server wallet files?

If you are using self-signed certs, the client and server must use the SAME root, and both must have a "user" cert pointing to that root, as defined in Christian Shay's blog post.

You said you were checking if the servers CA/root public was added to the root/LocalMachine. Did you find that occurred?