title: MariaDB over SSL not working, "certificate verify failed"
body: <p>Using <a href="https://www.cyberciti.biz/faq/how-to-setup-mariadb-ssl-and-secure-connections-from-clients/" rel="nofollow noreferrer">this guide</a> I'm attempting to set up MariaDB (mysql) for using SSL between <strong>dbserver</strong> and <strong>appclient</strong>.  </p>
  
  <p>I created the server and client certificates on the server, per the guide.  I then copied the three necessary client files to <strong>appclient</strong> and set ownership and permissions: </p>
  
  <pre><code>[root@appclient mysql]# ll /etc/pki/tls/certs/
  drwxr-xr-x. 2 mysql mysql   88 Feb  9 13:31 mysql
  
  [root@appclient mysql]# ll /etc/pki/tls/certs/mysql/
  -rw-------. 1 mysql mysql 1372 Feb  9 13:31 ca-cert.pem
  -rw-------. 1 mysql mysql 1230 Feb  9 14:16 client-cert.pem
  -rw-------. 1 mysql mysql 1705 Feb  9 14:16 client-key.pem
  </code></pre>
  
  <p>Here's the full my.cnf on appclient:</p>
  
  <pre><code>[mysqld]
  datadir=/var/lib/mysql
  socket=/var/lib/mysql/mysql.sock
  symbolic-links=0
  
  [client]
  ssl-ca=/etc/pki/tls/certs/mysql/ca-cert.pem
  ssl-cert=/etc/pki/tls/certs/mysql/client-cert.pem
  ssl-key=/etc/pki/tls/certs/mysql/client-key.pem
  
  [mysqld_safe]
  log-error=/var/log/mariadb/mariadb.log
  pid-file=/var/run/mariadb/mariadb.pid
  
  !includedir /etc/my.cnf.d
  </code></pre>
  
  <p>Next, I tested that port 3306 is open on dbserver:</p>
  
  <pre><code>[root@appclient mysql]# telnet dbserver 3306
  Connected to dbserver.
  Escape character is '^]'.
  R
  5.5.52-MariaDB
  </code></pre>
  
  <p>Next I checked MariaDB (mysql) ssl variables on <strong>dbserver</strong>: </p>
  
  <pre><code>MariaDB [(none)]&gt; show variables like '%ssl%';
  +---------------+------------------------------------------+
  | Variable_name | Value                                    |
  +---------------+------------------------------------------+
  | have_openssl  | YES                                      |
  | have_ssl      | YES                                      |
  | ssl_ca        | /etc/pki/tls/certs/mysql/ca-cert.pem     |
  | ssl_capath    |                                          |
  | ssl_cert      | /etc/pki/tls/certs/mysql/server-cert.pem |
  | ssl_cipher    |                                          |
  | ssl_key       | /etc/pki/tls/certs/mysql/server-key.pem  |
  +---------------+------------------------------------------+
  </code></pre>
  
  <p>Next I checked MariaDB (mysql) ssl variables on <strong>appclient</strong>: </p>
  
  <pre><code>MariaDB [(none)]&gt; show variables LIKE '%ssl%';
  +---------------+----------+
  | Variable_name | Value    |
  +---------------+----------+
  | have_openssl  | DISABLED |
  | have_ssl      | DISABLED |
  | ssl_ca        |          |
  | ssl_capath    |          |
  | ssl_cert      |          |
  | ssl_cipher    |          |
  | ssl_key       |          |
  +---------------+----------+
  7 rows in set (0.00 sec)
  </code></pre>
  
  <p>That looks like the start/source of the problem.   </p>
  
  <p>If I try to connect to <strong>dbserver</strong> from <strong>appclient</strong> anyway: </p>
  
  <pre><code>[root@appclient mysql]# mysql -h dbserver -u ssluser -p 
  Enter password: 
  ERROR 2026 (HY000): SSL connection error: error:14090086:SSL routines:SSL3_GET_SERVER_CERTIFICATE:certificate verify failed
  </code></pre>
  
  <p>No bueno.</p>
  
  <p>Checking <strong>appclient</strong>'s certs with openssl... </p>
  
  <pre><code>[root@appclient mysql]# cd /etc/pki/tls/certs/mysql/
  [root@appclient mysql]# openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem
  Error opening certificate file server-cert.pem
  139864320337824:error:02001002:system library:fopen:No such file or directory:bss_file.c:398:fopen('server-cert.pem','r')
  139864320337824:error:20074002:BIO routines:FILE_CTRL:system lib:bss_file.c:400:
  unable to load certificate
  client-cert.pem: OK
  </code></pre>
  
  <p>For kicks, I ran the same openssl test on <strong>dbserver</strong>:</p>
  
  <pre><code>[root@dbserver mysql]# openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem
  server-cert.pem: C = XX, ST = XX, L = CityName, O = MyOrganization, OU = MyGroup, CN = dbserver
  error 18 at 0 depth lookup:self signed certificate
  OK
  client-cert.pem: OK
  </code></pre>
  
  <p>The tutorial only mentions copying <code>ca-cert.pem</code>, <code>client-cert.pem</code> and <code>client-key.pem</code> to the client, yet the failure above points to a missing <code>server-cert.pem</code> on the client.   </p>
  
  <p>Do I need to create the server-*.pem files on the client also?  If so, where do these go in the <code>/etc/my.cnf</code> file?</p>