Accept concatenated PEM files for trustStore (aka make it easy to use SSL with Amazon RDS)

[eirikbakke]

Is your feature request related to a problem? If so, please give a short summary of the problem and how the feature would resolve it

To connect securely to a SQL Server instance, the trustStore connection property must point to a file that contains any necessary root certificate(s) and intermediate certificates. Currently, this file must be in the Java KeyStore (jks) or PKCS-12 (pk8/pfx) format. For databases hosted on Amazon RDS, however, these certificates are provided in a concatenated PEM format (global-bundle.pem which can be downloaded here).

The problem is that there is no straightforward way to convert from PEM to an acceptable JKS or PKCS12 format when the original PEM file contains many concatenated certificates. One ends up needing a script or custom tool (referenced in this StackOverflow question) that splits up the individual certificates in the PEM file and inserts them into the keystore.

Describe the preferred solution

The MSSQL JDBC driver should accept a concatenated PEM file for the trustStore parameter (in addition to any previously supported formats).

Describe alternatives you've considered

For me, the working approach was the aforementioned script, which after fixing one bug, produced a JKS file that allowed me to connect to MSSQL on RDS with JDBC without trustServerCertificate=true. This is not an approach I can easily document to other users, however.

The "easier" approach which did not work was the following command:

openssl pkcs12 -export -in global-bundle.pem -nokeys -out global-bundle.pfx

The resulting PFX file, while it does contain certificate data, was not in a form that worked as a keystore for the SQL Server JDBC driver. (Curiously, though, I was able to convert the working JKS file to a working PFX file with keytool.)

Additional context

I came upon this problem while writing documentation for Ultorg, a graphical database tool. I need simple steps to get SSL working that works on MacOS, Linux, and Windows. So I can't provide scripts that won't work on Windows etc., and I can't depend on certificates living in the Windows-native certificate stores etc.

Reference Documentations/Specifications

https://learn.microsoft.com/en-us/sql/connect/jdbc/connecting-with-ssl-encryption?view=sql-server-ver16

Reference Implementation

The PostgreSQL JDBC driver does accept concatenated PEM files for the sslrootcert property (I successfully tried it with global-bundle.pem from Amazon RDS). It looks like only a few lines of JDBC driver code would be needed to support it in the MSSQL JDBC driver. Basically:

KeyStore ks = KeyStore.getInstance("jks");
CertificateFactory cf = CertificateFactory.getInstance("X.509");
Object[] certs = cf.generateCertificates(new FileInputStream(TRUST_STORE_FILE_PATH)).toArray(new Certificate[]{});
ks.load(null, null);
for (int i = 0; i < certs.length; i++)
    ks.setCertificateEntry("cert" + i, (Certificate) certs[i]);

For comparison, the relevant code in the MSSQL JDBC driver is here.

[barryw-mssql]

Hi Eirik,

I'm not sure why you are claiming that there is no straight forward way to convert a PEM file to a JKS. I was able to do so using the Java keytool as follows:

keytool.exe -import -file global-bundle.pem -alias testGlobal-BundleTruststore -keystore myTestGlobal-BundleTruststore

When I list, the contents, it appears there are multiple objects in the JKS:

keytool.exe -list -v -keystore myTestGlobal-BundleTruststore

Please let us know if this helps.

Thx,
Barry

[eirikbakke]

Thank you for your response!

When I list, the contents, it appears there are multiple objects in the JKS:
keytool.exe -list -v -keystore myTestGlobal-BundleTruststore

The latter command includes the line "Your keystore contains 1 entry" in its output. And the JKS file is only 1.5KB large, compared to 180KB of the (base64-encoded) global-bundle.pem file.

When this small JKS file is used as a trustStore during a connection to MSSQL on AWS RDS, the connection fails due to a missing certificate ("PKIX path building failed ... unable to find valid certification path to requested target").

By contrast, the JKS file that's generated with the following commands is 142KB large, takes a while to generate, and permits a successful SSL connection:

awk 'split_after == 1 {n++;split_after=0} /-----END CERTIFICATE-----/ {split_after=1}{print > "onecert" n ".pem"}' < global-bundle.pem
find . -name 'onecert*.pem' -exec \
        keytool -import -file {} -alias {} -keystore myroots.jks -noprompt -storepass passwd \;

So the latter commands are still the best working approach I have been able to come up with.

[Jeffery-Wasty]

We'll be adding this to our backlog, with the intention to reassess at the start of our next semester. If anyone else is also interested in this enhancement, please comment below, as this will help us in prioritizing work for this issue.