Category Archives: sqlnet

SSL with PKCS12 truststore

Many many moons ago I vaguely remember having a similar issue with java keystore / truststore and microsoft certificates stores.

When you start using SSL for your listener, you could potentially face a large number of issues amoung your toolsets. In my opinion, the most disastrous one is that you cannot monitor your database with Enterprise Manager and a TCPS listener. I tried with 10g, 11g, 12c and I seriously doubt it will come in 13c, even a dozen of ERs have been filled. The best workaround I found is to use a separate listener to monitor your database and monitor the ssl-listener itself with IPC.

Today I had to deal with a driver from Datadirect, which finally works perfectly fine with SSL, but the challenge was to know what to put in the keystore and truststore…

In SQLNET, you use the single-sign-on wallet (cwallet.sso) created by OWM/orapki or ldap.

In Java, per default, you use a java keystore, that you generate with keytool (or even use the default cacerts). There is only a lexical difference between a keystore and a truststore, they could both be the same file. As documented in the JSSE Ref
A truststore is a keystore that is used when making decisions about what to trust

But for some other tools, the java keytool won’t do the trick, if the truststore cannot be of the type JKS.

One common type is the PKCS12. This is the your ewallet.p12 you get with the Wallet Manager.

E.g. from java :***

To use single-sign-on, use trustStoreType=SSO as I wrote there : jdbc-ssl

Other command formats are X509 base64 or DER file. The openssl command line allows you easy conversion

openssl pkcs12 -in ewallet.p12 -out file.pem
openssl x509 -outform der -in file.pem -out file.der

or in Windows Explorer, just click on your p12 file and then click on the certificate to export in the certificate store.

anonymous cypher suites for SSL (and a 12c pitfall)

If you configure your listener for encryption only, you do not really need authentication.

It works pretty fine until, I wrote multiple posts on ssl.

You add SSL_CLIENT_AUTHENTICATION=FALSE to your server sqlnet.ora and listener.ora and specify an “anon” cipher suite in your client. You do not need to validate the certificate, so a default wallet will do.

orapki wallet create -wallet . -auto_login_only





or if you use java, the default truststore -usually located in $JAVA_HOME/jre/lib/security/cacerts, will also do.

    System.setProperty("", "SSL_DH_anon_WITH_DES_CBC_SHA");

On some plateform however you may get something like : IBM’s Client TrustManager does not allow anonymous cipher suites.

So far so good, but if you upgrade your listener to or 12c, the anonymous suites won’t be accepted if not explicitely set up in sqlnet.ora. This is documented in Note 1434966.1

You will get something like “ORA-28860: Fatal SSL error”, “TNS-12560: TNS:protocol adapter error” in Oracle or “SSLHandshakeException: Received fatal alert: handshake_failure”, “SQLRecoverableException: I/O-Error: Received fatal alert: handshake_failure” in java.

There are two -obvious- ways to fix this. The preferred approach is to not use anonymous suite (they seem to have disappeared from the supported cypher suites in the doc).

For this task, you use another cipher suite. The easiest way is to not specify any or just use one like TLS_RSA_WITH_AES_128_CBC_SHA (java) / SSL_RSA_WITH_AES_128_CBC_SHA (sqlnet). Even if you do not use client authentication, you will then have to authenticate the server, and import the root ca in the wallet or the keystore.

# comment out ssl_cipher_suites=(SSL_DH_anon_WITH_3DES_EDE_CBC_SHA)


// comment out : System.setProperty("", "SSL_DH_anon_WITH_DES_CBC_SHA");

Or, as documented in metalink, define the suite in sqlnet.ora and listener.ora if you use or

KeepAlive socket in 12c listener

A not uncommon issue with firewalls and listeners are timeouts. Your production database may be behind a firewall, you may connect from a remote location, even your Windows workstation may have some firewall activated, possibly you use ssh tunnels or TCPS. All those occasionally lead to timeouts and connection abortion, for instance ORA-03113 end-of-file on communication channel, ORA-03135: connection lost contact, TNS-12547 Lost contact.

The good news is that Oracle 12c now implements Socket Options (see man setsockopt), as documented in Net admin new features and more nicely in Note 1591874.1
dcd visualized

I made until now a positive experience with this keepalive behavior, especially with SSL listener. The default value for SQLNET.EXPIRE_TIME is 0, so you must set it to a non-zero value first, the recommended value is 10 (minutes).

OracleContext as top object in Active Directory

When you do expand your Active Directory schema, it is not reversible; how to decide to use the OracleContext as a top object or not?

On the one hand, for tnsnames resolution, you could hide your context down in your AD structure and change the path in ldap.ora



Used LDAP adapter to resolve the alias
OK (20 msec)

PS> Select-String "DB01" C:\temp\tnsping.trc

nnflrne1: Quering the directory for dn: cn=DB01,cn=OracleContext,
nnflqbf: Search:  Base: cn=DB01,cn=OracleContext,OU=Oracle,OU=Misc,
  DC=example,DC=com; Scope: 0; filter: (objectclass=*) returns 0x0
nnflgne:   DN : cn=DB01,cn=OracleContext,OU=Oracle,OU=Misc,

So far so good; but on the other hand, it prevents you from using connection identifier like DB01.EXAMPLE.COM

TNS-03505: Failed to resolve name

PS> Select-String "DB01" C:\temp\tnsping.trc

nnflfdn: Turning simplified name DB01.EXAMPLE.COM into a dn.
nnflfdn:     The resulting dn is cn=DB01,cn=OracleContext,
nnflrne1: Quering the directory for dn: cn=DB01,cn=OracleContext,
nnflqbf: Search:  Base: cn=DB01,cn=OracleContext,dc=EXAMPLE,
  dc=COM; Scope: 0; filter: (objectclass=*) returns 0x20
nnfun2a: address for name "DB01.EXAMPLE.COM" not found

This no longer works. Your database domain name must match your Active Directory domain name and your object must be a top object domain

Platform guide for Windows : Oracle Context is the top-level Oracle entry in the Active Directory tree

It is probably wiser to follow this recommendation.

Also new in 11gR2 is NAMES.LDAP_AUTHENTICATE_BIND=TRUE, which removes the need of allowing anonymous ldap bind in AD

specify TNSNAMES for one program

Monday I wrote on tnsping.exe inconsistencies. Actually there is one good thing in having Oracle Client on Windows looking in the current directory first : you can set one tnsnames for a specific shortcut ! It is quite a viable alternative to .bat files with set TNS_ADMIN=path.

Demo :

First I create a small EXE in C#


using System;
using System.Threading;
using Oracle.DataAccess.Client;

class HelloWorld
  static void Main() {
    OracleConnection connection=
      new OracleConnection("Data Source=DB01; User Id=scott; password=tiger");
    try {
      Console.WriteLine("Msg: " + (new OracleCommand(
        "select * from global_name",connection)).ExecuteScalar());
    } catch(Exception e) {
      Console.WriteLine("Exception Occured :{0}",e.Message);
    } finally {


C:\Windows\Microsoft.NET\Framework\v4.0.*\csc.exe /R:C:\oracle\product\11.2.0\client_1\\bin\4\Oracle.DataAccess.dll HelloWorld.cs

Create a specific tnsnames and sqlnet





C:\TEMP> HelloWorld

To create a desktop icon with the correct WorkingDirectory with powershell

PS> $ws =  New-Object -comObject WScript.Shell
PS> $desktop = [Environment]::GetFolderPath("Desktop")
PS> $s = $ws.createshortcut($desktop+"\HelloWorld.lnk")
PS> $s.TargetPath = "C:\TEMP\HelloWorld.exe"
PS> $s.WorkingDirectory = "C:\TEMP"
PS> $s.Save()