Secure your Uptime MSSQL backend database connection with SSL!

by Jul 11, 2017

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. Soafter 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.

SELECT * FROM sys.dm_exec_connections
WHERE auth_scheme = 'SQL' and net_transport = 'TCP'
ORDER BY client_net_address
 
In the output, you should see several connections from your monitoring station's IP (shown in client_net_address) and the "encrypt_option" column should say true on all or almost all of them, which brings us to the last little bit. We need to secure the connection between SQL and the controller service as well! If you closed it, open up uptime.conf against and copy the database properties out of it so we can paste into our uptime-controller.conf file. everything from dbtype down the dbUrlString…

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

#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

Now open uptime/controller/resources/uptime-controller.conf, and replace your connection info in there with the one you copied.
 
Last, restart your Uptime_controller (linux) or Uptime Controller (Windows) service, and that's it. You've secured your DB connection.
 
But wait there's more? If you're using any of a variety of gadgets that connect directly to the database, you would have had to set up an ODBC connection to do so. While those gadgets will likely still work without running secure, why would you want to? Simply edit your ODBC connection and tell it to use SSL. It's that simple!
 
Now when you run that query, you shouldn't see any insecure connection coming from your monitoring station.