Secure your Uptime MSSQL backend database connection with SSL!

Hey there Uptimers! There's been a lot of stuff in the news lately regarding cyber attacks and data security in general. Just this morning I was listing to a piece on the Wall Street Journal technology podcast about how big companies are trying to protect all of our data that is out there from being "de-anonymized." Interesting stuff. Well, today's blog post isn't going to be WSJ material, but, if you use MSSQL for your backend database, This might interest you, or your DBA, or at the very least, your security folks? OK!

Uptime Infrastructure Monitor basically runs as two Java virtual machines, a webserver (Apache) and a database backend (MySQL by default). To connect to that database from Java, we use JDBC. For MSSQL (today's topic) we use JDBC to do so, but we use the JTDS driver to do it. More about that in a second. So, after my morning coffee rollercoaster reached it's height, I decided to try to secure this connection. It is after all, all the rage right now! JDBC allows for various options in its connection string which we allow you to configure in uptime/uptime.conf for the data collector JVM and in uptime/controller/resources/uptime-controller.conf for the JVM (Jetty) serving our API. If you or your team utilizes SSL on the SQL server that your Uptime DB resides on, but you aren't securing Uptime today, this is going to be a piece of cake. If you would LIKE to start using SSL between Uptime and your MSSQL backend but you're not currently using SSL on SQL server today, this is a bit more involved and, while I'm not going to cover it in this article, suffice it to say that your DBA can help you out or if you are also the DBA and you don't have a certificate to use to configure SQL with, I'd recommend generating a self-signed certificate using IIS 8 management tools. (Longest sentence ever…) I spent an inordinate amount of time trying to use OpenSSL (which I normally use for Java projects) and it took about 30 seconds to get it working with IIS management tools. And yes, I know, production, no self-signed certs, etc… ANYWAY, here's how you secure UPTIME.

First you'll need to be using Microsoft JDBC driver for SQL Server.

https://www.microsoft.com/en-us/download/details.aspx?id=54629

Download the file and unpack it!

Next, copy sqljdbc4.jar from the Microsoft JDBC Driver 4.0 for SQL Serversqljdbc_4.0enu folder into your uptime/core folder.

Now open uptime/uptime.conf and find your database config info. It should be up to or close to the top of the file mixed in with some other db example more than likely (you can ignore Scott and his tiger!), like so

dbType=mssql
dbHostname=uptime-sql1.idera.loc
dbPort=1433
dbName=uptime
dbUsername=uptime
dbPassword=guest

just under that you'll see "dbJdbcProperties" and "dbUrlString". Uncomment them and make them look like this, obviously supply your own info…

#optional additional SQL Server configuration
dbJdbcProperties=instance=MSSQLSERVER
#To use the sqljdbc4.jar instead of default jtds jar, uncomment the following two lines, place the jar file in uptime/core folder and restart the collector service.
dbDriverClass=com.microsoft.sqlserver.jdbc.SQLServerDriver
dbUrlString=jdbc:sqlserver://uptime-sql1.idera.loc:1433;databaseName=uptime;encrypt=true;trustServerCertificate=true

The key difference between the above and the example you'll see is the end of "dbUrlString" which has

added to the end of it.. (the semicolon separates the parameters so do not forget it between databaseName and encrypt…)

Save the file and restart your uptime_core (linux) or Uptime data collector (Windows) service. In a few seconds everything should come back up and you should be encrypted. To test this you can run the following query on the DB server we're connecting to.