Bug #19705 connector/j can't connect to 5.0.22 server using certificate
Submitted: 11 May 2006 5:04 Modified: 18 Aug 2006 12:01
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:5.0.22-bk OS:Windows (Windows/Linux)
Assigned to: Magnus Blåudd CPU Architecture:Any

[11 May 2006 5:04] Shane Bester
Description:
On a linux box, run mysqld with my.cnf:

[mysqld]
ssl
ssl-ca=/home/sbester/cert/cacert.pem
ssl-cert=/home/sbester/cert/server-cert.pem
ssl-key=/home/sbester/cert/server-key.pem

Then, create a user "GRANT ALL ON *.* TO 's1'@'%' IDENTIFIED BY '12345' require x509;"

Now, connect to the server using the attached java file (or command line client).  Both work.

Using windows server with same configuration results in java exceptions thrown from Connector.J 3.1.12 (attached).  Command line client works on Windows though.

How to repeat:
import java.sql.*;
import java.net.SocketException;

public class ssltest
{
  private static com.mysql.jdbc.Driver drv = null;
    public static void main(String args[]) throws SQLException
    {
    String sqlStr = "";
    Connection c = null;
    boolean isSslOn = true;

     if ( drv == null )
      {
      drv = new com.mysql.jdbc.Driver();
      }

    String connection =  "jdbc:mysql://127.0.0.1/test?user=s1&password=12345";

    if ( isSslOn )
      {
      /*
       * with appropriate values for keystore, truststore, and password
       */
      System.setProperty("javax.net.ssl.keyStore","g:/keystore");
      System.setProperty("javax.net.ssl.keyStorePassword","password");
      System.setProperty("javax.net.ssl.trustStore","g:/truststore");
      System.setProperty("javax.net.ssl.trustStorePassword","password");
      connection += "&useSSL=true";
      }

     // this works on linux, but not on Windows
      c = drv.connect( connection, null );

    }
}

Suggested fix:
not sure, may be related to http://bugs.mysql.com/bug.php?id=17737
[11 May 2006 5:12] MySQL Verification Team
-Djavax.net.debug=all output

Attachment: jvm_debug_windows.txt (plain/text, text), 34.08 KiB.

[11 May 2006 6:29] MySQL Verification Team
5.0.20 worked fine on Linux.  Now, using latest source, jdbc driver doesn't appear to be connecting using certificates to Linux or Windows server. MySQL commandline client worked fine.

<snip>
main, WRITE: TLSv1 Change Cipher Spec, length = 1
main, handling exception: java.net.SocketException: Software caused connection abort: socket write error
main, SEND TLSv1 ALERT:  fatal, description = unexpected_message
main, WRITE: TLSv1 Alert, length = 2
main, Exception sending alert: java.net.SocketException: Software caused connection abort: socket write error
main, called closeSocket()
</snip>
[28 Jun 2006 13:03] MySQL Verification Team
I tried connecting to mysql-max-5.0.22-linux-i686.tar.gz from the mirrors, on linux and it worked just fine. Will try Windows version shortly
[10 Jul 2006 12:11] Domas Mituzas
Connection to 5.0.24-bk yassl from Mac 1.5.0_06 JRE

Attachment: mac_1_5_0_06_debug_trace.txt (text/plain), 25.64 KiB.

[10 Jul 2006 15:27] Domas Mituzas
This is one of possible problems:
Bundled YaSSL library fails to SSL_accept() with TLSv1_server_method(). 

yaSSL::DiffieHellman::set_sizes (this=0x0, pSz=@0xbffff980, gSz=@0xbffff988, pubSz=@0xbffff984) at crypto_wrapper.cpp:885
885         Integer p = pimpl_->dh_.GetP();
(gdb) bt
#0  yaSSL::DiffieHellman::set_sizes (this=0x0, pSz=@0xbffff980, gSz=@0xbffff988, pubSz=@0xbffff984) at crypto_wrapper.cpp:885
#1  0x0000b00f in yaSSL::DH_Server::build (this=0x500630, ssl=@0x1800400) at yassl_imp.cpp:133
#2  0x00009c78 in yaSSL::ServerKeyExchange::build (this=0xbffff9ec, ssl=@0x1800400) at yassl_imp.cpp:1482
#3  0x00031dfe in yaSSL::sendServerKeyExchange (ssl=@0x1800400, buffer=buffered) at handshake.cpp:809
#4  0x00002e98 in yaSSL_accept (ssl=0x1800400) at ssl.cpp:231
#5  0x0000285b in main () at server.c:34

This quite relates to my trace shown above - where DH fails. 

Source code of my pseudo server, linked to 5.0.24-bk/libmysqlclient.a:

#define YASSL_PREFIX
#include "/Users/midom/Development/mysql-5.0/extra/yassl/include/openssl/ssl.h"
#include <sys/types.h>
#include <sys/socket.h>
#include <netinet/in.h>
#include <string.h>

main() {
	int s,client; 
	int val=1;
	struct sockaddr_in sin;
	SSL_CTX * ctx;
	SSL * ssl;
	
	s=socket(AF_INET,SOCK_STREAM,0);
	memset(&sin,0,sizeof(sin));
	sin.sin_addr.s_addr=INADDR_ANY;
	sin.sin_family=AF_INET;
	sin.sin_port=htons(5151);
	setsockopt(s,SOL_SOCKET,SO_REUSEADDR,
		&val,sizeof(val));
	bind(s,(struct sockaddr *)&sin, sizeof(sin));
	listen(s,5);
	client=accept(s, (struct sockaddr *)&sin, (socklen_t *)&val);
	
	SSL_library_init();
	ctx = SSL_CTX_new(TLSv1_server_method());
	SSL_CTX_use_certificate_file(ctx,"cert",SSL_FILETYPE_PEM);
	SSL_CTX_use_PrivateKey_file(ctx,"cert",SSL_FILETYPE_PEM);
	ssl = SSL_new(ctx);
	SSL_set_fd(ssl,client);
	SSL_set_accept_state(ssl);
	SSL_accept(ssl);
	sleep(1);
}
[11 Jul 2006 11:26] Domas Mituzas
Few facts:
1. TLSv1 crashes only when DH parameters have not been set externally with SSL_CTX_set_tmp_dh()
2. OpenSSL and YaSSL clients connect properly.
3. Java when connects to OpenSSL-enabled server does not do DH.
4. Java when connects to YaSSL-enabled server complains about bad exponent value (after getting dh ephemeral)

Probably different DH parameter set might work?
[11 Jul 2006 11:40] Domas Mituzas
DH1024 parameter set fixes it, working on patch.
[11 Jul 2006 12:37] Domas Mituzas
Patch for mysql-5.0.24-bk viosslfactories

Attachment: dh-patch-5.0 (application/octet-stream, text), 2.17 KiB.

[15 Jul 2006 5: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.
[31 Jul 2006 12:29] Magnus Blåudd
I'm trying to figure out the status of this bug as we have a support issue waiting for it.

I hope the original problem is fixed but would like some help to verify that is the case.

The second problem regarding MacOS X should be made a separate bug report so this one can be closed if it's fixed.
[1 Aug 2006 9:42] Magnus Blåudd
The connection fails in CertDecoder::Decode with error SIG_OTHER_E, this is caused by the function ValidateSignature failing. If I comment out the validation part the connect will continue sucessfully.

This is from the debug log:

T@5    : | info: ssl_: 02E89E00  timeout: 28800
T@5    : | info: cipher_name= 'DHE-RSA-AES128-SHA'
T@5    : | info: Client certificate:
T@5    : | info:         subject: /C=SE/ST=Sweden/L=Uppsala/O=MySQL AB/OU=Softwa
re Dev/CN=Magnus Svensson
T@5    : | info:         issuer: /C=SE/ST=Sweden/L=Uppsala/O=MySQL AB/OU=Softwar
e Dev/CN=Magnus Svensson
T@5    : | info: shared_ciphers: 'Not Implemented, SSLv2 only'
T@5    : <sslaccept
T@5    : info: Reading user information over SSL layer

Will continue to investigate why the ValidateSignature function fails.
[1 Aug 2006 10:09] Magnus Blåudd
The ValidateSignature function is used to validate the cert received from the client against a list of trusted CA certs in the MySQL Server. Looks like I have generated a cliencert which has been signed by an untrusted CA. Will need to figure out how to either make it a trusted CA or generate a cert with one of the MySQL Servers trusted CA's.

// validate signature signed by someone else
bool CertDecoder::ValidateSignature(SignerList* signers)
{
    assert(signers);

    SignerList::iterator first = signers->begin();
    SignerList::iterator last  = signers->end();

    while (first != last) {
        if ( memcmp(issuerHash_, (*first)->GetHash(), SHA::DIGEST_SIZE) == 0) {
      
            const PublicKey& iKey = (*first)->GetPublicKey();
            Source pub(iKey.GetKey(), iKey.size());
            return ConfirmSignature(pub);
        }   
        ++first;
    }
    return false;
}
[1 Aug 2006 14:39] Magnus Blåudd
Just verified that I can connect with the ssltest java program to MySQL Server 5.0.25 that I just checked out from bk.

The steps I followed to get it working is:

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

It is now possible to connect with SSL turned on.
[1 Aug 2006 15:01] Magnus Blåudd
Here is the update test program I used to connect and read some data from table t1.

import java.sql.*;
import java.net.SocketException;

class ssltest
{
  private static com.mysql.jdbc.Driver drv = null;
    public static void main(String args[]) throws SQLException
    {
    String sqlStr = "";
    Connection c = null;
    boolean isSslOn =  true;

     if ( drv == null )
      {
      drv = new com.mysql.jdbc.Driver();
      }

    String connection =  "jdbc:mysql://127.0.0.1:9306/test?user=root&password=";

    if ( isSslOn )
      {
      /*
       * with appropriate values for keystore, truststore, and password
       */
      System.setProperty("javax.net.ssl.keyStore","keystore");
      System.setProperty("javax.net.ssl.keyStorePassword","password");
      System.setProperty("javax.net.ssl.trustStore","truststore");
      System.setProperty("javax.net.ssl.trustStorePassword","password");
      connection += "&useSSL=true";
      }

     // this works on linux, but not on Windows
      c = drv.connect( connection, null );

      Statement stmt = c.createStatement();

      String query = "SELECT a FROM t1";
      ResultSet rs = stmt.executeQuery(query);
      while (rs.next()) {
        String s = rs.getString("a");
        System.out.println(s);
      }
    }
}
[1 Aug 2006 15:06] Magnus Blåudd
Very nice page describing how to setup keystores for the java client.
http://www.wso2.net/tutorials/wss4j/2006/06/15/setting-up-keystores
[3 Aug 2006 9:16] Magnus Blåudd
Both Shane and I got this working yesterday, so it's been fixed by upgrading to latest version of yaSSL.

Tested with 5.0.25
[18 Aug 2006 12:01] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.25 and 5.1.12 changelogs.
[19 Oct 2007 21:43] 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
[22 Oct 2007 14:55] Magnus Blåudd
Nice! That is an effect of another bugfix. If you don't use --ssl-ca nor --ssl-capath the client will connect using ssl but not check it's the right server.