jdbc - How to connect to database with SSL in google apps script?

Question

I'm trying to connect to a database via SSL within Google Apps Script, referencing these docs. The error is:

Execution failed: Failed to establish a database connection. Check connection string, username and password.

I can use these exact same parameters from another db client (Sequel Pro) and it works fine. I've got the db accepting connections from any IP address (0.0.0.0/0).

I believe I've eliminated all the other variables (user name, password, etc. etc.), its only when I attempt to use SSL within Apps Script that it fails.

Can anyone provide a working example of connecting to a MySQL database with SSL within Google Apps Script?

My apps script:

function connectDb() {
  var address = 'x.x.x.x';  // no, I'm not literally using x's in my ip address

  var instanceUrl = 'jdbc:mysql://' + address + ':3306/';

  var clientSslKey = '-----BEGIN RSA PRIVATE KEY-----
' +
    'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
' +
    /* snip */
    '-----END RSA PRIVATE KEY-----';

  var clientSslCertificate = '-----BEGIN CERTIFICATE-----
' +
    'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
' +
    /* snip */
    '-----END CERTIFICATE-----';

  var serverSslCertificate = '-----BEGIN CERTIFICATE-----
' +
    'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
' +
    /* snip */
    '-----END CERTIFICATE-----';

  // https://developers.google.com/apps-script/reference/jdbc/jdbc#getconnectionurl-info
  var connParams = {
    user: 'temp',
    // no password set
    _serverSslCertificate: serverSslCertificate,
    _clientSslCertificate: clientSslCertificate,
    _clientSslKey: clientSslKey,
  };
  var conn = Jdbc.getConnection(instanceUrl, connParams);
}

EDIT

I filed a bug here which got marked as a duplicate of this other one which as a "P2" priority. Maybe that means it will be fixed soonish?

Answer 1

I can confirm that I can connect to a MySQL database with SSL within Google Apps Script.

It's important to note useSSL=true is indeed necessary

I was able to get it working by following the example at https://issuetracker.google.com/issues/36761592#comment18 (relevant snippet repeated below):

var conn = Jdbc.getConnection('jdbc:mysql://<ip address>/<db name>?useSSL=true', { 
  user: '<user>', 
  password: '<pass>', 
  _serverSslCertificate: '-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----', 
  _clientSslCertificate: '-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----', 
  _clientSslKey: '-----BEGIN RSA PRIVATE KEY-----
...
-----END RSA PRIVATE KEY-----' 
});

If you're using a Google Cloud SQL instance, you may want to consider using getCloudSqlConnection(). Presumably it's encrypted as well (based on the fact that I can indeed get a connection when the corresponding instance has "Only secured connections are allowed to connect to this instance." enabled), and, importantly, doesn't require you to manage (i.e. not lose control of) the key and certs yourself.