How to connect to a DB using SSL and a connection datasource in Mule 4

CONTENT

Since the DB connector does not provide an OOTB way to connect to a database through SSL, this article provides an alternative approach on how to do it.

STEPS TO FOLLOW

1. Create a simple application in Anypoint Studio
2. Refer to Spring based datasources, MULE 4 section.
3. By now you should have imported Spring Module, created a file like spring-config.xml under src/main/resources and refer to that file.
4. After that, you need to configure the beans inside spring-config.xml. Here is an example of how spring-config.xml should look like (make sure to include every namespace):

<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:jdbc="http://www.springframework.org/schema/jdbc"

    xsi:schemaLocation="http://www.springframework.org/schema/beans
      http://www.springframework.org/schema/beans/spring-beans-4.2.xsd
      http://www.springframework.org/schema/jdbc
      http://www.springframework.org/schema/jdbc/spring-jdbc-4.2.xsd
      http://www.springframework.org/schema/context
      http://www.springframework.org/schema/context/spring-context-4.2.xsd
      http://www.springframework.org/schema/security
      http://www.springframework.org/schema/security/spring-security-4.2.xsd">	
	
	<bean id="dataSource" class="oracle.jdbc.pool.OracleDataSource" destroy-method="close"> 
	   <property name="driverType" value="oracle.jdbc.driver.OracleDriver" />
		<property name="URL" value='jdbc:oracle:thin:@(DESCRIPTION= (ADDRESS=(PROTOCOL=TCPS)(PORT=1522)(HOST=<HOST>))(CONNECT_DATA=(SERVICE_NAME=<SERVICE_NAME>))(SECURITY = (SSL_SERVER_CERT_DN = "<SSL_CERVER_CERT_DN>")))' />
		<property name="User" value="<USER>" />
		<property name="Password" value="<PASSWORD>" />
		<property name="ConnectionProperties">
		<value>
		javax.net.ssl.trustStore:<PATH_TO_TRUSTSTORE>
		javax.net.ssl.trustStoreType:<TRUSTSTORE_TYPE>
		javax.net.ssl.trustStorePassword:<TRUSTSTORE_PASSWORD>
		</value>
		</property>
	</bean>
	</beans>

What do bold fields mean?

• id: the value of id will be used to fill the Data source ref field in DB connector (will be explained later)
• class: the class of the datasource of your choice. This is extremely important since it defines the fields you need to fill like driverType, URL, User, Password and ConnectionProperties. Notice that for this example, oracle.jdbc.pool.OracleDataSource is used and the fields can be retrieved from https://docs.oracle.com/cd/E16338_01/appdev.112/e13995/oracle/jdbc/pool/OracleDataSource.html. You need to find the documentation of the datasource of your choice.
• driverType: the driver of your choice. As mentioned before, this field is named like this because it belongs to oracle.jdbc.pool.OracleDataSource. It can be named in other datasources as "diverClass" or "driverClassName".
• ConnectionProperties: allows you to set SSL properties (and other properties of your desire). As mentioned before, this field is named like this because it belongs to oracle.jdbc.pool.OracleDataSource. It could be named differently in other datasources.
• javax.net.ssl.trustStore: the absolute path to the file where you store the certificates of the DB server you plan to connect to. If you plan to deploy to Cloudhub, please check Cloudhub path for connectors / processors that require absolute path.
• javax.net.ssl.trustStoreType  and javax.net.ssl.trustStorePassword are self-explaining.

Note: for 2-way SSL, you need to add the following properties as well:

• javax.net.ssl.keyStore=<PATH_TO_KEYSTORE>
• javax.net.ssl.keyStoreType:<KEYSTORE_TYPE> (usually jks)
• javax.net.ssl.keyStorePassword:<KEYSTORE_PASSWORD>

5. Now with the datasource properly configured, add the DB connector to the canvas.
6. Go to Global Elements --> Database Config --> General tab and Select Data Source Reference Connection
7. Add the driver of your choice (make sure it supports connections over SSL, otherwise it won't work)
8. Fill the Data source ref with the value of id of point 4. (datasource) in this example.

Now you are set and ready to test the connection. The motivation of this approach is to be used in Cloudhub where overriding the javax.net.ssl.trustStore property is not an option since it removes all existing certificates. However, it can be applied in on-prem environments as well.

For a sample Spring bean configuration for DB2, refer to the following article:

Properly Disposing DB2 driver

Disclaimer: This solution provides a suggestion that should be considered in conjunction with your specific use-case and requirements and does not represent a complete solution for all circumstances.