Data Engineering Administrator Guide > Authorization > Configuring Access to an SSL-Enabled Database
  

Configuring Access to an SSL-Enabled Database

When you use a JDBC connection, you can configure Sqoop connectivity to SSL-enabled databases.
You can include a SSL-enabled JDBC-compliant database as a Sqoop source or target in an Informatica mapping that runs in a Hadoop environment.
For example, you can include the following sources or targets from SSL-enabled JDBC-compliant databases in a Sqoop mapping:

Configure the JDBC Connection for SSL-Enabled Databases

You must configure the Sqoop properties in the JDBC connection to connect to SSL-enabled databases such as Microsoft SQL Server and Oracle.
When you configure a JDBC connection, add the appropriate JDBC connection string for the SSL-enabled database that you want to connect to in the JDBC connection properties. Specify the JDBC connection string in the connect Sqoop argument which Sqoop must use to connect to the SSL database.
To configure Sqoop connectivity to an SSL-enabled Oracle database, you must additionally create a parameter file with the required SSL properties. Provide the location of the file through the --connection-param-file <parameter_file_location> Sqoop argument in the JDBC connection.

Configuring Sqoop Connectivity to an SSL-Enabled Oracle Database

The secure Oracle database that you want to connect to must use the PKCS12 wallet with the keystore and truststore, TLS_RSA_WITH_AES_256_CBC_SHA cipher algorithm, and TLS version 1.2.
To configure Sqoop connectivity to the Oracle database, add the dependant jars for using Oracle wallets and enable the Oracle PKI provider. Create a parameter file with the SSL properties required to connect to the secure Oracle database and specify the location of this file through the Sqoop argument in the JDBC connection.
    1. Create a parameter file and include the following SSL properties in the file:
    oracle.jdbc.J2EE13Compliant=true
    javax.net.ssl.trustStore=/tmp/truststore.p12
    javax.net.ssl.trustStoreType=PKCS12
    javax.net.ssl.trustStorePassword=informatica
    javax.net.ssl.keyStore=/tmp/ewallet.p12
    javax.net.ssl.keyStoreType=PKCS12
    javax.net.ssl.keyStorePassword=oracle4u
    2. Place the parameter file in a common path in the Data Integration Service machine and in all the nodes of the Hadoop cluster. For example, /tmp/param_file
    3. Open the java.security file in the following location of the Data Integration Service machine and in all the nodes in the Hadoop cluster: <JAVA_HOME>/jre/lib/security
    4. To enable the Oracle PKI provider, add the following property at position 3 in the java.security file:
    security.provider.3=oracle.security.pki.OraclePKIProvider
    When you add the property at position 3, the rest of the existing properties from position 3 shifts to subsequent positions.
    5. Copy the following dependent .jar files to use the Oracle wallet from the Oracle web site:
    6. Paste the .jar files to the following directory on the machine where the Data Integration Service runs: <Informatica installation directory>/externaljdbcjars
    7. Paste the following .jar files at: <Informatica installation directory>/java/jre/lib/ext/
    8. In the JDBC connection, provide the connection string for the JDBC driver:
    Sample connection string for DataDirect Oracle JDBC driver:
    jdbc:informatica:oracle://<host_name>:<port>;CatalogOptions=6;
    ServiceName=<service_name>;
    trustStorePassword=<truststore_password>;
    keyStorePassword=<keystore_password>;CryptoProtocolVersion=TLSv1.2;
    keyStore=<keystore_location_of_ewallet.p12_file>;
    trustStore=<truststore_location_of_truststore.p12_file>;
    HostNameInCertificate=<database_host_name>;encryptionMethod=SSL;
    ValidateServerCertificate=True;
    Sample connection string for Oracle JDBC driver:
    odbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)
    (HOST=<host>)(PORT=<port_number>))
    (CONNECT_DATA=(SERVICE_NAME=<service_name>)))"
    9. Specify the following Sqoop argument in the JDBC connection to connect to an SSL-enabled Oracle database:
    --connect jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=<host>)(PORT=<port_number>))
    (CONNECT_DATA=(SERVICE_NAME=<service_name>)))"
    10. Specify the following Sqoop argument in the JDBC connection to use the SSL properties defined in parameter file:
    --connection-param-file <parameter_file_location>,
    where parameter_file_location is the path of the configured parameter file that contains the SSL properties:
    11. Recycle the Data Integration Service.