Secure (encrypted) connection to MySQL (MariaDB)

[sardern]

We're setting everything up for our JSS install (v.9.91) - we have our Jumpstart session scheduled for next week.

I have a question related to the database server that we are intending to have as a separate system that our JSS talks to. (We are using MariaDB 10.0.x.)

Now, we've setup the DB server to be fine talking over TLS, as shown by connecting via the command-line tools:

<JSS server> $ mysql --ssl -u jamfsoftware -p -h <JSS DB server> jamfsoftware
...
> status
...
>> SSL: Cipher in use is DHE-RSA-AES256-GCM-SHA384

Is there an "easy" way - through a configuration mod - to enable JSS to be happy talking to our database over TLS, rather than in plaintext? I don't, at this stage, need to have any more advanced client-cert setups - just a "regular" TLS session against the server-cert to protect the confidentiality of the connection as it travels across our network.

I've tested the setup by changing the GRANT privileges to include "REQUIRE SSL" on the database side and then our JSS service fails to connect to the DB.

Thanks for any help with this,

Steve

[peter · Accepted solution]

I'll preface this by saying that I don't believe that's an officially supported configuration, however...

Database connection settings are stored in ROOT/WEB-INF/xml/DataBase.xml. There's some documentation around that here. In that file is the line:

<jdbcParameters>?characterEncoding=utf8&amp;useUnicode=true&amp;jdbcCompliantTruncation=false</jdbcParameters>

Based on what I've seen on a JDBC connection string for connecting over TLS, you'll want to add the following parameters: verifyServerCertificate=true&useSSL=true&requireSSL=true, resulting in the following:

<jdbcParameters>?characterEncoding=utf8&amp;useUnicode=true&amp;jdbcCompliantTruncation=false&amp;verifyServerCertificate=true&amp;useSSL=true&amp;requireSSL=true</jdbcParameters>

You can also change the database hostname and port in that file.

I haven't tested this out, but this would be the place to start if you want to set up a TLS connection between the JSS and MySQL.

[sardern]

Thank you, Peter - this works. (Appreciating that it's NOT officially supported.)

[burdett]

Steve,

Great to hear that you got this working. I put in a request https://jamfnation.jamfsoftware.com/featureRequest.html?id=4779
to see if JAMF will officially support this.

[spalmer]

@sardern I just came across this thread when searching on JAMF Nation.

We had tried something like what @peter posted about back in November 2015 and had some issues. We also added the useSSL=true to our jdbcParameters and configured a custom truststore file in the SystemSettings.xml file

However, after a few weeks we realized that this also broke our ability to use VPP and Push Notifications. When we worked with JAMF Support we discovered that in the JSS logs there were a ton of VPP and Push Notification PKIX errors (I think these were errors related to SSL Certs if I remember correctly). As soon as we removed the useSSL=true parameter and the custom truststore these errors disappeared and everything was back to normal.

I was curious if you could provide some details on whether or not you had similar issues and how you overcame them. I noticed that in the Feature Request mentioned by burdett, https://jamfnation.jamfsoftware.com/featureRequest.html?id=4779 that someone else pointed to their custom truststore inside the jdbcParameters instead of the SystemProperties.xml file. Is that what you did as well?

[sardern]

I think that we kept everything as simple as possible - I just added our internal CA cert to the system-wide cacerts used by java - typically at /usr/java/latest/lib/security/cacerts [we run the server on linux.]

I didn't tinker around with the SystemProperties.xml file at all. Now, I've just checked our running setup and we don't appear to be getting any errors related to VPP and push notifications.

Now, there may be other areas where we're misconfigured but, so far, neither I or any of our admin level users have noticed anything being either screwy or outright broken.

[jcwoll]

I'm running the JSS on Windows.

I've imported our internal CA cert into Java.

I'm trying to encrypt the mysql connection between the DMZ JSS and the internal JSS.

I'm assuming that just editing the DataBase.xml would yield the same results that @sardern sees?

[grahamfw]

Anyone able to get this working? I'm not having a lot of luck.