Retrieve public server certificate key during handshake

Question

I want to retrieve the sent public server certificate of a Microsoft SQL Server (2012/2014) during SSL/TLS handshake using my Java application.

My environment first:

• MS SQL is set up to use force encryption
• only accepts SSL/TLS connection
• has among others a self-signed CA and a certificate issued by said CA
• issued certificate is used by MS SQL server

To achieve this programmatically I am using my own trust manager implementation. Please see the excerpt of the relevant code here:

SSLSocket sslSocket = (SSLSocket) getFactory().createSocket(socket, host, port, true);
sslSocket.startHandshake();

getFactory():

private SSLSocketFactory getFactory() throws IOException
{
    // irrelevant code removed here
    return factory();
}

factory():

private static SSLSocketFactory factory() throws NoSuchAlgorithmException, KeyManagementException 
{
    SSLSocketFactory factorySingleton;
    SSLContext ctx = SSLContext.getInstance("TLS");
    ctx.init(null, getTrustManager(), null);
    factorySingleton = ctx.getSocketFactory();

    return factorySingleton;
}

getTrustManager():

private static TrustManager[] getTrustManager()
{
    X509Certificate[] server = null;
    X509Certificate[] client = null;
    X509TrustManager tm = new X509TrustManager()
    {
        X509Certificate[] server1 = null;
        X509Certificate[] client1 = null;
        public X509Certificate[] getAcceptedIssuers()
        {
            return new X509Certificate[0];
        }

        public void checkServerTrusted(X509Certificate[] chain, String x)
        {
            server1 = chain;
            Logger.println("X509 Certificate chain: " + chain);
        }

        public void checkClientTrusted(X509Certificate[] chain, String x)
        {
            client1 = chain;
            Logger.println("X509 Certificate chain: " + chain);
        }
    };

    return new X509TrustManager[]{tm};
}

I was expecting that the call to startHandshake() would at some point make my application receive the different certificates from my SQL server and in an attempt to verify them call my custom trust manager. At this point I would have the certificates (X509Certificate[] chain). But my trust manager is not called or at least the breakpoints inside both checker methods are not called.

This is one of the MS docs I used for reference: https://msdn.microsoft.com/en-us/library/bb879919(v=sql.110).aspx#Anchor_1

"During SSL handshake, the server sends its public key certificate to the client." <--- exactly what I want/need.

Answer 1

After week long searches I have found the problem. What does not work/is only a workaround can be seen here: https://superuser.com/questions/1042525/retrieve-server-certificate-from-sql-server-2012-to-trust

The problem/issue is the TDS (Tabular Data Stream) protocol used by Microsoft, which is an application layer protocol wrapping all layers and connections underneath. That means a driver has to implement this TDS protocol when connecting to Microsoft SQL server or Sybase (TDS was created by Sybase initially). FreeTDS is such an implementation and for Java there is jTDS, which is mostly dead unfortunately. Despite that there are still some fixes done but not included and released as a new jTDS version. jTDS can be found here: https://sourceforge.net/projects/jtds/files/ but with Java 1.8 there was a change to a data type which caused jTDS to send 256 bytes of nonsense to MSSQL thus making SSL/TLS impossible. This was fixed in r1286 (https://sourceforge.net/p/jtds/code/commit_browser)

After applying these changes and using at least the connection string property SSL=require the custom trust manager in net\sourceforge\jtds\ssl\SocketFactories.java:

private static TrustManager[] trustManagers()
{
    X509TrustManager tm = new X509TrustManager()
    {
        public X509Certificate[] getAcceptedIssuers()
        {
            return new X509Certificate[0];
        }

        public void checkServerTrusted(X509Certificate[] chain, String x)
        {
            // Dummy method
        }

        public void checkClientTrusted(X509Certificate[] chain, String x)
        {
            // Dummy method
        }
    };

    return new X509TrustManager[]{tm};
}

will be called. With this the described way in OP can be used to retrieve a certificate from the server. This is not the intended usage so one needs to add some ugly getter/setter and trickery to actually get the certificate one such approach is the following changes:

In net\sourceforge\jtds\jdbc\SharedSocket.java change enableEncryption() to this:

void enableEncryption(String ssl) throws IOException
{
  Logger.println("Enabling TLS encryption");
  SocketFactory sf = SocketFactories.getSocketFactory(ssl, socket);
  sslSocket = sf.createSocket(getHost(), getPort());
  SSLSocket s = (SSLSocket) sslSocket;
  s.startHandshake();
  setX509Certificates(s.getSession().getPeerCertificateChain());

  setOut(new DataOutputStream(sslSocket.getOutputStream()));
  setIn(new DataInputStream(sslSocket.getInputStream()));
}

and add the following field with its getter/setter:

private javax.security.cert.X509Certificate[] x509Certificates;

private void setX509Certificates(javax.security.cert.X509Certificate[] certs)
{
  x509Certificates = certs;
}

public javax.security.cert.X509Certificate[] getX509Certificates()
{
  return x509Certificates;
}

In net\sourceforge\jtds\jdbc\TdsCore.java change negotiateSSL() so that this is inclused:

if (sslMode != SSL_NO_ENCRYPT)
{
    socket.enableEncryption(ssl);
    setX509Certificate(socket.getX509Certificates());
}

And again have the exact same field with getter/setter:

public javax.security.cert.X509Certificate[] getX509Certificate()
{
    return x509Certificate;
}

public void setX509Certificate(javax.security.cert.X509Certificate[] x509Certificate)
{
    this.x509Certificate = x509Certificate;
}

private javax.security.cert.X509Certificate[] x509Certificate;

The same has to be done for net\sourceforge\jtds\jdbc\JtdsConnection.javas constructor JtdsConnection()

to call setX509Certificates(baseTds.getX509Certificate()) after negotiateSSL() was called on baseTds.negotiateSSL() inside the constructor. This class also must contain the getter/setter:

public javax.security.cert.X509Certificate[] getX509Certificates()
{
    return x509Certificates;
}

public void setX509Certificates(javax.security.cert.X509Certificate[] x509Certificates)
{
    this.x509Certificates = x509Certificates;
}

private javax.security.cert.X509Certificate[] x509Certificates;

Finally, one can create his own utility class to make use of all these addition like this:

JtdsConnection jtdsConnection = new JtdsConnection(url, <properties to be inserted>);
X509Certificate[] certs = jtdsConnection.getX509Certificates()

For the properties (they are not all the standard ones you usually find for jdbc) use the provided DefaultProperties.addDefaultProperties() and afterwards change user, password, host, etc in the new Properties() object.

PS.: One might wonder why all of these cumbersome changes... for example, because of licensing reasons one cannot ship Microsofts jdbc driver or does not want to / cannot use it, this provides and alternative.