TDE encryption in Oracle/MSSQL

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Database TDE encryption setup, implementation details for Oracle and SQL Server (MSSQL)

Environment

Any Supported Releases

Resolution

Oracle

• The Progress DataDirect Driver that we ship with Clarity does support JDBC Encryption starting with DD release 5.1.0 and it requires Oracle Advanced Security (OAS) licensing.
• Encrypting the data at rest (i..e the data stored in the Oracle RAC hosts) uses the Transparent Data Encryption (TDE) feature of Oracle Advanced Security.
• We have tested TDE in our labs and we have found that this causes a 20% performance overhead impact to the Clarity application. Note with Oracle 19 the overhead will be less but still some is expected
• You can enable this on the Clarity side by adding the required tags to the JDBC URL in NSA/CSA
• Encrypting the network data flow between Oracle RAC servers and Clarity Application server hosts requires the Network Data Encryption (NDE) feature of Oracle Advanced Security- we have not tested this in our labs yet so we do not support it.
• From 16.0.3 onwards Data Direct Driver is replaced with database native drivers 

To implement Oracle Advanced Security encryption
                 -Set the EncryptionLevel property in the JDBC URL to accepted, requested, or required
                - Set the EncryptionTypes property in the JDBC URL to one or multiple algorithms (Oracle Advanced Security provides the Advanced Encryption Standard (AES), DES, 3DES, and RC4 symmetric cryptosystems for protecting the confidentiality of network traffic)
 
For example, your JDBC URL string in NSA or properties.xml including these 2 tags should look something like this:

Till Clarity Version 16.0.2 : url="jdbc:clarity:oracle://DBSERVER:1521;ServiceName=SERVICENAME;BatchPerformanceWorkaround=true;InsensitiveResultSetBufferSize=0;ServerType=dedicated;supportLinks=true;EncryptionLevel=requested;EncryptionTypes=AES" 

From Clarity Version 16.0.3 Onwards:

• Oracle URL with Encryption - jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=*****)(PORT=****))(CONNECT_DATA=(SERVICE_NAME=***)(SERVER=dedicated))(SECURITY=(ENCRYPTION_CLIENT=requested)(ENCRYPTION_TYPES_CLIENT=AES256)))
• Oracle URL with SSL - jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=XXXXXX)(PORT=XXXX))(CONNECT_DATA=(SERVICE_NAME=XXXX)(SERVER=dedicated))(CLARITY_PARAMETERS=(javax.net.ssl.trustStore=/opt/keystore)(javax.net.ssl.trustStorePassword=XXX)))
  
  

SQL Server (MSSQL)

MSSQL DB Encryption is also supported following the Microsoft steps below:

Transparent Data Encryption (TDE)

JDBC driver may or may not be required to be modified with new parameters, depending on your SQL Server TDE configuration. 
Example of URL:

Till Clarity Version 16.0.2 : jdbc:clarity:sqlserver://DBSERVER:1433;DatabaseName=DB_PROD;InsensitiveResultSetBufferSize=0;ProgramName=Clarity;encryptionmethod=ssl;DateTimeInputParameterType=dateTime;

From Clarity Version 16.0.3 Onwards: 

• MSSQL with SSL & Encryption - jdbc:sqlserver://DBSERVER:1433;DatabaseName=niku;trustServerCertificate=false;encrypt=true;trustStore=c:/test;trustStorePassword=XXX
   

Additional Information

Also check Jaspersoft database encryption - Broadcom KB