New post
Article
· Nov 9, 2023 3m read

Connecting to Cloud SQL with DBeaver using SSL/TLS

#Cloud #JDBC #Security #SQL #SSL #InterSystems IRIS

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.

📺If you prefer popcorn over scrolling, check out this video in which we walk you through the steps captured below.

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 

Discussion (20)5
Log in or sign up to continue
Sylvain Guilbaud · Jan 25, 2024

Hi @Benjamin De Boe,

thanks for this useful article.

I'm not able to connect to a running IRIS Cloud SQL instance, neither from DBeaver nor from a simple java test.

DBeaver :

Version 23.3.3.202401211839

Java :

java checkConnection
checkConnection caught exception: java.sql.SQLException: [InterSystems IRIS JDBC] Communication link failure: Socket is closed

import java.sql.*;
import javax.sql.*;
import com.intersystems.jdbc.*;
import java.sql.Connection;

public class checkConnection{
  public static void main(String[] args) {
    try {

      String dbUrl = 
        "jdbc:IRIS://k8s-092c0f86-acbb1223-47d44444fb-7667ad082a377a9e.elb.us-east-1.amazonaws.com:443/USER"; 
      String user = "SQLAdmin";
      String pass = "********";

      IRISDataSource ds = new IRISDataSource();
      ds.setURL(dbUrl);
      ds.setUser(user);
      ds.setPassword(pass);
      ds.setConnectionSecurityLevel(10);
      Connection dbconnection = ds.getConnection();
      System.out.println("Connected to InterSystems IRIS via JDBC.");


     
// Use IRISDataSource to open a connection
// Execute a query and get a scrollable, updatable result set.
      String sql="Select sysdate as now";
      PreparedStatement pstmt = dbconnection.prepareStatement(sql);
      java.sql.ResultSet rs = pstmt.executeQuery();

// Move to the first row of the result set and change the name.
      rs.next();
      System.out.println("\n date = " + rs.getString("now"));


// Close objects and catch any exceptions.
      pstmt.close();
      rs.close();
      dbconnection.close();
    } catch (Exception ex) {
      System.out.println("checkConnection caught exception: "
             + ex.getClass().getName() + ": " + ex.getMessage());
    }
  } // end main()
} // end class checkConnection
Java
Java

As you can see keystore.jks and SSLConfig.properties are in the same directory as the java class :

After the test, the javatls.log is empty

0 0
Sylvain Guilbaud Jan 25, 2024 to Sylvain Guilbaud

If I activate the debug in the SSLConfig.properties, I don't receive too much information :

logFile (class java.lang.String) = javatls.log
protocol (class java.lang.String) = TLSv1.3
cipherSuites (class java.lang.String) = TLS_AES_256_GCM_SHA384
trustStore (class java.lang.String) = keystore.jks
trustStoreType (class java.lang.String) = JKS
trustStorePassword (class java.lang.String) = 123456
RNG seeding time 1 msec
Supported CipherSuites:
[TLS_AES_256_GCM_SHA384, TLS_AES_128_GCM_SHA256, TLS_CHACHA20_POLY1305_SHA256, TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384, TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256, TLS_ECDHE_ECDSA_WITH_CHACHA20_POLY1305_SHA256, TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384, TLS_ECDHE_RSA_WITH_CHACHA20_POLY1305_SHA256, TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256, TLS_DHE_RSA_WITH_AES_256_GCM_SHA384, TLS_DHE_RSA_WITH_CHACHA20_POLY1305_SHA256, TLS_DHE_DSS_WITH_AES_256_GCM_SHA384, TLS_DHE_RSA_WITH_AES_128_GCM_SHA256, TLS_DHE_DSS_WITH_AES_128_GCM_SHA256, TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384, TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384, TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256, TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256, TLS_DHE_RSA_WITH_AES_256_CBC_SHA256, TLS_DHE_DSS_WITH_AES_256_CBC_SHA256, TLS_DHE_RSA_WITH_AES_128_CBC_SHA256, TLS_DHE_DSS_WITH_AES_128_CBC_SHA256, TLS_ECDH_ECDSA_WITH_AES_256_GCM_SHA384, TLS_ECDH_RSA_WITH_AES_256_GCM_SHA384, TLS_ECDH_ECDSA_WITH_AES_128_GCM_SHA256, TLS_ECDH_RSA_WITH_AES_128_GCM_SHA256, TLS_ECDH_ECDSA_WITH_AES_256_CBC_SHA384, TLS_ECDH_RSA_WITH_AES_256_CBC_SHA384, TLS_ECDH_ECDSA_WITH_AES_128_CBC_SHA256, TLS_ECDH_RSA_WITH_AES_128_CBC_SHA256, TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA, TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA, TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA, TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA, TLS_DHE_RSA_WITH_AES_256_CBC_SHA, TLS_DHE_DSS_WITH_AES_256_CBC_SHA, TLS_DHE_RSA_WITH_AES_128_CBC_SHA, TLS_DHE_DSS_WITH_AES_128_CBC_SHA, TLS_ECDH_ECDSA_WITH_AES_256_CBC_SHA, TLS_ECDH_RSA_WITH_AES_256_CBC_SHA, TLS_ECDH_ECDSA_WITH_AES_128_CBC_SHA, TLS_ECDH_RSA_WITH_AES_128_CBC_SHA, TLS_RSA_WITH_AES_256_GCM_SHA384, TLS_RSA_WITH_AES_128_GCM_SHA256, TLS_RSA_WITH_AES_256_CBC_SHA256, TLS_RSA_WITH_AES_128_CBC_SHA256, TLS_RSA_WITH_AES_256_CBC_SHA, TLS_RSA_WITH_AES_128_CBC_SHA, TLS_EMPTY_RENEGOTIATION_INFO_SCSV]
Enabled CipherSuites:
[TLS_AES_256_GCM_SHA384]

Bash
Bash
0 0
Sylvain Guilbaud Jan 25, 2024 to Evgeny Shvarov

Hi @Evgeny Shvarov 
keystore.jks and SSLConfig.properties are well copied in /Applications/DBeaver.app/Contents/MacOS (and even /Applications/DBeaver.app/Contents/Eclipse) ; but DBeaver still answers :

[InterSystems IRIS JDBC] Communication link failure: Socket is closed

As you've seen I've also made a test outside DBeaver, with a simple java test which fails as well, with the same message.

And all configuration files are located in my local java directory test.

I've regenerated the keystore.jks several times with the keytool command. Same result.

0 0
Sylvain Guilbaud Jan 25, 2024 to Sylvain Guilbaud

Despite it still not work for me with DBeaver/Java, the good news it that it works with Python :

import iris
import ssl
import time
 
def main():
    connection_string = "k8s-092c0f86-acbb1223-47d44444fb-7667ad082a377a9e.elb.us-east-1.amazonaws.com:443/USER"
    username = "SQLAdmin"
    password = "********"
 
    context = ssl.SSLContext(ssl.PROTOCOL_TLS_CLIENT)
    context.verify_mode=ssl.CERT_REQUIRED
    context.check_hostname = False
    context.load_verify_locations("certificateSQLaaS.pem")

    connection = iris.connect(connection_string, username, password, sslcontext=context)
 
    print("connected")
    # when finished, use the line below to close the connection
    time.sleep(55)
    connection.close()
    print("disconnected")
 
if __name__ == "__main__":
    main()
Python
Python

1 0
Sylvain Guilbaud Jan 25, 2024 to Sylvain Guilbaud

Here a simple python test used :

import iris
import ssl
import getpass
import os
 
def main():
    connection_string = "k8s-092c0f86-acbb1223-47d44444fb-7667ad082a377a9e.elb.us-east-1.amazonaws.com:443/USER"
    try:
        username = os.environ['CLOUDLOGIN']
    except:
        username = getpass.getpass('Login:')
    try:
        password = os.environ['CLOUDPASSWORD']
    except:
        password = getpass.getpass('Password:')

    context = ssl.SSLContext(ssl.PROTOCOL_TLS_CLIENT)
    context.verify_mode=ssl.CERT_REQUIRED
    context.check_hostname = False
    context.load_verify_locations("certificateSQLaaS.pem")

    connection = iris.connect(connection_string, username, password, sslcontext=context)
 
    print("connected")
    tablename = "data.movie"
    cursor = connection.cursor()
    try:
        cursor.execute("DROP TABLE "+tablename)   
        print(tablename+" dropped succesfully") 
    except InterfaceError as err:
        print(f"Unexpected {err=}")
    except Exception as err:
        print("ERROR WHILE DROPPING TABLE "+tablename)
        print(f"Unexpected {err=}, {type(err)=}") 
    try:
        cursor.execute("CREATE TABLE "+tablename+" (title varchar(500), year int, score numeric)")
        print(tablename+" created succesfully")
    except Exception as err:
        print("ERROR WHILE CREATING TABLE"+tablename) 
        print(f"Unexpected {err=}, {type(err)=}")
    data = [
        ("Monty Python Live at the Hollywood Bowl", 1982, 7.9),
        ("Monty Python's The Meaning of Life", 1983, 7.5),
        ("Monty Python's Life of Brian", 1979, 8.0),
    ]
    try:
        cursor.executemany("INSERT INTO "+tablename+" VALUES(?, ?, ?)", data)
        print("data succesfully inserted in "+tablename)

    except Exception as err:
        print("ERROR WHILE INSERTING DATA IN"+tablename) 
        print(f"Unexpected {err=}, {type(err)=}")
    connection.commit()
    connection.close()
    print("disconnected")
 
if __name__ == "__main__":
    main()
Python
Python

2 1