MySQL Bugs: #45280: For SSL connection Keystore Requires Import of Client Key

Bug #45280	For SSL connection Keystore Requires Import of Client Key
Submitted:	3 Jun 2009 3:39	Modified:	18 Jan 2010 14:44
Reporter:	Donna Harmon	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J Documentation	Severity:	S3 (Non-critical)
Version:	5.1.7	OS:	Any
Assigned to:	Tony Bedford	CPU Architecture:	Any

Description:
Documentation is missing the step of importing SSL Client Key into the keystore in addition to the Client Certificate when importing an existing Client Certificate.  SSL connection will not work without it.  Also, it is not necessary for the client certificate to be in DER format, only the Key.

How to repeat:
View documentation here http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-using-ssl.html

Suggested fix:
Change to something like the following:

To import an existing certificate and key, first the key should be in DER format. You can use openssl to convert an existing key into the DER format.

Convert the client-key.pem to DER format:
shell> openssl pkcs8 -topk8 -nocrypt -in client-key.pem -out client-key.cert -outform der

Import the Client Certificate into the keystore using keytool:
shell> keytool -import -file client-cert.pem -keystore keystore -alias mysqlClientCertificate

When it asks you to "Trust this certificate" type: yes

Compile the ImportKey java program below:

import java.io.InputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.security.Key;
import java.security.KeyFactory;
import java.security.KeyStore;
import java.security.PrivateKey;
import java.security.cert.Certificate;
import java.security.cert.CertificateFactory;
import java.security.spec.PKCS8EncodedKeySpec;

public class ImportKey  {
	private static String keystorename, keyalias, keypass, keyfile, certfile;
	private static final Error USAGE = new Error("Invalid arguments: { -e | -i } <keystore> <keyalias> <keypass> <pkcs8file> <x509 certificate>.");

	public static void main(String[] args) throws Exception {
    	if (args.length != 6)
    		throw USAGE;
    	
    	keystorename = args[1];
    	keyalias = args[2];
    	keypass = args[3];
    	keyfile = args[4];
    	certfile = args[5];
    	
    	if ("-e".equals(args[0])) {
    		mainExport();
    		return;
    	}
    	if ("-i".equals(args[0])) {
    		mainImport();
    		return;
    	}
    	
    	throw USAGE;
    }
    
    private static void mainExport() throws Exception {
    	/* load pk and certificate from keystore */
        KeyStore ks = KeyStore.getInstance("JKS", "SUN");
        ks.load(new FileInputStream(keystorename), keypass.toCharArray());        
        Key pk = ks.getKey(keyalias, keypass.toCharArray());
    	Certificate cert = ks.getCertificate(keyalias);

    	/* Save key as pkcs8 non-crypted DER and certificate as x509 DER */
    	new FileOutputStream(keyfile).write(pk.getEncoded());
        new FileOutputStream(certfile).write(cert.getEncoded());
    }
    
    private static void mainImport() throws Exception {
        /* Read private key */
        InputStream fl = new FileInputStream(keyfile);
        byte[] key = new byte[fl.available()];
        fl.read(key, 0, fl.available());
        fl.close();
        KeyFactory kf = KeyFactory.getInstance("RSA");
        PrivateKey privateKey = kf.generatePrivate(new PKCS8EncodedKeySpec(key));

        /* generate certificate entry */
        CertificateFactory cf = CertificateFactory.getInstance("X.509");
        Certificate[] certs = cf.generateCertificates(new FileInputStream(certfile)).toArray(new Certificate[] {});
        
        /* load keystore, store as new keys */
        KeyStore ks = KeyStore.getInstance("JKS", "SUN");
        ks.load(new FileInputStream(keystorename), keypass.toCharArray());
        ks.setKeyEntry(keyalias, privateKey, keypass.toCharArray(), certs);
        ks.store(new FileOutputStream(keystorename), keypass.toCharArray());
    }
}

shell> javac ImportKey.java

Import the Client Key which is in DER format using the ImportKey java program:
shell> java -cp . ImportKey -i keystore_name key_alias_name keystore_password key_file cert_file

Verify your keystore entries are correct(review Owner and Issuer values):
shell> keytool -list -v -keystore keystore
You should have at least two entries, one for the client key imported and one for the client certificate:
Entry Type: keyEntry
Entry Type: trustedCertEntry

shell> keytool -list -v -keystore truststore
You should have at least one entry for the CA certificate:
Entry Type: trustedCertEntry

I'll take this one, given its possible connection to BUG#44198.

Problem with Generating New Certificate Documentation on same page as well:

Is currently:
To generate your own client certificate, use keytool to create a suitable certificate and add it to the keystore file:

 shell> keytool -genkey -keyalg rsa \
     -alias mysqlClientCertificate -keystore keystore 

...

Should be something like:

1) Generate Initial Key Pair

shell> keytool -genkey -keysize 1024 -keyalg rsa -alias mysqlClientCertificate -keystore keystore

2) Generate a CA Certificate Signing Request

shell> keytool -certreq -v -alias mysqlClientCertificate -keystore keystore -file client-sign-request.pem

3) Sign the Certificate

shell> openssl x509 -req -in client-sign-request.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-signed.pem

4) Import the CA Certificate

shell> keytool -import -alias CACertificate -file ca-cert.pem -keystore keystore

5) Import the Signed Certificate into the Keystore

shell> keytool -import -keystore keystore -alias mysqlClientCertificate -file client-signed.pem

Thanks very much for this.  I'd been banging my head against this wall for a day.

One further point:

In later editions of the JDK, keytool command names have been changed.  genkey is now genkeypair, etc.  I guess the old names will still work but the mysql documentation should probably reflect the differences somehow.

Also, there appears to be much confusion.  Users are reporting "solutions" that only solve encryption (REQUIRE SSL) but not authentication (REQUIRE X509).  This whole topic deserves better documentation, which one would think should be doable now that Sun and MySQL are one.  

In any case, thanks to Donna Harmon for providing this.

Unfortunately, I cannot get either of the methods suggested by Donna Harmon to work.

The first method ("importing an existing Client Certificate") fails on this step:

Import the Client Certificate into the keystore using keytool:
shell> keytool -import -file client-cert.pem -keystore keystore -alias
mysqlClientCertificate

with the error 

keytool error: java.lang.Exception: Input not an X.509 certificate

It's not entirely clear in this explanation where client-cert.pem was supposed to have come from but I assumed it was from the script created here: http://dev.mysql.com/doc/refman/5.1/en/secure-create-certs.html which I had previously run.

In any case, still no joy with the first method.

As far as the second method goes, it all the steps complete successfully but if I try to run a simple java program that uses the keys to access mysql
defining on the command line the following parameters
javax.net.ssl.keyStore
javax.net.ssl.keyStorePassword
javax.net.ssl.trustStore
javax.net.ssl.trustStorePassword

I get the following error:

java.security.InvalidAlgorithmParameterException: the trustAnchors parameter must be non-empty

I am not an expert but I do notice that the second method never mentions the truststore, unlike the first method.  Could this be why it doesn't work?

I wonder why this defect is considered "minor".  MySQL is distributing documentation that is flat out incorrect, and this brave attempt at a fix also is insufficient.  

This should be a fairly simple process - but I have wasted days on experimenting.  One should not need to be a JSSE expert to set this up.

Will be fixed as part of major Connector/J docs rewrite. See WL #4462 (a note has been made of this bug in that worklog item).