Configuring Db2 Big SQL for Ranger TLS/SSL

Db2® Big SQL requires some manual configuration to work with TLS/SSL on Cloudera Data Platform.

Before you begin

Before proceeding, check that the following prerequisites are met:

You have enabled Ranger security support for Db2 Big SQL. For details, see Enabling and disabling Ranger security support for Db2 Big SQL.
You have configured Ranger Admin for TLS/SSL. For details, see TLS encryption using auto-TLS.

Cloudera Data Platform auto-TLS provides the keystore and truststore that are needed for Db2 Big SQL to access the Ranger Admin service. It also takes care of generating certificates and importing them into the keystore and truststore.

If you are using a custom TLS/SSL solution, create a keystore and truststore for the Db2 Big SQL service as you would for other CDP services (such as Hive). The additional requirement for Db2 Big SQL is to generate jceks files by using the IBM® JDK and to create the policymgr-ssl properties files. See Step 6 and Step 7, below.

Procedure

Complete the following steps to configure Db2 Big SQL for Ranger TLS/SSL. These steps assume that the Db2 Big SQL user ID is bigsql, and that the keytool executable is located in /usr/java/jdk1.8.0_232-cloudera/bin/keytool. Be sure to adjust the path, if necessary.

As root on the machine on which the Db2 Big SQL head node is located, open the terminal and run the following commands:

mkdir /home/bigsql/ranger
chown bigsql:hadoop /home/bigsql/ranger
cd /var/lib/cloudera-scm-agent/agent-cert/
cp cm-auto-host_keystore.jks /home/bigsql/ranger/

To make the /home/bigsql/ranger/cm-auto-host_keystore.jks file readable by the bigsql user, run one of the following commands:
```
chmod 644 /home/bigsql/ranger/cm-auto-host_keystore.jks
```
or
```
chown bigsql:hadoop /home/bigsql/ranger/cm-auto-host_keystore.jks
```
If you know the CM global truststore password, you can copy the global truststore into the /home/bigsql/ranger directory.
```
cp cm-auto-global_truststore.jks 
  /home/bigsql/ranger/bigsql_truststore.jks
```
The truststore password will be used in Step 6d.
If you do not know the CM global truststore password, import the global truststore into a local truststore and assign a password to it when prompted:
```
/usr/java/jdk1.8.0_232-cloudera/bin/keytool -importkeystore
  -srckeystore cm-auto-global_truststore.jks
  -destkeystore /home/bigsql/ranger/bigsql_truststore.jks
```
Press enter when prompted for the source truststore password, and take note of the destination password, because it is the truststore password that will be used in Step 6d.
Take note of the password that is stored in /var/lib/cloudera-scm-agent/agent-cert/cm-auto-host_key.pw.
It is the keystore password and it will be used in Step 6c.
As the bigsql user, create jceks files to store the keystore and truststore passwords in an encrypted form:
1. Run cd /home/bigsql/ranger
2. Run export PATH=~bigsql/sqllib/java/jdk64/bin:$PATH
3. Run the following command, using the keystore password that you found in cm-auto-host_key.pw as the -value option. The keystore password must be enclosed by single quotation marks.
```
java -cp "/opt/cloudera/parcels/CDH/lib/ranger-hive-plugin/install/lib/*"
  org.apache.ranger.credentialapi.buildks create sslKeyStore 
  -value 'FBGky7ZvrMf7aJPQikOiBr6KPS9wdbXRZIQPJ0Jp7aU'
  -provider jceks://file/home/bigsql/ranger/bigsql_cred.jceks
```
  Note: The /opt/cloudera/parcels/… path might need to be modified to reflect the lib/ranger-hive-plugin directory on your host.
4. Run the following command, using the truststore password, which is either the CM global truststore password if you followed Step 3, or the password with which you created the local truststore if you followed Step 4. The truststore password must be enclosed by single quotation marks.
```
java -cp "/opt/cloudera/parcels/CDH/lib/ranger-hive-plugin/install/lib/*"
  org.apache.ranger.credentialapi.buildks create sslTrustStore
  -value 'BigSql123' -provider jceks://file/home/bigsql/ranger/bigsql_cred_trust.jceks
```
  Note: The /opt/cloudera/parcels/… path might need to be modified to reflect the lib/ranger-hive-plugin directory on your host.

As root, complete the following steps:

Run cd /usr/ibmpacks/current/bigsql/bigsql/conf/

Verify that the property values in the ranger-policymgr-ssl.xml file match the keystore, truststore, and jceks file locations that were used in earlier steps.

If you chose a different location or name for these files, update the ranger-policymgr-ssl.xml file to reflect those changes.

<name>	<value>
`xasecure.policymgr.clientssl.keystore`	`/home/bigsql/ranger/cm-auto-host_keystore.jks`
`xasecure.policymgr.clientssl.truststore`	`/home/bigsql/ranger/bigsql_truststore.jks`
`xasecure.policymgr.clientssl.keystore.credential.file`	`jceks://file//home/bigsql/ranger/bigsql_cred.jceks`
`xasecure.policymgr.clientssl.truststore.credential.file`	`jceks://file//home/bigsql/ranger/bigsql_cred_trust.jceks`

Run cp ranger-policymgr-ssl.xml ranger-bigsql-policymgr-ssl.xml
Run cp ranger-policymgr-ssl.xml ranger-hive-policymgr-ssl.xml
Run cp ranger-policymgr-ssl.xml ranger-hbase-policymgr-ssl.xml
Run chmod 644 /usr/ibmpacks/bigsql/7.1.0.0/bigsql/conf/ranger-bigsql-policymgr-ssl.xml
Run chmod 644 /usr/ibmpacks/bigsql/7.1.0.0/bigsql/conf/ranger-hive-policymgr-ssl.xml
Run chmod 644 /usr/ibmpacks/bigsql/7.1.0.0/bigsql/conf/ranger-hbase-policymgr-ssl.xml

Restart Db2 Big SQL.
```
bigsql-admin -restart
```
Note: There is a known issue when TLS/SSL is enabled for the Apache Solr service. Db2 Big SQL is not able to audit to Ranger by using Solr.

What to do next

If you encounter any problems during this procedure, see Configuring Db2 Big SQL for Ranger TLS/SSL: Troubleshooting tips.