Bug #26644 Not possible to connect to MySQL server using Java/jdbc and SSL.
Submitted: 26 Feb 2007 19:17 Modified: 11 Dec 2007 15:49
Reporter: Anders Rydqvist Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S1 (Critical)
Version:5.0.27 OS:Windows (Windows, Linux, Mac)
Assigned to: MC Brown CPU Architecture:Any
Tags: java, jdbc, SSL

[26 Feb 2007 19:17] Anders Rydqvist
Description:
I am trying to establish a SSL enabled connection from a Java application to MySQL (version 5.0.27-standard). I have followed the instructions on http://dev.mysql.com/doc/refman/5.0/en/secure-create-certs.html and http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-using-ssl.html. I have also tried to use the certificates that comes along with the source distrubution with MySQL.

It works fine when I connect from a mysql client:
mysql> \s
--------------
mysql Ver 14.12 Distrib 5.0.22, for Win32 (ia32)

Connection id: 38
Current database:
Current user: current_user
SSL: Cipher in use is DHE-RSA-AES256-SHA
Using delimiter: ;
Server version: 5.0.27-standard
Protocol version: 10
Connection: a_connection via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 3 hours 18 min 12 sec

I can make a connection from my java application without using SSL. For example
show variables like '%ssl%'
have_openssl YES
ssl_ca /root/Desktop/openssl/test/server/cacert.pem
ssl_capath
ssl_cert /root/Desktop/openssl/test/server/server-cert.pem
ssl_cipher ALL:-DES:-RC2:-RC4:-MD5
ssl_key /root/Desktop/openssl/test/server/server-key.pem.

But when I try to make a connection using SSL something goes wrong (same user as I use when connecting from a mysql client=success). I have tried using mysql-connector-3.1.10 and mysql-connector-java-5.0.4-bin.jar and several different java versions.

-Djavax.net.ssl.keyStore=D:/temp/ssl/keystore/truststore
-Djavax.net.ssl.keyStorePassword=some_password
-Djavax.net.ssl.trustStore=D:/temp/ssl/keystore/
-Djavax.net.ssl.trustStorePassword=some_password
-Djavax.net.debug=all

Se code snippet and error message in the attahed file. 

How to repeat:
Follow the instructions on http://dev.mysql.com/doc/refman/5.0/en/secure-create-certs.html and http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-using-ssl.html

I have used the certificates/keys that comes with the source installation of mysql. 

Suggested fix:
Either your instructions are wrong or the are something wrong in the mysql-server.

Possible problem???

keyStore provider is :

No keyStore provider, and the application crash when *** CertificateVerify should be done.
[27 Feb 2007 10:12] Magnus Blåudd
Your client sends the below cert to the server and then you get the disconnect. It looks to me like the cert is not signed with the same CA cert the server is using and thus the server will disconnect.

matching alias: mysqlclientcertificate
*** Certificate chain
chain [0] = [
[
  Version: V1
  Subject: CN=Anders Rydqvist, OU=IST Int AB, O=Utveckling, L=Vaxjo, ST=Sverige, C=Sv
  Signature Algorithm: MD5withRSA, OID = 1.2.840.113549.1.1.4

  Key:  Sun RSA public key, 1024 bits
  modulus: 110958749445639002219320101611151416963913678160790830980414149873413125611977791787123735909195565253505471508060552056825686577921994540224248341540380138976025916783646168462863842526328383509648345133906199129819865846694229588779918117787546202962841149192056149513428845072312280553173119392789951944093
  public exponent: 65537
  Validity: [From: Thu Feb 22 20:58:39 CET 2007,
               To: Wed May 23 21:58:39 CEST 2007]
  Issuer: CN=Anders Rydqvist, OU=IST Int AB, O=Utveckling, L=Vaxjo, ST=Sverige, C=Sv
  SerialNumber: [    45ddf5ef]

]
[27 Feb 2007 10:17] Magnus Blåudd
Try with the certs we provide or add the CA cert you used to sign your own cert with to the servers CA cert file.
[27 Feb 2007 18:56] Anders Rydqvist
--Using the sam cacert.pem file as I'm using at the server.
--The cacert.pem file that comes with the MySQL source download.
keytool -import -alias mysqlServerCACert -file cacert.pem -keystore truststore 

Ange keystore-l?senord:  cacert99
?gare: O=MySQL AB, L=Uppsala, ST=Uppsala, C=SE
Utf?rdare: O=MySQL AB, L=Uppsala, ST=Uppsala, C=SE
Serienummer: 800efd86e2a6dbf5
Giltigt fr?n: Wed May 03 10:48:54 CEST 2006 till: Tue Jan 27 09:48:54 CET 2009
Certifikatsfingeravtryck:
         MD5:  DA:83:1D:6C:0E:EE:BD:2C:57:AF:16:E2:71:D3:D0:63
         SHA1: 10:4A:D1:69:1E:DF:56:75:FB:D7:02:5E:AE:B1:2B:B0:C8:AA:0B:7C
Betror du det h?r certifikatet? [nej]:  ja
Certifikatet har lagts till i keystore-filen

--The next step...

keytool -genkey -keyalg rsa -alias mysqlClientCertificate -keystore keystore

Enter keystore password:  myclient99
What is your first and last name?
  [Unknown]:  Anders
What is the name of your organizational unit?
  [Unknown]:  IST Int AB
What is the name of your organization?
  [Unknown]:  Utveckling
What is the name of your City or Locality?
  [Unknown]:  Vaxjo
What is the name of your State or Province?
  [Unknown]:  K
What is the two-letter country code for this unit?
  [Unknown]:  SV
Is <CN=Anders, OU=Utveckling, O=IST Int AB,
 L=Vaxjo, ST=K, C=SV> correct?
  [no]:  y

Enter key password for <mysqlClientCertificate>
        (RETURN if same as keystore password):
        
--Then I try to run my application with
-Djavax.net.ssl.keyStore=/Users/Anders/Development/Java/Project/SSLMySQL/cert/keystore
-Djavax.net.ssl.keyStorePassword=myclient99
-Djavax.net.ssl.trustStore=/Users/Anders/Development/Java/Project/SSLMySQL/cert/truststore
-Djavax.net.ssl.trustStorePassword=cacert99
-Djavax.net.debug=all

--And I got an the errors, what I'm I missing???
[1 Mar 2007 7:48] Magnus Blåudd
This seems very similar to http://bugs.mysql.com/bug.php?id=19705, please have a look at the steps provided there.

If problem persist please provide information about OS and java version etc.
[1 Mar 2007 8:01] Anders Rydqvist
It works if I only using the "truststore" file. Se above:
keytool -import -alias mysqlServerCACert -file cacert.pem -keystore truststore

-Djavax.net.ssl.trustStore=/Users/Anders/Development/Java/Project/SSLMySQL/cert/truststore
-Djavax.net.ssl.trustStorePassword=cacert99
-Djavax.net.debug=all

But when I follow the example on http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-using-ssl.html and create a client certificate 

keytool -genkey -keyalg rsa -alias mysqlClientCertificate -keystore keystore
-Djavax.net.ssl.keyStore=path_to_keystore_file
-Djavax.net.ssl.keyStorePassword=password
-Djavax.net.ssl.trustStore=path_to_truststore_file
-Djavax.net.ssl.trustStorePassword=password

It crash...

The mysql-server does not recognize this certificate. And this generates an error. Do I need this certificate? If so, how should I do to make it work, what  am I missing???
 
I have tried this on Windows XP, Linux and Mac. It works if I don't use the client certificate. I have tried on Java 1.5.0_04 and 1.5.0_11 .
Using the same mysql-version 5.0.27
[1 Mar 2007 8:05] Magnus Blåudd
Look at my comment from [1 Aug 2006 16:39] and you will see that we didn't generate a new certificate for he client but rather imported the client certificate distributed with MySQL. Please try that first and we can continue to make sure it works with your new cert as well.

We might need to apply the dh-patch-5.0 that Domas supplied for bug#19705, this could be a problem with the server.

"[15 Jul 2006 7:41] Domas Mituzas 
The behavior I'm facing can be reproduced only with Apple-supplied JVM. It seems
to be purely MacOSX'ish JVM issue."
[1 Mar 2007 11:19] Magnus Blåudd
> But when I follow the example on
> <snip>
> It crash...

You don't mean the server crash? Just the client fails to connect. I must admit the error code isn't exactly descriptive.

What I think happens is that when you create the cerificate it's signed with the CA cert from that machine(not the same as MySQL Server ws started with) and that causes the server to reject it.

Try to import the CA cert from the server with something like this before you create your own cert.
> keytool -import -keystore keystore.jks -import -trustcacerts -file ca.crt
> keytool -list -v -keystore keystore.jks
[1 Mar 2007 11:23] Magnus Blåudd
But the manual already describes that part so I assume you have done it.
[1 Mar 2007 11:31] Anders Rydqvist
Yes I have tried that. Like I said earlier, I have followed the instructions. It works when I ONLY use the certificate that comes along with your source dist.

But your manual say:
...
"You will then need to generate a client certificate, so that the MySQL server knows that it is talking to a secure client:

keytool -genkey -keyalg rsa -alias mysqlClientCertificate -keystore keystore"
...

To me it sounds like I should use 2 certificate, one "truststore"- and one "keystore"-file.

-Djavax.net.ssl.keyStore=path_to_keystore_file
-Djavax.net.ssl.keyStorePassword=password
-Djavax.net.ssl.trustStore=path_to_truststore_file
-Djavax.net.ssl.trustStorePassword=password

And the "keystore" file that I generate with the above command, fails=connection fails.
[1 Mar 2007 11:36] Anders Rydqvist
But I should try to do what you described in the other bug-report. But that aren't the same instruction as on your home page (part 2 and 3 diff).

1. Import the CA cert file used when starting the MySQL Server into the the
"truststore". (This cert can be imported without conversion, probably because it
has no metadata at the begining of the file).

$> keytool -import -file mysql-<version>/SSL/cacert.pem  -keystore truststore
-alias 

2. Convert the client cert included with the mysql distribution(found in the SSL
directory) into the binary format(DER) using 'openssl x509' command.
$> openssl x509 -outform DER -in client-cert.pem -out client.cert

3. Then import the converted client cert into "keystore"
keytool -import -file mysql-<version>/SSL/client.cert -keystore keystore -alias
mysqlClientCertificate
[2 Mar 2007 6:49] Anders Rydqvist
When I import the mysql-client.cert with your instruction:

2. Convert the client cert included with the mysql distribution(found in the SSL
directory) into the binary format(DER) using 'openssl x509' command.
$> openssl x509 -outform DER -in client-cert.pem -out client.cert

3. Then import the converted client cert into "keystore"
keytool -import -file mysql-<version>/SSL/client.cert -keystore keystore -alias mysqlClientCertificate

It works!!!

But the instructions on your web-site is only says, "You will then need to generate a client certificate, so that the MySQL server knows that it is talking to a secure client: keytool -genkey -keyalg rsa \
     -alias mysqlClientCertificate -keystore keystore "

And that can not be true, because in this instruction it says nothing about that you must sign the client cert, with the CA-cert.

I'm not all that when it comes to SSL, but it seems to me the instructions is wrong???
[2 Mar 2007 9:35] Magnus Blåudd
Thanks for your patience, I'm glad it worked out. I'm setting this as a documentation problem.
[19 Oct 2007 21:42] bob eff
we got ssl working with mysql without requireSSL and without the client passing a cert on jdbc.

we defined the ssl variables in the my.cnf startup file:

[mysqld]

ssl-ca=/opt/mysql/certs/ca-cert.pem
ssl-cert=/opt/mysql/certs/server-cert.pem
ssl-key=/opt/mysql/certs/server-key.pem

i dont know if you have to do all three or not?

then, make sure the mysql server shows:

+---------------+-------------------------------------------+
| Variable_name | Value                                     |
+---------------+-------------------------------------------+
| have_openssl  | YES                                       |
| have_ssl      | YES                                       |
| ssl_ca        | /opt/mysql/certs/ca-cert.pem     
| ssl_capath    |                                           |
| ssl_cert      | /opt/mysql/certs/server-cert.pem 
| ssl_cipher    |                                           |
| ssl_key       | /opt/mysql/certs/server-key.pem  
+---------------+-------------------------------------------+

then in the jdbc connector URL, add the useSSL=true parameter:

url="jdbc:mysql://128.200.184.8:3306/codes?useSSL=true"

on unix, you can use snoop to see the packets being encrypted or unencrypted.

also, you can grant the user "require ssl" and they are forced to connect with SSL. otherwise, it's their option.

grant select, insert, update, delete, execute on test.* to username@'%' require ssl

for command line access we had to use:

mysql -uuser -ppass --host any.company.com --ssl-key=server-key.pem

but it also worked to use server-cert.pem or ca-cert.pem
[11 Dec 2007 15:49] MC Brown
After lots of tests and trying different combinations, I've now updated the documentation for Connector/J and using SSL. 

I've found there are two ways to use it: 

1. Use existing certificates for both the CA and client certificates (I used the samples from our release). This requires conversion of the client cert to a suitable format.

2. Use the server certificate and sign/create your own. 

I've modified the documentation accordingly with step by step instructions for both, and verified everything is working the JSEE debugging to monitor the SSL connectivity.