MSSQL Database Connection SSL Problems

glinklater · February 24, 2023, 8:49am

I am trying to make a connection to a MSSQL database which is proving difficult. My setup is as follows:

Datalore: 2023.1 on Docker
Driver: MSSQL JDBC 11.2.1 (mssql-jdbc-11.2.0.jre8-preview.jar — set by Datalore)
JRE: java-17-openjdk-amd64
TLSv1.2
URL: jdbc:sqlserver://A.B.C.D:1433;database=???;trustServerCertificate=true
Auth: user-pass

My connection through Datalore results in the following error message:

[ApplicationImpl pooled thread 1] c.i.d.d.DatabaseConnectionEstablisher - 
  [08S01] The driver could not establish a secure connection to SQL Server 
    by using Secure Sockets Layer (SSL) encryption. 
      Error: "Connection reset ClientConnectionId:...".

At first I suspected something was wrong with my networking, however I am able to make a connection through the same host using DataGrip with the exact same driver version and parameters.

Anything I can try to debug this further? Or is this a bug in Datalore?

Error Log

java.io.IOException: Connection reset ClientConnectionId:...
        at com.microsoft.sqlserver.jdbc.TDSChannel$SSLHandshakeInputStream.ensureSSLPayload(IOBuffer.java:918)
        at com.microsoft.sqlserver.jdbc.TDSChannel$SSLHandshakeInputStream.readInternal(IOBuffer.java:968)
        at com.microsoft.sqlserver.jdbc.TDSChannel$SSLHandshakeInputStream.read(IOBuffer.java:961)
        at com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream.readInternal(IOBuffer.java:1207)
        at com.microsoft.sqlserver.jdbc.TDSChannel$ProxyInputStream.read(IOBuffer.java:1194)
        at java.base/sun.security.ssl.SSLSocketInputRecord.read(SSLSocketInputRecord.java:478)
        at java.base/sun.security.ssl.SSLSocketInputRecord.readHeader(SSLSocketInputRecord.java:472)
        at java.base/sun.security.ssl.SSLSocketInputRecord.decode(SSLSocketInputRecord.java:160)
        at java.base/sun.security.ssl.SSLTransport.decode(SSLTransport.java:111)
        at java.base/sun.security.ssl.SSLSocketImpl.decode(SSLSocketImpl.java:1505)
        at java.base/sun.security.ssl.SSLSocketImpl.readHandshakeRecord(SSLSocketImpl.java:1420)
        at java.base/sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:455)
        at java.base/sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:426)
        at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1795)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:3329)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2950)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2790)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1663)
        at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1064)
        at com.intellij.database.remote.jdbc.helpers.JdbcHelperImpl.connect(JdbcHelperImpl.java:54)
        at com.intellij.database.remote.jdbc.impl.RemoteDriverImpl.connect(RemoteDriverImpl.java:26)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:568)
        at java.rmi/sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:360)
        at java.rmi/sun.rmi.transport.Transport$1.run(Transport.java:200)
        at java.rmi/sun.rmi.transport.Transport$1.run(Transport.java:197)
        at java.base/java.security.AccessController.doPrivileged(AccessController.java:712)
        at java.rmi/sun.rmi.transport.Transport.serviceCall(Transport.java:196)
        at java.rmi/sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:587)
        at java.rmi/sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(TCPTransport.java:828)
        at java.rmi/sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.lambda$run$0(TCPTransport.java:705)
        at java.base/java.security.AccessController.doPrivileged(AccessController.java:399)
        at java.rmi/sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:704)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
        at java.base/java.lang.Thread.run(Thread.java:833)
        Suppressed: com.intellij.database.util.AsyncTask$Frame$FrameData: Async frame data: <Anonymous> -> Refresh -> Submit -> Prepare connection -> Establish database connecti
on -> Get database credentials -> Perform database connection

glinklater · February 26, 2023, 12:58pm

After some further investigation it seems the problem is with OpenSSL 3.0.2.

If I attempt to connect to SQL Server using the following:

openssl s_client -connect A.B.C.D:1433

It produces the following error:

0A000126:SSL routines:ssl3_read_n:unexpected eof while reading:../ssl/record/rec_layer_s3.c:308

After some searching it seems a change was introduced to make checking the EOF more strict, as not doing so creates a vulnerability, however it seems this has broke many things. The man pages explain the problem further:

SSL_OP_IGNORE_UNEXPECTED_EOF

Some TLS implementations do not send the mandatory close_notify alert on shutdown. If the application tries to wait for the close_notify alert but the peer closes the connection without sending it, an error is generated. When this option is enabled the peer does not need to send the close_notify alert and a closed connection will be treated as if the close_notify alert was received.

You should only enable this option if the protocol running over TLS can detect a truncation attack itself, and that the application is checking for that truncation attack.

For more information on shutting down a connection, see SSL_shutdown(3).

The reason why my other test worked was because it was using LibreSSL instead of OpenSSL.

I’m not really sure what to do about this; OpenSSL 3.0.2 is the published version of the latest Ubuntu LTS so its not going to be arbitrarily updated, but in the meanwhile I can’t use Datalore with my database.

Thoughts?

glinklater · March 15, 2023, 2:56pm

I think I have ruled out OpenSSL being the problem; I am able to connect to the database using the ODBC driver using OpenSSL v3.0.2.

The next thing to try would be the JDBC driver; I’ve tried using sqlline with MSSQL JDBC 11.2.1, 11.2.3, and 12.2.0 (all jre8). None of those have been successful. I’ll continue by trying some earlier versions.

Turns out I was using sqlline incorrectly, after correcting my use the JDBC drivers work fine… looks like this might be a bug in Datalore.

glinklater · March 16, 2023, 11:38am

After further testing with sqlline, I was able to connect to the database with the same JDBC driver from the same host.

I tried the same inside the jetbrains/datalore-agent:2023.1 docker image (58f1eaaa37a4) and the connection failed once more. Something about the docker image on an Ubuntu 22.04 does not like connecting to SQL Server. I tried the same docker image and process using sqlline from macOS and it connected fine.

glinklater · March 21, 2023, 8:35am

After further testing, I was able to connect to the database briefly after the DBMS’ host was restarted. Some hours afterwards Datalore produces the same error as described above. Other applications continue to connect to the DBMS normally and no changes were made to the DBMS or host configuration in the meanwhile.