Skip to main content
tims4831424
Inspiring
July 9, 2019
Question

CFML 2016 connection string mysql over ssh

  • July 9, 2019
  • 1 reply
  • 943 views

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

Kindly regards.

Thorsten

    This topic has been closed for replies.

    1 reply

    pete_freitag
    Participating Frequently
    July 9, 2019

    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
    Inspiring
    July 10, 2019

    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
    Participating Frequently
    July 10, 2019

    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.