ORA-12696: Double Encryption Turned On Even When IGNORE_ANO_ENCRYPTION_FOR_TCPS=TRUE is Set

The error "ORA-12696: Double Encryption Turned On, login disallowed" is an expected one when both SSL and Native encryption (ANO) is enabled. By default Oracle doesn't allow both encryption types and return the above error.

However, there is a parameter that could be set so that ANO is ignored for TCPS connections. The parameter is called IGNORE_ANO_ENCRYPTION_FOR_TCPS and setting this to TRUE would allow both TCP with ANO and TCPS connections to be used concurrently.
While settting up this configuration the connections for TCPS were failing with ORA-12696. The parameter IGNORE_ANO_ENCRYPTION_FOR_TCPS could be set on either sqlnet.ora or in the TNS alias. No matter where it was set, the TCPS connections kept getting the above error. It was puzzling as the documentation was followed to the letter.



Apparently the issue is with documetnation. The parameter is actaully called "SQLNET.IGNORE_ANO_ENCRYPTION_FOR_TCPS" (visible on the step 4 below). However the documentation where it shows how to set it and helpful copy buttons all ignore the "SQLNET." part. See below for current documentation at the time of this blog post.

MOS Doc 2614143.1 which also address the same issue shows it being set as IGNORE_ANO_ENCRYPTION_FOR_TCPS=TRUE (though the sqlnet.ora content shown in the same MOS has it set correctly).
SR was raised to correct the documentation so that parameter is reflected correctly with the prefix SQLNET. similar to other parameters such as SQLNET.ENCRYPTION_CLIENT, SQLNET.ENCRYPTION_TYPES_CLIENT and etc which all has been documented with SQLNET. prefix.
Only place this is correctly reflected (time of this blog) is in net services reference guide (which helped to identify the root cause of the issue).
When setting this on the TNS alias then the parameter could be with sqlnet. prefix or without it(documentation is correct for this setting).

Remote Cloning a PDB with Encrypted Data

This post list the steps for remote cloning a PDB that has encrypted data. What's difference in this case, to that of a remote cloning a PDB without the use of 12c TDE is the use of the encryption key. The master key of the source PDB must be available to cloned PDB. There are multiple ways of achieving this. This post shows two convenient ways to use when remote cloning PDBs with encrypted data.

Update 2018/07/24 : Method 1 shown below doesn't work on Oracle cloud where TDE is enabled by default. This is due to bug 24763954 which is closed as not a bug. If remote cloning is done on Oracle cloud then use the method two mention in the post. For more refer MOS notes 2228673.1, 2208792.1, 2415131.1.

Method 1. Using one_step_plugin_for_pdb_with_tde parameter
According to advance security guide "when ONE_STEP_PLUGIN_FOR_PDB_WITH_TDE is set to TRUE, the database caches the keystore password in memory, obfuscated at the system level, and then uses it for the import operation. The default for ONE_STEP_PLUGIN_FOR_PDB_WITH_TDE is FALSE".
So in order to clone a PDB with encrypted data simply set the ONE_STEP_PLUGIN_FOR_PDB_WITH_TDE to true and run the cloning operation.

1. The remote PDB has a encrypted tablespace

SQL> select tablespace_name,encrypted from dba_tablespaces where encrypted='YES' ;

TABLESPACE_NAME                ENC
------------------------------ ---
ENCTEST                        YES

SQL> select t.name,ENCRYPTIONALG,STATUS FROM V$ENCRYPTED_TABLESPACES  e, v$tablespace t where e.ts#=t.ts#;

NAME       ENCRYPT STATUS
---------- ------- ----------
ENCTEST    AES128  NORMAL

2. Create a key store (encryption wallet) at the CDB root where the clone will be created. Without this the cloning will fail. Creating wallet is shown in a previous post.

3. Set the ONE_STEP_PLUGIN_FOR_PDB_WITH_TDE to true

ALTER SYSTEM SET one_step_plugin_for_pdb_with_tde=TRUE SCOPE=BOTH;

4. Run the remote clone operation. Steps for remote cloning is available in a previous post.

create pluggable database mypdb from cxpdb@PDB1K_LINK 
file_name_convert=('/opt/oracle/oradata/cxcdb/cxpdb/','/opt/oracle/oradata/oracdb/mypdb/') ;

Pluggable database created.

5. Finally open the cloned PDB.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORAPDB                         READ WRITE NO
         4 MYPDB                          MOUNTED


SQL> alter pluggable database mypdb open;

Pluggable database altered.


SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORAPDB                         READ WRITE NO
         4 MYPDB                          READ WRITE NO

6. If no longer used then set the ONE_STEP_PLUGIN_FOR_PDB_WITH_TDE to default value of false.

ALTER SYSTEM SET one_step_plugin_for_pdb_with_tde=FALSE SCOPE=BOTH;

Method 2. Using Key Store Password of the Local CDB
In this method the key store password of the local CDB (CDB where the clone PDB is created) is used during the clone command. As per security guide the encrypted data is still accessible because during the cloning the master key of the remote PDB is copied over. However it's best to re-key after the cloning as the original key information is not shown in the PDB's v$ views.

1. The same remote PDB is used for this example as well. It's also assumed the local CDB has wallet already created.

2. Execute the remote cloning command on the CDB root specifying the key store password.

create pluggable database mypdb from cxpdb@PDB1K_LINK 
file_name_convert=('/opt/oracle/oradata/cxcdb/cxpdb/','/opt/oracle/oradata/oracdb/mypdb/') 
KEYSTORE IDENTIFIED BY  asanga123;

Pluggable database created.

3. Open the PDB and check the encryption key on the clone PDB's v$view. As mentioned in the security guide this return no rows.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORAPDB                         READ WRITE NO
         5 MYPDB                          READ WRITE NO

SQL> alter session set container=mypdb;

Session altered.

SQL> select CON_ID,KEY_ID,KEYSTORE_TYPE,CREATOR_DBNAME,CREATOR_PDBNAME from v$encryption_keys order by 1;

no rows selected

4. Run the below to re-key. The force option is used due to bug 22826718. Refer 1944507.1 for more.

ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY asanga123 with backup;
 
 SQL> select CON_ID,KEY_ID,KEYSTORE_TYPE,CREATOR_DBNAME,CREATOR_PDBNAME from v$encryption_keys order by 1;

    CON_ID KEY_ID                                                  KEYSTORE_TYPE     CREATOR_DB CREATOR_PD
---------- ------------------------------------------------------- ----------------- ---------- ----------
         5 AXj5300QAE8Kv7cOn6U0xJ8AAAAAAAAAAAAAAAAAAAAAAAAAAAAA    SOFTWARE KEYSTORE oracdb     MYPDB

12c Encryption

This post gives a highlight of using TDE in 12c. For detail information refer advance security guide. In 12c the orapki/alter system commands related to key management has been replaced with ADMINISTER KEY MANAGEMENT commands.
The first set of steps shows setting a non-CDB for use of TDE. 1. Create a location for wallet files (key store location).

mkdir -p /opt/app/oracle/product/12.1.0/dbhome_1/network/admin/tde
chmod 700 /opt/app/oracle/product/12.1.0/dbhome_1/network/admin/tde

2. Add ENCRYPTION_WALLET_LOCATION to sqlnet.ora file

ENCRYPTION_WALLET_LOCATION =
  (SOURCE =
   (METHOD = FILE)
    (METHOD_DATA =
     (DIRECTORY = /opt/app/oracle/product/12.1.0/dbhome_2/network/admin/tde)
    )
  )

3. Create the software key store (wallet) by specifying key store location and password or key store (asanga123 in this case)

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/opt/app/oracle/product/12.1.0/dbhome_1/network/admin/tde' identified by asanga123;

Result of this is a key store file (wallet file).

SQL> ! ls
ewallet.p12

At this stage the status of the key store would be closed

SQL> select WRL_TYPE,STATUS,WALLET_TYPE,WALLET_ORDER,FULLY_BACKED_UP,CON_ID,WRL_PARAMETER from v$ENCRYPTION_WALLET;

WRL_TYPE   STATUS   WALLET_TYPE   WALLET_OR FULLY_BAC     CON_ID WRL_PARAMETER
---------- -------  ------------- --------- --------- ---------- -----------------------------------------------------------
FILE       CLOSED   UNKNOWN       SINGLE    UNDEFINED          0 /opt/app/oracle/product/12.1.0/dbhome_1/network/admin/tde/ 

4. Open the key store by providing the key store password

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY asanga123;
keystore altered.

SQL> select WRL_TYPE,STATUS,WALLET_TYPE,WALLET_ORDER,FULLY_BACKED_UP,CON_ID,WRL_PARAMETER from v$ENCRYPTION_WALLET;

WRL_TYPE   STATUS             WALLET_TYPE   WALLET_OR FULLY_BAC     CON_ID WRL_PARAMETER
---------- ------------------ ------------- --------- --------- ---------- -----------------------------------------------------------
FILE       OPEN_NO_MASTER_KEY PASSWORD      SINGLE    NO                 0 /opt/app/oracle/product/12.1.0/dbhome_1/network/admin/tde/ 

5. Usually at this stage auto login key store is also created. But this lead to an issue (refer 1944507.1).

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY asanga123 WITH BACKUP;
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY asanga123 WITH BACKUP
*
ERROR at line 1:
ORA-28417: password-based keystore is not open

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE open identified by asanga123;
ADMINISTER KEY MANAGEMENT SET KEYSTORE open identified by asanga123
*
ERROR at line 1:
ORA-28354: Encryption wallet, auto login wallet, or HSM is already open

Therefore master encryption key is created before creating auto login wallet.

SQL>  ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY asanga123 with backup ;
keystore altered.

This will change wallet status from open_no_master_key to open.

SQL> select WRL_TYPE,STATUS,WALLET_TYPE,WALLET_ORDER,FULLY_BACKED_UP,CON_ID,WRL_PARAMETER from v$ENCRYPTION_WALLET;

WRL_TYPE   STATUS  WALLET_TYPE   WALLET_OR FULLY_BAC     CON_ID WRL_PARAMETER
---------- ------- ------------- --------- --------- ---------- -----------------------------------------------------------
FILE       OPEN    PASSWORD      SINGLE    NO                 0 /opt/app/oracle/product/12.1.0/dbhome_1/network/admin/tde/

On the database alert log following could be observed

Creating new database key for new master key and wallet
Creating new database key with the new master key
Retiring: ena 2 flag 6 mkloc 0
   encrypted key 8dd09c987ef966198af992379477f13900000000000000000000000000000000
   mkid b81a02de82664fbcbf2c9bcdcec4a3ae
Creating: ena 2 flag e mkloc 1
   encrypted key 92c72aeada0197dda6da3e4d64ac875c00000000000000000000000000000000
   mkid ac6f7e2c97ff4fdfbf9d900a653e2c21
Switching out all online logs for the new master key

6. Finally create the auto login key store so that key store is auto opened when the database starts

SQL> ADMINISTER KEY MANAGEMENT CREATE local AUTO_LOGIN KEYSTORE FROM KEYSTORE '/opt/app/oracle/product/12.1.0/dbhome_1/network/admin/tde' identified by asanga123;
keystore altered.

This will create the cwallet.sso file and wallet type will be changed to local_autologin

SQL> ! ls
cwallet.sso  ewallet_2015101517371593.p12  ewallet.p12  ewallet.p12.bak

select WRL_TYPE,STATUS,WALLET_TYPE,WALLET_ORDER,FULLY_BACKED_UP,CON_ID,WRL_PARAMETER from v$ENCRYPTION_WALLET;

WRL_TYPE   STATUS WALLET_TYPE       WALLET_OR FULLY_BAC     CON_ID WRL_PARAMETER
---------- ------ ----------------- --------- --------- ---------- -----------------------------------------------------------
FILE       OPEN   LOCAL_AUTOLOGIN   SINGLE    NO                 0 /opt/app/oracle/product/12.1.0/dbhome_1/network/admin/tde/ 

Encryption could be enabled on CDB with PDB same way as above. Below is an example of enabling TDE for CDB with two PDBs.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBONE                         READ WRITE NO
         4 PDBTWO                         READ WRITE NO

Create key store, open and set master encryption key.

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/opt/app/oracle/product/12.1.0/dbhome_1/network/admin/tde' identified by asanga123;
keystore altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY asanga123 container=all;
keystore altered.

SQL>  ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY asanga123 with backup container=all;
keystore altered.

SQL> select WRL_TYPE,STATUS,WALLET_TYPE,WALLET_ORDER,FULLY_BACKED_UP,CON_ID,WRL_PARAMETER from v$ENCRYPTION_WALLET;

WRL_TYPE   STATUS  WALLET_TYPE   WALLET_OR FULLY_BAC     CON_ID WRL_PARAMETER
---------- ------- ------------- --------- --------- ---------- -----------------------------------------------------------
FILE       OPEN    PASSWORD      SINGLE    NO                 0 /opt/app/oracle/product/12.1.0/dbhome_1/network/admin/tde/

Enable auto login

SQL> ADMINISTER KEY MANAGEMENT CREATE local AUTO_LOGIN KEYSTORE FROM KEYSTORE '/opt/app/oracle/product/12.1.0/dbhome_1/network/admin/tde' identified by asanga123;
keystore altered.

Login to a PDB and check the wallet status

SQL> show con_name

CON_NAME
-----------
PDBTWO

select WRL_TYPE,STATUS,WALLET_TYPE,WALLET_ORDER,FULLY_BACKED_UP,CON_ID,WRL_PARAMETER from v$ENCRYPTION_WALLET;

WRL_TYPE   STATUS WALLET_TYPE       WALLET_OR FULLY_BAC     CON_ID WRL_PARAMETER
---------- ------ ----------------- --------- --------- ---------- -----------------------------------------------------------
FILE       OPEN   LOCAL_AUTOLOGIN   SINGLE    NO                 0 /opt/app/oracle/product/12.1.0/dbhome_1/network/admin/tde/ 

v$encryption_keys view could be used to identify each containers key ids even though container id remains 0 for all.

SQL> select CON_ID,KEY_ID,KEYSTORE_TYPE,CREATOR_DBNAME,CREATOR_PDBNAME from v$encryption_keys;

    CON_ID KEY_ID                                                 KEYSTORE_TYPE     CREATOR_DBNAME  CREATOR_PDBNAME
---------- ------------------------------------------------------ ----------------- --------------- ------------------------------
         0 AQugdpFHIk9yv1tQiZj0EhUAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   SOFTWARE KEYSTORE PDBENC          CDB$ROOT
         0 Af8ZFXnsWk+/v9Z4RFalEHwAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   SOFTWARE KEYSTORE PDBENC          PDBONE
         0 AU3e99wEOk8lv4QEBp4Ow3AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA   SOFTWARE KEYSTORE PDBENC          PDBTWO

Following test could be used to verify TDE is working. In this case a tablespace is created with encryption enabled. Few rows are inserted to tables created in those table spaces. At times buffer cache flushing or manual check points may be required to force database writer to write to data files

SQL>  create tablespace enctest datafile size 10m ENCRYPTION DEFAULT STORAGE(ENCRYPT);
Tablespace created.
SQL>  create tablespace nonenctest datafile size 10m ;
Tablespace created.

SQL>select TABLESPACE_NAME,ENCRYPTED from dba_tablespaces;

TABLESPACE_NAME                ENC
------------------------------ ---
ENCTEST                        YES
NONENCTEST                     NO

create table enctable (a varchar2(100)) tablespace ENCTEST;
create table nonecntbale (a varchar2(100)) tablespace NONENCTEST;

begin
  for i in 1 .. 100
  loop
  insert into enctable values ('top secret text');
  insert into nonecntbale values ('top secret text');
  end loop;
  commit;
  end;
/
alter system checkpoint;

Strings command is used to look into the data file content.First the data file belonging to non-encrypted tablespace

strings /opt/app/oracle/oradata/ENT12C1/datafile/o1_mf_nonencte_c1zsml08_.dbf
top secret text,
top secret text,

Secondly the data file belonging to the encrypted tablespace

strings /opt/app/oracle/oradata/ENT12C1/datafile/o1_mf_enctest_c1zsl0d9_.dbf
Vj#>{
O+l;2
c1ax|
        drl
fzc1
Lbqby%}
u;Fa
=B]Wv
~/th
9hHW
=Jc;
@s|J84
|3M*
2ATG

As seen above encrypted tablespace doesn't show it's content in clear text.



Encryption requires additional CPU. Next is a minor test that was done to compare the CPU usage for inserting to a encryption enabled tablespace vs non-encrypted tablespace.

create table NOencryptiontbl(a number, b varchar2(100)) tablespace NONENCTEST;
create table encryptiontbl(a number, b varchar2(100)) tablespace ENCTEST;
  
  begin
    for i in 1 .. 1000000
    loop
    --insert into encryptiontbl values (i,'asdfghjkllqwertyuiopzxcvbnm134567890'||i);
    insert into NOencryptiontbl values (i,'asdfghjkllqwertyuiopzxcvbnm134567890'||i);
    commit;
    end loop;
  end;
  /

The inserts were first done on table created on non-encrypted and then on table created on encryption enabled tablespace. Value of CPU used by this session statistic was used to compare the CPU usage of each session once the inserts have completed. Graph below shows the CPU used, with TDE enabled it took on average 51 CPU seconds more for inserts to complete.

If TDE is used in a data guard environment then the standby must have the key store (wallet) copied over from the primary. Without wallet open the recovery at standby will fail. In the below output datafile 17 was part of a encrypted tablespace created at primary.

Datafile #17: '/opt/app/oracle/oradata/ENT12C1S/datafile/o1_mf_t2_bv215f2m_.dbf'
kcrf_decrypt_redokey: wallet is not opened..(err 28365)
Errors with log /opt/app/oracle/fast_recovery_area/ENT12C1S/archivelog/2015_07_23/o1_mf_1_2726_bv214gg4_.arc
MRP0: Background Media Recovery terminated with error 28365
Thu Jul 23 16:13:49 2015
Errors in file /opt/app/oracle/diag/rdbms/ent12c1s/ent12c1s/trace/ent12c1s_pr00_2541.trc:
ORA-28365: wallet is not open
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 41627576
Thu Jul 23 16:13:50 2015
Errors in file /opt/app/oracle/diag/rdbms/ent12c1s/ent12c1s/trace/ent12c1s_pr00_2541.trc:
ORA-28365: wallet is not open
Thu Jul 23 16:13:51 2015
MRP0: Background Media Recovery process shutdown (ent12c1s)

Another thing to watch out for is exporting using exp as oppose to expdp. Tables residing in encrypted tablespaces cannot be exported using exp.

exp asanga/asa file=asa.dmp tables=p1,p2
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
About to export specified tables via Conventional Path ...
EXP-00111: Table P1 resides in an Encrypted Tablespace T2 and will not be exported
EXP-00111: Table P2 resides in an Encrypted Tablespace T2 and will not be exported
Export terminated successfully with warnings.

Useful metalink notes
TDE Wallet Problem in 12c: Cannot do a Set Key operation when an auto-login wallet is present [ID 1944507.1]
Master Note For Transparent Data Encryption ( TDE ) [ID 1228046.1]
How to migrate a non pluggable database that uses TDE to pluggable database ? [ID 1678525.1]
Known TDE Wallet Issues [ID 1301365.1]
Auto Login Wallet Cannot Be Closed [ID 1204604.1]
Removing TDE, but V$ENCRYPTION_WALLET / GV$ENCRYPTION_WALLET still has rows. [ID 2003528.1]
Step by Step Troubleshooting Guide for TDE Error ORA-28374 [ID 1541818.1]
How To Import Encrypted Datapump Data from 11G To 12c DB [ID 1642059.1]

Related Post
19c Encryption