Connecting to Cloud SQL with DBeaver using SSL/TLS

With the release of InterSystems IRIS Cloud SQL, we're getting more frequent questions about how to establish secure connections over JDBC and other driver technologies. While we have nice summary and detailed documentation on the driver technologies themselves, our documentation does not go as far to describe individual client tools, such as our personal favourite DBeaver. In this article, we'll describe the steps to create a secure connection from DBeaver to your Cloud SQL deployment.

Step 0: Creating your deployment

First, log into the Cloud Services Portal and create a Cloud SQL deployment. The one thing you need to be mindful of is to check the box to enable external connections. Other than that, all the default settings should work fine.

Step 1: Installing the certificate

In order to connect securely, we'll use certificates to encrypt everything that gets sent over the wire. You can download the certificate from the deployment details page through the "Get X.509 certificate" button:

We then need to store this certificate in a trusted keystore using the keytool utility. This is a standard piece of Java infrastructure, so nothing specific to IRIS or DBeaver at this point. Use the command below to import the certificate. The location of your certificateSQLaaS.pem certificate file does not matter after running this command, so you can delete it from your downloads folder afterwards. The location of the keystore.jks file does matter, so make sure you run the command from a folder where it makes sense and is safe from uninstall or upgrade surprises, such as a cert directory in your user's home dir. The -alias is optional, but helpful when you intend to reuse the same keystore file to store multiple certificates.

keytool -importcert -file path-to-cert/cert-file.pem -keystore keystore.jks -alias myDeploymentName

For more details, see the documentation.

Step 2: Create an SSLConfig.properties file

Next, we'll need to tell the IRIS JDBC driver how to find this keystore, which is accomplished through an SSLConfig.properties file. This simple text file needs to be placed in the working directory of the Java program that'll open the JDBC connection. On Windows, that's %LOCALAPPDATA%\DBeaver, which translates to C:\Users\<you>\AppData\Local\DBeaver. On Mac, it's typically /Applications/DBeaverEE.app/Contents/MacOS. As an alternative, you can also create the file elsewhere and set the full path as an environment variable named com.intersystems.SSLConfigFile.

In its simplest form, this file only needs to point to the keystore and include the password. Note that the path to your keystore.jks file needs to be properly escaped for Java to read it, so on windows you'll need to use double backslashes.

trustStore=/path/to/keystore/keystore.jks
trustStorePassword=keystore-password

There are many additional settings you can configure through this file described in the docs, including named configurations, but the above is sufficient.

Step 3: Create your DBeaver connection

Now that we installed our certificate and specified where IRIS JDBC can find it, we can create our DBeaver connection. All of the settings for the "main" tab in the connection creation dialog can be found in the deployment details screen as pasted above:

The only thing left to do is to tell DBeaver to enable encryption, which is accomplished by setting the "connection security level" to 10 in the "Driver properties" tab:

That's it! If you click "Test Connection", you should get a thumbs up, or a useful error message. In the latter case, check out this troubleshooting document if it's not obvious what to change.

Note for Mac users

If you're using a mac there seems to be a bug in DBeaver where the above may not be sufficient. The work around is unconventional but it does work. In the Database/Schema field, where you'd normally put 'USER' put this full string instead:

USER:sslConnection=true;sslTrustStoreLocation=/pathToTruststore/truststore.jks;sslTrustStorePassword=123456;

Tip and misc other wisdom courtesy of @Rick Guidice