MuleSoft – MsSQL JDBC connection over SSL

plektonlabs-mssql-jdbc-ssl-scaled

Once upon a time, there was a Microsoft SQL Server named DB007 running in a data center in an enterprise organization in Toronto. DB007 was sad. It doesn’t get much attention due to the high demand in the SAAS based database solution. Everyone is excited talking about the cloud-based solution, NoSQL, data lakes, etc. However very few people considering storing data into DB007.

All of a sudden, a MuleSoft developer named Tani decided to use DB007 for storing some event information. DB007 got very excited and thankful to Tani. It promised to work hard and cooperate with Tani at its best. The next morning, DB007 got more surprised to see Tani already build a MuleSoft API to persist data into DB007 in just one day and her API started to push plenty of useful event data for testing. DB007 was waiting for this day for a long time and was started dreaming about the go-live date.

After a meeting with the security team in the organization, Tani started looking bit worry. She got a requirement from them about connecting to DB007 from MuleSoft more secure way. MuleSoft API will be deployed in anypoint platform, an Amazon cloud infrastructure and will be connecting to DB007 running in the organization’s data-center. MuleSoft has a connector that uses MsSQL JDBC driver to connect to any Microsoft SQL server like DB007 using TCP protocol. That means any data communication between the API and DB007 is exposed to the internet and anyone can trap the communication channel and retrieve all the secret data. That is why the security team wants communication encrypted. DB007 wishes that Tani can solve this problem and make its dream true.

Tani was already familiar with SSL encryption. She configured HTTPS in many many MuleSoft APIs very easily and quickly to have the communication encrypted and secured. She is also familiar with public key infrastructure (PKI) certificate SSL authentication in client-server communication technology. In the case of HTTPS communication, she used to configure a TLS context with the trust store and/or a key store and refer the TLS context in her HTTPS configuration. However, she doesn’t see any easy way to do the same in the database connector. DB007 found Tani very determined to find the solution. She asked the network team to create an SSL certificate for DB007 and sign the SSL certificate by the organization certificate authority. Ask the DB administrator to install the signed certificate into DB007 and turn the encryption configuration ON. However, she is not sure how to tell the MuleSoft database connector to use a TLS context.

Problem: How to configure a database connector to turn on the SSL encryption.

Solution: To solve the problem Tani followed the steps below:

  1. Got the organization root certificate named plektonlabs-root.cer from the network team and created a trust store named plektonlabs-truststore.jks using the following command in keytool shipped with JDK:
    keytool -import -file C:\certificates\plektonlabs-root.cert -alias plektonlabs-root -keystore plektonlabs-root.jks

    she also sets the trust store password to access plektonlabs-root.jks
  2. Then she had the trust store that signed the certificate installed into DB007. So, she copied the plektonlabs-root.jks into the MuleSoft project

    plektonlabs-root.jks
  3. But what is next?
    After doing some research on the JDBC connection she found few flags to secure the communication using SSL.

    encrypt: a boolean flag needs to set in the JDBC connection string. Tani sets the value to true.

    trustServerCertificate: a boolean flag default to true. If the value is true, the JDBC driver trusts any certificate exposed by the server. But Tani does not want to make the connection vulnerable to a bad person who can pretend as the server and expose any certificate to gain the trust and steal information. Therefore, Tani set the value to false.

    trustStore: As the previous flag trustServerCertificate is set to false, a trust store needs to be provided to the JDBC driver so that it can check if the exposed certificate or its root certificate is included in the trust store. Tani already created and added the trustStore named plektonlabs-root.jks which includes the organization’s root certificate.

    trustStorePassword: Tani already sets a trust store password “changeit” while creating the trust store. Now she needs to provide the password so that JDBC driver can open up the trust store and compare it with the exposed server certificates.

    hostNameInCertificate: the database hostname. Without this flag, it is highly likely that JDBC will give an error saying that the resolved IP does not match with the hostname mentioned in the certificate. Because usually CA issue the certificate to a specific host with its domain name (can be wild card domain too). Calling DB with IP won’t match the hostname in the certificate. So, JDBC will reject the SSL handshake.

    These are how Tani’s configuration looks like in the database configuration in MuleSoft anypoint studio.

    plektonlabs-db-config-ssl


    plektonlabs-mulesoft-jdbc-ssl

After a couple of tests, Tani found it is working as expected and DB007 started getting data again with proper encryption. Tani used the following environment config and versions to test it.
Anypoint Studio: 7.4.1
MuleSoft Runtime: 4.2.1
MsSQL JDBC Driver: 4
Microsoft SQL Server 2017
AdopOpenJDK 1.8

DB007 become so happy after going live. It wishes that everyone knows how to configure the MsSQL JDBC connection over SSL. Tani did not stop there. She did some more research and found that the MySQL JDBC driver uses the different sets of properties to connect over SSL.

verifyServerCertificate=true
useSSL=true,
requireSSL=true: It is used to enforce the server using SSL. If the server does not configure SSL, the connection will fail.
clientCertificateKeyStoreUrl=<trust-store-path>
clientCertificateKeyStorePassword=<trust-store-pass>

Thanks to Tani and MuleSoft helping for DB007. We will share few more stories on MuleSoft integration from Tani in the future. Until then stay tuned!

To read more MuleSoft related blogs and case study, please visit: www.plektonlabs.com/blog

Share This Post

Share on facebook
Share on linkedin
Share on twitter
Share on email

More To Explore

Blog

The Use of APIs in the Business Supply chain

Supply chains that mostly rely on legacy EDI (electronic data interchange) systems, a technology introduced over half a century ago to support communication with business

Blog

The 5 Foundations of API Governance

For many, governance is a necessary but tedious task. But when done right, governance offers a clear direction, breaks down barriers, and enables different parts

How can we help?

A little about yourself and we're ready to go

We pride ourselves on swift communication and prompt responses. Let us know what you're thinking and how we can help you.

Contact Information​

Head Office
75 Cornwall Drive, Ajax ON L1T3G2

Toronto Office
602 – 8133 Warden Ave, Markham, ON L6G 1B3, Canada

Phone: +14168902757
Email: info@plektonlabs.com

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.