Using Your Own Certificates

In this hands-on tutorial we will go over everything that is needed to get an IMPORT command running in a scenario where the TLS certificates involved are issued by yourself (or the organization you are part of).

Goals

The goals of this tutorial are to

understand the different TLS certificate types and be able to create them
practice installing certificates
run IMPORT on Exasol to copy data from MySQL over a TLS connection

This will take you through tasks usually performed by network admins and database admins.

Before You Start

If you haven't already we highly recommend you familiarize yourself with the general TLS topic by reading the TLS Introduction.

What you Will Need

To follow this tutorial, you need

at least 10 GiB free space on your filesystem
A computer running Ubuntu or an Ubuntu Virtual Machine. Inside that Ubuntu you will need to install:
- OpenSSL
- Docker
- Exasol's docker-db
- MySQL server installed via package

Windows Sub-system for Linux (WSL) not Supported

This tutorial does not work on the Windows Sub-system for Linux (WSL).

We recommend using an Ubuntu VM when working with Windows. E.g. powered by Virtualbox.

Installing the Software

Update the machine you are using
```
sudo apt update
sudo apt upgrade
```
Install Docker
```
sudo apt install docker.io
```
Install OpenSSL. Note that on Ubuntu OpenSSL should be preinstalled. If not run:
```
sudo apt install openssl
```

Create a directory where we will keep the files that we are creating in the course of this project.

tutorial_dir="$HOME/tutorials/tls_with_exasol/import"
mkdir -p "$tutorial_dir"
cd "$tutorial_dir"

Creating and Installing the Certificates

In this part of the tutorial we will create the required TLS certificates and install them on Exasol and MySQL.

Understanding the Relationship of Host Authentication and Server Certificate

When you create a TLS certificate that is intended to work in the real world, you need to make sure that the server's hostname is correctly specified in the certificate. Remember that the server certificate's whole purpose is to prove that the server is who it claims to be.

Once you access the server, the client will work down a list of matching criteria to determine whether the certificate matches the server (see RFC6125, 6.3). The whole process is non-trivial, so we encourage you at this point to take a look into the specification. It is well-written and contains relevant examples.

In a nutshell, the client holds client-local information about the server against data presented in the server certificate. If they match, the server and certificate are considered authentic.

While the standard states that the so called 'Common Name' (CN) from the certificate's subject is only the last way to match the hostname, it is still the most widely used variant. So for the sake of simplicity we are going to use this mechanism in this tutorial too.

For you that means you need to know the hostname before you create the certificate.

If you have a host that is intended to be reachable under multiple hostnames, please read about the concept of the "Subject Alternative Name" (SAN, see RFC5280, 4.2.1.6). SAN is outside the scope of this tutorial.

Creating Your own Certification Agency (CA)

To get that right out of the way, yes, in a normal organization, you already have a Certification Agency (CA). But for the purpose of this tutorial, let's pretend you don't.

That means we will create our own, and that in turn gives us the opportunity to create the server certificates we are going to use quickly.

Do this to create a CA certificate:

Create a key pair for the CA
```
openssl genrsa -aes256 -out ca.key 4096
```
You will be prompted for a passphrase. Use tutorial for this demonstration. In a real world scenario you would obviously choose a secure, non-guessable passphrase.

Create a CA certificate

 CERT_HOSTNAME='host.example.com'
 openssl req -x509 -new -nodes -key ca.key -sha256 -days 365 -out ca.crt -subj "/CN=$CERT_HOSTNAME/C=DE/L=Bavaria/O=Tutorial Organization"

See "Understanding the Relationship of Host Authentication and Server Certificate" for details on why the hostname plays a role here.

Since the last step requires reading the CA key, you will need to enter the passphrase again here

Let's talk about what the individual parameters of the openssl mean:

req : This subcommand takes care of everything that has to do with a certificate signing request. We will talk about Signing Requests later in detail.

-x509 : This switch tells OpenSSL to immediately execute the signing request, so that the result will be a self-signed certificate. It's a shortcut.

-new : Create a fresh certificate instead of signing an existing one.

-key <path> : Path to the file that holds the key to sign the certificate.

-sha256 : Use the SHA256 hash algorithm to generate a hash from the certificate contents.

-days <number> : Number of days until the certificate will expire.

-out <path> : Path to the file that will contain the newly created certificate.

-subj <key=value[/key=value]*> : Subject name of the certificate. Usually in form of an LDAP-style distinguished name. You can find an attribute list in RFC4519.

Reading the Certificate

Let's face it. Sooner or later you will have to read through the contents of TLS certificates in order to debug why you don't get a TLS connection working. So why not get practicing that out of the way right now while you are still relaxed — instead of when you are fixing network connections in a production system.

The command to view the contents of a certificate file is comparably simple:

openssl x509 -in ca.crt -text -noout

Here's what the parameters mean:

x509 : subcommand for all actions related to managing X.509 certificates.

This will print out a longish text representation of the certificate like the one below. The example output below is shortened ([...]) where you normally have long rows of hex dumps.

Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number:
            0d:fc:[...]
        Signature Algorithm: sha256WithRSAEncryption
        Issuer: CN = TLS Tutorial CA, C = DE, L = Bavaria, O = Tutorial Organization
        Validity
            Not Before: Dec  9 14:37:35 2022 GMT
            Not After : Dec  9 14:37:35 2023 GMT
        Subject: CN = TLS Tutorial CA, C = DE, L = Bavaria, O = Tutorial Organization
        Subject Public Key Info:
            Public Key Algorithm: rsaEncryption
                RSA Public-Key: (4096 bit)
                Modulus:
                    00:ba:[...]
                Exponent: 65537 (0x10001)
        X509v3 extensions:
            X509v3 Subject Key Identifier: 
                14:44:73:3F:17:A5:F8:74:93:05:07:0D:A9:FA:7F:BD:26:E1:54:E0
            X509v3 Authority Key Identifier: 
                keyid:14:44:73:3F:17:A5:F8:74:93:05:07:0D:A9:FA:7F:BD:26:E1:54:E0

            X509v3 Basic Constraints: critical
                CA:TRUE
    Signature Algorithm: sha256WithRSAEncryption
         6e:8d:[...]

The output tells us that we are locking at an X.509 certificate in version 3. The signature consists of an SHA256 hash that is signed with an RSA encryption key. That's the key we created as the very first thing.

If you are wondering why issuer and subject are the same, then remember, we are talking about a root CA certificate here, so that is a valid combination.

The validity is a year as we defined. It is represented here by a start and end date. The start date is the date and time we created the certificate.

The public key part is the most interesting part of the certificate, since that is what clients will use to validate certificates that claim to be signed by this CA. We are talking about asymmetric cryptography here and only the actual CA holds the private signing key. That means if the client is able to validate a given signature with the CA's known and trusted public key, that signature is authentic.

Subject key identifier and authority key identifier are unique identifiers that are used in certificate chaining. Note that in our root CA example they are identical.

The critical flag after an extension definition means that any system using the certificate must know and process it. The system can ignore extensions that are not marked like this. Case in point: the key identifiers are non-critical because there is a fallback in the form of issuer and subject, although the IDs are obviously the better alternative for machine evaluation.

The CA:TRUE entry is pretty self-explaining. You are looking at a CA certificate.

Finally, there is a block that contains the signature of the certificate, which in this case is not particularly useful, since it is self-signed. Since that is the beginning of the chain, and it contains the public key that is required to validate the signature, you have the dreaded chicken-of-the-egg problem of all IT security here. That means the root CA certificates must be installed on a client by means of a trusted channel.

Creating a Signing Request for a Server Certificate

Services that are offered via TLS present a server certificate to the client. The client reads the certificate contents and uses CA certificates that are installed locally to verify the signature in the server certificate.

Note this in this step you will create a certificate signing request (CSR) (see RFC2986) instead of an actual certificate. Think of the signing request a precursor of a signed certificate.

Create a key pair without passphrase for the server:
```
openssl genrsa -out server.key 4096
```
You will be prompted for a password. Use tutorial for this demonstration. In a real world scenario you would obviously choose a secure, non-guessable password.

Create a signing request

openssl req -new -nodes -key server.key -sha256 -out server.csr -subj '/CN=TLS Tutorial MySQL Server/C=DE/L=Bavaria/O=Tutorial Organization'

Note how the -x509 switch is gone from the command. This time we really want a signing request, not a self-signed certificate.

If you look real close, you might wonder why there is no -days switch here. The reason is simple: the CA decides how long a certificate it creates is valid. Not the requester.

Reading a Signing Request

Let's take a look at the generated signing request to get a better understanding of its contents.

openssl req -text -noout -in server.csr

Produces an output that looks like this (again we shortened the hex dumps):

Certificate Request:
    Data:
        Version: 1 (0x0)
        Subject: CN = TLS Tutorial CA, C = DE, L = Bavaria, O = Tutorial Organization
        Subject Public Key Info:
            Public Key Algorithm: rsaEncryption
                RSA Public-Key: (4096 bit)
                Modulus:
                    00:99:af[...]
                Exponent: 65537 (0x10001)
        Attributes:
            a0:00
    Signature Algorithm: sha256WithRSAEncryption
         8c:33:[...]

As you can see, there is not much to it. It looks like a half-finished certificate. Just a text representation of the data you provided on the commandline.

The modulus is a property that the private and public key of a server share. Its purpose is allowing to verify key authenticity.

If you are curious, you can check the modulus of the server key like this:

openssl rsa -in server.key -text

When you do this on your machine you will find that the output matches the modulus in the CSR.

RSA Private-Key: (4096 bit, 2 primes)
modulus:
    00:99:af:[...]
[...]

The most important puzzle piece in the signing request is that it contains the public key of the requester. In our case the server public key. Without that users of the certificate would not be able to verify the authenticity of the TLS connection.

You can export the public key from the signing request like this:

openssl req -in server.csr -pubkey -noout

If you compare it to the public key contained in the server key pair, you will find it is identical.

openssl rsa -in server.key -pubout

Signing the Server Certificate

That is something that the organization owning the server would normally send to the certification agency. The request contains everything the CA needs to create a signed certificate.

Since in our tutorial you own both the server and the CA, you can play both parts.

We earlier briefly already touched the topic of certificate extensions. To define which extensions you want on the server certificate signed by the CA, this creates a small configuration file.

echo '[extensions]
keyUsage = critical, nonRepudiation, digitalSignature, keyEncipherment, keyAgreement
basicConstraints = CA:false' > server_cert_extensions.cfg

Let's look at the component of the configuration options.

[extensions] : Is a section header. This header can be used for finding a set of options in the configuration file.

keyUsage : Defines what a client should accept the certificate for. If something is not listed here, the client must (enforced by the critical modifier) refuse any attempts to use the key for it. Case in point, this server key may not sign certificates.

Non-repudiation : in this context means a cryptographic method to make sure that neither sender nor recipient of information can deny to have processed that information. : The key can also be used for creating a digital signature and encipher other keys as well as key agreement. If you are asking yourself why payload data encryption is not mentioned, remember that TLS uses asymmetric cryptography only until the symmetric session key is exchanged. Actual data payload is encrypted symmetrically.

basicConstraints : We explicitly add a constraint here that this configuration is not for CA certificates. You could drop that option if you want, because this is the default for certificates.

The following command creates a certificate from the signing request and signs it with the private key of the CA.

openssl x509 -req -in server.csr -CA ca.crt -CAkey ca.key -CAcreateserial -out server.crt -days 90 -sha256 -extfile server_cert_extensions.cfg -extensions extensions

You will be prompted for the passphrase of the CA key.

As always let's take a peek at the certificate that we just created.

openssl x509 -in server.crt -text -noout

Your server certificate should now look similar to the output below:

Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number:
            2c:18:91:ff:3a:b3:31:38:a4:16:fc:05:f3:ed:18:74:fd:c5:57:b8
        Signature Algorithm: sha256WithRSAEncryption
        Issuer: CN = TLS Tutorial CA, C = DE, L = Bavaria, O = Tutorial Organization
        Validity
            Not Before: Jan 12 09:07:04 2023 GMT
            Not After : Apr 12 09:07:04 2023 GMT
        Subject: CN = TLS Tutorial MySQL Server, C = DE, L = Bavaria, O = Tutorial Organization
        Subject Public Key Info:
            Public Key Algorithm: rsaEncryption
                RSA Public-Key: (4096 bit)
                Modulus:
                    00:99:af:[...]
                Exponent: 65537 (0x10001)
        X509v3 extensions:
            X509v3 Key Usage: critical
                Digital Signature, Non Repudiation, Key Encipherment, Key Agreement
            X509v3 Basic Constraints: 
                CA:FALSE
    Signature Algorithm: sha256WithRSAEncryption
         75:41:37:[...]

Now, we are all set to install the certificates.

Certificate Chains

For the sake of completeness it should be mentioned here that out in the wild server certificates are usually signed by subordinate CAs. So the client has to follow the certificate chain all the way down to a CA it trusts. Servers can even present server certificates that are combined with some intermediate CA certificates to fill the gaps. Even then the fact still remains, that client must know and trust the last CA in the chain.

We keep things simple here though, since this is just a tutorial meant to convey the general idea of server certificate validation.

Setting up MySQL

Install MySQL client and server

sudo apt install mysql-client mysql-server

Log into MySQL as MySQL root user (on Ubuntu now requires sudo)
```
sudo mysql -u root
```

Create a database shapes and a user tutorial_user for our import experiment

create database shapes;
create user 'tutorial_user' identified by 'tutorial';
grant all on shapes.* to tutorial_user;
exit

Login as a tutorial_user
```
mysql -u tutorial_user -p
```

Populate the database

create table shapes.shapes (name VARCHAR(40), corners int);
insert into shapes.shapes values ('point', 1), ('line', 2), ('triangle', 3), ('rectangle', 4);
exit

As you can tell we won't win any prize for most original database usage here, but that is not the point of the tutorial.

Installing Certificates in MySQL

By default, the MySQL server on Ubuntu comes with a self-signed certificate. Since self-signed certificates don't originate from a CA the client knows, clients will be prompted to trust them.

This is a bad practice, and since we already went through the trouble of creating our own certificates, our next job will be to use them with MySQL.

Install the CA certificate centrally:

sudo cp ca.crt /usr/local/share/ca-certificates/exasol_tutorial_ca.crt
sudo update-ca-certificates

Verify that it was installed:

ls -l /etc/ssl/certs/exasol*

Must output something like:

lrwxrwxrwx 1 root root 55 Jan 12 11:43 /etc/ssl/certs/exasol_tutorial_ca.pem -> /usr/local/share/ca-certificates/exasol_tutorial_ca.crt

Note how the symbolic link changes the file suffice from .crt to .pem

Install the server certificate and key

sudo mkdir -p /etc/mysql/certs
sudo cp server.crt /etc/mysql/certs/
sudo cp server.key /etc/mysql/certs/
sudo chown mysql:mysql /etc/mysql/certs/server.key

Note that we need sudo privileges here, since we need to install the certificates in a central locations that belong to the root user.

Additionally, we give the private key to the MySQL user on OS level. This user is aptly called mysql, belongs to a group of the same name and is created automatically when you install MySQL on Ubuntu. We don't want anyone else to be able to read it, so weakening permissions on that file is not an option.

You can check ownership and permissions like this:

ls -l /etc/mysql/certs/

This should list two files:

-rw-r--r-- 1 root  root  1919 Jan 12 11:56 server.crt
-rw------- 1 mysql mysql 3326 Jan 12 11:56 server.key

Now edit the [mysqld] section of the MySQL configuration file /etc/mysql/mysql.conf.d/mysqld.cnf as root user and add:

[mysqld]
ssl
ssl-ca=/etc/ssl/certs/exasol_tutorial_ca.pem
ssl-cert=/etc/mysql/certs/server.crt
ssl-key=/etc/mysql/certs/server.key

Also, we need to enable Exasol later to access the MySQL server. Since that access will not be from localhost, you need to accept all interfaces.

Change the line for the bind address so that it looks as follows and is not commented out:

bind-address = 0.0.0.0

Restart the MySQL server daemon so that the changed configuration takes effect.

sudo service mysql restart

Examining the TLS Connection to the MySQL Server

At this point you should be able to use OpenSSL's s_client to look at the TLS connection:

openssl s_client -starttls mysql -connect localhost:3306

This results in a log text that tells you all the details about the TLS connection.

Let's look at some of the more interesting parts. We'll start with the certificate chain.

Certificate chain
 0 s:CN = TLS Tutorial MySQL Server, C = DE, L = Bavaria, O = Tutorial Organization
   i:CN = TLS Tutorial CA, C = DE, L = Bavaria, O = Tutorial Organization
 1 s:CN = TLS Tutorial CA, C = DE, L = Bavaria, O = Tutorial Organization
   i:CN = TLS Tutorial CA, C = DE, L = Bavaria, O = Tutorial Organization

As you can see the chain has two links. The server certificate (denoted with index zero) and the issuing CA (index one).

The abbreviation s: stands for "subject name", i: for "issuer". You can also see that the CA certificate is self-signed.

We also learn a few details about the cipher and server public key. This helps us judge, if the cryptography used is still secure enough:

New, TLSv1.3, Cipher is TLS_AES_256_GCM_SHA384
Server public key is 4096 bit

At the time of this writing TLS 1.3 (January 2023), AES 256 and RSA 4096 are all considered secure.

As mentioned before, asymmetric cryptography is only used to establish the initial connection. Once that is done the TLS communication partners negotiate a symmetric session. You can see this here:

Post-Handshake New Session Ticket arrived:
SSL-Session:
    Protocol  : TLSv1.3
    Cipher    : TLS_AES_256_GCM_SHA384
    Session-ID: DAB72937942090F3588FD90368047E7B90361EAE4427DD326530F7CE6041174E
    Session-ID-ctx: 
    Resumption PSK: F2055F6F181E774D3B05EBF321662C702F32DCB48D057B9C928FF35FC217C5A167773EF4715339E34B5061C113511A00
    PSK identity: None
    PSK identity hint: None
    SRP username: None
    TLS session ticket lifetime hint: 300 (seconds)
    TLS session ticket:
    0000 - 4b 25 3d [...]

Session Tickets are a way to allow resuming sessions without keeping server-side state per client. If you imagine how many clients a TLS-capable server might have to handle under high load, this is quite useful.

The most important part here are the cipher and the ticket lifetime of 5 minutes.

Side-note: if you ever experience TLS connections breaking of after a certain time, you should check first this session ticket lifetime and then any intermediate network components like firewalls. Sometimes they interfere with the connection renewal process.

Finally, you can connect the MySQL client to the server using TLS:

mysql -u tutorial_user -p --ssl-mode=REQUIRED -u tutorial_user

The source side of our import via TLS is now set up and ready to use.

Setting up Docker

For the next steps we need a Docker setup. We already installed the Docker package via APT before, so we can dive right into the configuration.

Add your current user to the docker group
```
sudo usermod -aG docker "$USER"
```
Log out and back in to apply the group change
Verify that your current user is in the docker group
```
groups
```
Tell the system control to auto-start the docker daemon:
```
sudo systemctl enable docker.service
```
Start the docker daemon without restaring the machine:
```
sudo systemctl start docker.service
```

Installing Exasol via Docker

Exasol's docker-db provides a Docker container that is quite convenient for experiments like our tutorial.

The following docker command downloads the container's layers and afterward starts the container. Expect a couple of minutes until the download and database start are done.

docker run --name exasoldb -p 8563:8563 -p 2580:2580 --detach --privileged --stop-timeout 120  exasol/docker-db:7.1.17

There are two ports forwarded to the host:

8563: database port
2580: BucketFS port

Docker 101 - Some Useful Tips for Newcomers to Docker

You can check the status of your docker containers with docker ps. The -a switch also shows containers that are not running at the moment.

docker ps -a

A typical result looks like this (shortened):

CONTAINER ID   IMAGE                     COMMAND                  CREATED       STATUS       PORTS                   [...]
d0bbc7ef331f   exasol/docker-db:7.1.17   "/usr/opt/EXASuite-7…"   4 hours ago   Up 4 hours   0.0.0.0:49156->443/tcp, [...]

The ID is important since that is a unique number that you can use to control a container. The exposed port numbers are also listed here, which comes in handy in case you forgot to expose one.

Here are a couple of useful docker commands

Command	Meaning
`docker run ...`	Start a new container from an image
`docker start <container-id>`	Start the container with the given ID
`docker stop <container-id>`	Stop the container
`docker ps [-a]`	List running / all containers
`docker image ls`	Show all cached docker images

For more information check the Docker Commandline Reference

Installing the MySQL JDBC driver in Exasol

Import into Exasol requires that the database driver of the source database (MySQL in our case) is installed in Exasol.

Installation in the Docker variant requires uploading the driver to a BucketFS bucket and updating a configuration file.

Download the MySQL Connector (JDBC driver)

wget https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-j-8.2.0.tar.gz

Unpack the connector
```
tar xzvf mysql-connector-j-8.2.0.tar.gz
```
Get the ID of the Exasol Docker container
```
container_id=$(docker ps -a | grep exasol | sed -e's/ .*//') && echo "$container_id"
```
This sets the variable container_id and outputs the result.

Get the write-password of the default Bucket in BucketFS

write_pwd=$(docker exec -it "$container_id" cat /exa/etc/EXAConf | grep WritePasswd | sed -e's/^.* = //' -e's/[\n\r]//g' | base64 --decode)

Copy the driver to the default Bucket

curl -vX PUT -T mysql-connector-j-8.2.0/mysql-connector-j-8.2.0.jar "http://w:$write_pwd@localhost:2580/default/drivers/jdbc/mysql-connector-j-8.2.0.jar"

Create a driver configuration (settings.cfg)
```
echo 'DRIVERNAME=MYSQL_JDBC
JAR=mysql-connector-j-8.2.0.jar
DRIVERMAIN=com.mysql.cj.jdbc.Driver
PREFIX=jdbc:mysql:
NOSECURITY=YES
FETCHSIZE=100000
INSERTSIZE=-1' > settings.cfg
```
Please make sure to copy this from the rendered version (not the Markdown source) of this tutorial. You want no leading spaces in the configuration file.

Upload the configuration to drivers/jdbc in the default bucket:

curl -vX PUT -T settings.cfg "http://w:$write_pwd@localhost:2580/default/drivers/jdbc/settings.cfg"

Verify this installation:

curl http://localhost:2580/default

Must produce a listing that looks similar to this:

EXAClusterOS/ScriptLanguages-standard-EXASOL-7.1.0-slc-v4.0.0-CM4RWW6R.tar.gz
drivers/jdbc/mysql-connector-j-8.2.0.jar
drivers/jdbc/settings.cfg

Copying the Certificate to Exasol

Information: this part will be added in a separate pull request.

Run the Import

In this last part of the tutorial, we import the data from the shapes database and shapes table in MySQL into Exasol.

One last piece of preparation is that you get the IP address of the Docker host (i.e. the Ubuntu machine you run the tutorial on).

ip address show docker0 | grep inet

Please, note down the IP address. You will need it for the IMPORT command.

We need a way to talk to Exasol, so please install the usql commandline SQL client on the Ubuntu machine. Current versions come with the Exasol Go driver preinstalled.

wget https://github.com/xo/usql/releases/download/v0.13.5/usql_static-0.13.5-linux-amd64.tar.bz2
tar --bzip2 -xvf usql_static-0.13.5-linux-amd64.tar.bz2

Next, connect to the Exasol server

./usql_static exa://sys:exasol@localhost?validateservercertificate=0

Since we forwarded the default port, we can directly connect to localhost, even if the Exasol database is running in a Docker container.

You are probably asking yourself why we skip certificate validation here. The reason is that we otherwise would have to outfit Exasol with a server certificate too and that is beyond the scope of this particular tutorial for now (later versions might add this).

In order to run the import, we need a target table on Exasol, so let's prepare it.

CREATE SCHEMA target_schema;
CREATE TABLE target_schema.shapes_target(name VARCHAR(40), corners INT);

You are now set up and ready to run the import.

IMPORT INTO target_schema.shapes_target
FROM JDBC AT 'jdbc:mysql://<ip-address-of-docker-host>/shapes?sslMode=REQUIRED'
USER 'tutorial_user' IDENTIFIED BY 'tutorial'
STATEMENT 'SELECT * FROM shapes.shapes';

Let's look at the individual parts of this SQL statement.

IMPORT INTO <target-schema>.<target-table> : Tells Exasol to run an import into an existing schema and table.

FROM <source> AT <url> : Defines from which datasource the data should be copied. Here a JDBC URL with server IP address and a configuration parameter.

: As you probably guessed, the option sslMode=REQUIRED enforces a TLS connection from the MySQL JDBC driver to the MySQL server.

USER <username> IDENTIFIED BY <password> : Contains the credentials that are used to connect to the source. In our case the database user and password on MySQL.

STATEMENT '<sql-statement-to-run-on-source>'

: Defines which statement should be executed on the data source. The result set of this statement is what gets imported.

Now, check the results in the target table:

SELECT * FROM target_schema.shapes_target;

This must yield the following result

   name    | corners 
-----------+---------
 point     |       1
 line      |       2
 triangle  |       3
 rectangle |       4
(4 rows)

Congratulations, you just imported data from MySQL via a TLS connection.

Conclusion

In this tutorial you learned how to create, read and debug TLS certificates for a Certification Agency (CA) and for a server. You installed the server certificate machines to allow certificate chain validation. You installed the server certificate on the source server and used s_client to look at the TLS connection. Finally, you ran an import that transferred data from a source database via TLS to Exasol.

References

Docker CLI

Docker Commandline Reference, Docker.com

Non-Repudiation

non-repudiation, NIST Glossary, NIST

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

use_your_own_certificate.md

use_your_own_certificate.md

Using Your Own Certificates

Goals

Before You Start

What you Will Need

Windows Sub-system for Linux (WSL) not Supported

Installing the Software

Creating and Installing the Certificates

Understanding the Relationship of Host Authentication and Server Certificate

Creating Your own Certification Agency (CA)

Reading the Certificate

Creating a Signing Request for a Server Certificate

Reading a Signing Request

Signing the Server Certificate

Certificate Chains

Setting up MySQL

Installing Certificates in MySQL

Examining the TLS Connection to the MySQL Server

Setting up Docker

Installing Exasol via Docker

Docker 101 - Some Useful Tips for Newcomers to Docker

Installing the MySQL JDBC driver in Exasol

Copying the Certificate to Exasol

Run the Import

Conclusion

References

Docker CLI

Non-Repudiation

RFC2986

RFC7468

RFC5077

RFC5280

RFC6121

x509v3_config

Files

use_your_own_certificate.md

Latest commit

History

use_your_own_certificate.md

File metadata and controls

Using Your Own Certificates

Goals

Before You Start

What you Will Need

Windows Sub-system for Linux (WSL) not Supported

Installing the Software

Creating and Installing the Certificates

Understanding the Relationship of Host Authentication and Server Certificate

Creating Your own Certification Agency (CA)

Reading the Certificate

Creating a Signing Request for a Server Certificate

Reading a Signing Request

Signing the Server Certificate

Certificate Chains

Setting up MySQL

Installing Certificates in MySQL

Examining the TLS Connection to the MySQL Server

Setting up Docker

Installing Exasol via Docker

Docker 101 - Some Useful Tips for Newcomers to Docker

Installing the MySQL JDBC driver in Exasol

Copying the Certificate to Exasol

Run the Import

Conclusion

References

Docker CLI

Non-Repudiation

RFC2986

RFC7468

RFC5077

RFC5280

RFC6121

x509v3_config