This post is about configuring SQL*Net to support native SQL*Net encription and SSL authentication for different users at the same time. Imagine your security offices comes along and demands that every connection to the database must be encrypted and it must be enforced by the database. The application server (e.g. Weblogic) already authenticates itself to the database using SSL certificates, so the network traffic is already encrypted. All other connects use username/password authentication and must be encrypted by native SQL*Net encryption. The database server enforces encryption by setting SQLNET.ENCRYPTION_SERVER = REQUIRED. From a security point of view this is paramount.

Going ahead and configure everything according docs and white papers I bet you’ll end up with “ORA-12696 Double Encryption Turned On, login disallowed”.

[side note]
If SQLNET.ENCRYPTION_SERVER is set to REQUIRED then the client gets “ORA-28865: SSL connection closed”, but once you start digging in the SQL*Net trace file of the server process you’ll find ORA-12696.
Funnily, if SQLNET.ENCRYPTION_SERVER is set to REQUESTED then the client actually gets ORA-12696 reported.
[/side note]

The basic problem is, that there is only one $TNS_ADMIN location with one sqlnet.ora containing all the configuration. Ultimately, we need to find a way to use multiple $TNS_ADMIN locations and separate the sqlnet.ora configurations for the different listening end points/services.
One solution would be to fire up two listeners with different environment settings. Note that the server processes spawned by the listener inherit the properties from it, that being permissions and environment variables.
Following I’m going to show a solution with one single listener using a little known feature: specifying ENVS parameter in the static listener SID list.

But first let’s have a look a the current configuration which is giving us some trouble. There’s a listener configuration with two listening end points (one for TCP and one for TCPS) and a mixed configuration in sqlnet.ora.
My $TNS_ADMIN is pointing to /u01/app/oracle/network/admin which is the default location set in the oracle’s user profile and is used when starting up the listener.

$ cat /u01/app/oracle/network/admin/listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oel6ora12dev1.localdomain)(PORT = 2483))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCPS)(HOST = oel6ora12dev1.localdomain)(PORT = 2484))
    )
  )
SSL_CLIENT_AUTHENTICATION=FALSE
WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY=/u01/app/oracle/admin/DEV1/wallet/auth)))

$ cat /u01/app/oracle/network/admin/sqlnet.ora

SQLNET.ENCRYPTION_SERVER=requested
SQLNET.ENCRYPTION_TYPES_SERVER=(aes256)
SQLNET.AUTHENTICATION_SERVICES=(BEQ,TCPS)
SSL_CLIENT_AUTHENTICATION=TRUE
SSL_CIPHER_SUITES=(SSL_RSA_WITH_AES_256_CBC_SHA)
WALLET_LOCATION = (SOURCE = (METHOD=FILE)(METHOD_DATA = (DIRECTORY = /u01/app/oracle/admin/DEV1/wallet/auth)))

If we try to connect from a client using SSL authentication it errors with “ORA-12696 Double Encryption Turned On, login disallowed”. Connecting using username/password works just fine and the network traffic is properly encrypted.

To be able to have different SQL*Net configurations we first need to create a separate directory structure. In this case I just create it next to the current network/admin directory and separate the configuration into two sqlnet.ora files:
$ mkdir /u01/app/oracle/network/adminssl
$ vi /u01/app/oracle/network/admin/sqlnet.ora

SQLNET.ENCRYPTION_SERVER=requested
SQLNET.ENCRYPTION_TYPES_SERVER=(aes256)

$ vi /u01/app/oracle/network/adminssl/sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES=(BEQ,TCPS)
SSL_CLIENT_AUTHENTICATION=TRUE
SSL_CIPHER_SUITES=(SSL_RSA_WITH_AES_256_CBC_SHA)
WALLET_LOCATION = (SOURCE = (METHOD=FILE)(METHOD_DATA = (DIRECTORY = /u01/app/oracle/admin/DEV1/wallet/auth)))

Now, I need to tell the listener to set the proper environment when spawning server processes for incomming connection requests. This is done by specifying the TNS_ADMIN location in the ENVS parameter:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DEV1_SSL.localdomain)
      (ORACLE_HOME = /u01/app/oracle/product/ora12101)
      (SID_NAME = DEV1)
      (ENVS="TNS_ADMIN=/u01/app/oracle/network/adminssl")
    )
  )

Now, whenever a connection is established via service DEV1_SSL.localdomain the listener exports the environment varialbe TNS_ADMIN before spawning the server process. The server process then looks up the environment variable and reads the sqlnet.ora file in /u01/app/oracle/network/adminssl.
As you can see, next to using different listening end points I also present an addtitional service on the listener.
Depending on which service and end point the client uses it gets either native SQL*Net encryption or SSL authentication including SSL encryption.

On the client side I use either one of the following TNS entries, depending on which authentication method the user uses.

DEV1_SSL.LOCALDOMAIN =
  (DESCRIPTION =
    (ADDRESS_LIST=
      (ADDRESS = (PROTOCOL = TCPS)(HOST = oel6ora12dev1.localdomain)(PORT = 2484))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DEV1_SSL.localdomain)
      (SECURITY=(SSL_SERVER_CERT_DN="CN=DEV1.localdomain"))
    )
  )

DEV1_NSE.LOCALDOMAIN =
  (DESCRIPTION =
    (ADDRESS_LIST=
      (ADDRESS = (PROTOCOL = TCP)(HOST = oel6ora12dev1.localdomain)(PORT = 2483))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DEV1.localdomain)
    )
  )

$ sqlplus /@DEV1_SSL
or
$ sqlplus system/manager@DEV1_NSE

Setting different values for TNS_ADMIN at the time of process spawning is useful in other situations, too. For instance, this technique can be applied if you have one listener for multiple databases and each database has its wallet.

In Grid Infrastructure environment there is also the possibility to set environment variables in Oracle Clusterware which is probably more comfortable to handle:

srvctl setenv database -d  -t TNS_ADMIN=

Foot note: All test have been run on Oracle 12.1.0.1