MySQL JDBC Connection

[stevenGravy]

This entry steps through setting up a MySQL SSL JDBC connection via the desktop tsh connection.

Prerequisites:

• MySQL database
• JDBC library such as mysql-connector-java-8.0.29.jar
• Database connected to MySQL database in Teleport
• Logged in to a desktop tsh session

Note These instructions are in Linux and Mac style. This was confirmed with a RDS MySQL and Teleport 9.2.4 version.

To connect through Teleport or directly via SSL you need to use specific settings in JDBC. These instructions show an example script to generate the required files integrated with a tsh session. A sample Java client that uses these files is provided.

truststore and keystore files are required to connect via SSL as documented here. After logging in via tsh db login dbname you can use this script to generate the trust and key store files.

Note the changes required in the script to match your tsh session.

Trust and Key Store Generation Script Example

#!/bin/bash

# Based on https://dev.mysql.com/doc/connectors/en/connector-j-reference-using-ssl.html

# Change to another password
TRUSTSTORE_PASS=a4790ef6-df8c-11ec-9ff5-6f87601c9cfd
#Change to another password
CLIENTKEYSTORE_PASS=aefbde76-df8c-11ec-acbc-d3cfe635fed7

#Information from tsh status
TELEPORT_CLUSTER=clustername
TELEPORT_PROXY=example.teleport.com
TELEPORT_USER=jeff

# Change to the database name
DB_NAME=dev-mysql-db

TRUSTSTORE_NAME=devtruststore
CLIENT_KEYSTORE=dev-client-keystore.p12
KEYSTORE=devkeystore

DELETE_STORE_FILES=true

if [ "$DELETE_STORE_FILES" = "true" ]; then
   echo Removing truststore, client keystore and keystore
   rm $TRUSTSTORE_NAME $CLIENT_KEYSTORE $KEYSTORE
fi


echo "**********************  Creating Truststore ***********************"
echo
keytool -importcert -alias teleportSQLCert -file ~/.tsh/keys/${TELEPORT_PROXY}/cas/${TELEPORT_CLUSTER}.pem \
  -keystore ${TRUSTSTORE_NAME} -storepass ${TRUSTSTORE_PASS}
echo
echo "Convert the client key and certificate files to a PKCS #12 archive"

openssl pkcs12 -export  \
   -in ~/.tsh/keys/${TELEPORT_PROXY}/${TELEPORT_USER}-db/${TELEPORT_CLUSTER}/${DB_NAME}-x509.pem \
   -inkey  ~/.tsh/keys/${TELEPORT_PROXY}/${TELEPORT_USER} \
   -name "teleportDevClient" -passout pass:${CLIENTKEYSTORE_PASS} -out ${CLIENT_KEYSTORE}
echo
echo "**********************  Importing Client Key and Certificate into Java keystore ****"
keytool -importkeystore -srckeystore $CLIENT_KEYSTORE -srcstoretype pkcs12 \
  -srcstorepass ${CLIENTKEYSTORE_PASS} -destkeystore ${KEYSTORE} -deststoretype JKS -deststorepass ${CLIENTKEYSTORE_PASS}

Java Client Example

import java.sql.*;  
import java.util.*;
class MysqlClient{  
public static void main(String args[]){  
try{  

	Properties properties = new Properties();

properties.put("useSSL", "true");

// Change to your username.  The password is blank
properties.put("user", "devuser");
properties.put("password", "");


properties.put("trustCertificateKeyStoreUrl", "file:///Users/jeff/java/devtruststore");
properties.put("trustCertificateKeyStorePassword", "a4790ef6-df8c-11ec-9ff5-6f87601c9cfd");

//Properties prpoperties = new Properties();
properties.put("clientCertificateKeyStoreUrl", "file:///Users/jeff/java/devkeystore");
properties.put("clientCertificateKeyStorePassword", "aefbde76-df8c-11ec-acbc-d3cfe635fed7");

//Change the jdbc url to your cluster
Class.forName("com.mysql.cj.jdbc.Driver");
Connection con=DriverManager.getConnection(  
"jdbc:mysql://teleport.example.com:3036/classicmodels",properties);  

Statement stmt=con.createStatement();  
//change to a working query for your db
ResultSet rs=stmt.executeQuery("select * from employees");  
while(rs.next())  
System.out.println(rs.getInt(1)+"  "+rs.getString(2)+"  "+rs.getString(3));  
con.close();  
}catch(Exception e){ System.out.println(e);}  
}  
}

Compile

export CLASSPATH=mysql-connector-java-8.0.29.jar:.
javac MysqlClient.java

Running

#copy the above script into setupstore.sh
chmod +x setupstore.sh
./setupstore.sh
#will prompt to confirm to Trust this certificate
java MysqlClient