CFML 2016 connection string mysql over ssh

[tims4831424]

Hi docs anybody knows, how I can connect to an external database over ssh ?

Kindly regards.

Thorsten

[pete_freitag]

Hi Thorsten,

Typically that would be done over TLS (formerly known as SSL) not ssh, though you could probably create a SSH tunnel. Using TLS is probably what you want for an encrypted communication channel.

Typically the process I have used is to create a ca certificate, public key and private key on the mysql server, then import the CA certificate into a PKCS12 keystore. From there you have to use the JDBC connection string to tell the datasource to use SSL and point to a keystore file. For example:

useSSL=true&requireSSL=true&trustCertificateKeyStoreUrl=file:///config/mysql/mysql-ca-truststore.p12&trustCertificateKeyStoreType=PKCS12&trustCertificateKeyStorePassword=whatever

For reference, and for instructions on how to do the MySQL side of it, take a look at the MySQL Docs: MySQL :: MySQL Connector/J 8.0 Developer Guide :: 6.7 Connecting Securely Using SSL

If your MySQL Server supports TLS 1.2 (the community edition doesn't by default, see my blog entry about that) you may also want to add enabledTLSProtocols=TLSv1.2

--

Pete Freitag

Foundeo Inc.

[tims4831424]

Hi Pete,

thank you for your replay.

The Problem is, the Server where is installed Mysql does not accept connections from outside.

I only can connect with localhost.

[pete_freitag]

There is a setting in MySQL that can be changed to allow connections, you would then also need to open the mysql port in the firewall for traffic coming from your CF server's IP.

I'd probably go for the above route, but if it really is not an option, then you can create a SSH tunnel, for example:

Assuming you are on a unix OS on your CF server you would run this:

ssh -L  33306:127.0.0.1:3306 user@mysql-server.example.com

This creates a tunnel on port 33306 on 127.0.0.1 which points to port 3306 on mysql-server.example.com

You would of course need to make sure the tunnel stays up all the time (which is why it is probably better to configure the MySQL server to accept the connections directly), if your CF server is on Windows then you can use putty to create the tunnel.

[Dave Watts]

I'm just coming in to second Pete's recommendation. Setting up a persistent ssh tunnel is likely to cause problems down the road. It's likely to be fragile. See if you can get your network administrators to allow TLS connections from your server to the MySQL server, if at all possible.

Dave Watts, Eidolon LLC