FreeTDS fails to connect to SQL Server after recent Windows patches and TLS1 being disabled

Question

I'm having a weird problem with FreeTDS (MacOS 10.11.5) not connecting to SQL Server 2014 running on Windows Server 2012 r2 which I had been able to connect to previously (about a week or so ago). The server in the last week has been going through security hardening (most recent 2012 r2 patches, plus other things* which I'm assuming is the culprit, but my local dev machine (and actually only FreeTDS on my machine) seems to be the only problem after the patches.

I suspect that what is causing the problem lies somewhere in the RC4 ciphers and SSL 2.0 & TLS 1.0 being disabled, but I don't know how to remediate that.

To be clear, unlike other similar questions - I can not via any means connect freetds to DB01, but I can connect other drivers to DB01 (but I am developing a python app and need freetds in this instance) and other machines can connect to DB01.

The basic error is:

$ tsql -S DB01 -U db_user
Password: ****************
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Error 20002 (severity 9):
    Adaptive Server connection failed
There was a problem connecting to the server

Not super helpful - I've done the normal troubleshooting, including trying various TDS versions, using different switches on the command line, etc.

The log also points to the possibility of there being an SSL problem - e.g. tls.c logging "handshake failed", the packet that contains the string "SSL_Self_Signed_Fallback" (otherwise the packets are unreadable):

net.c:216:Connecting to 000.000.000.000 port 1433 (TDS version 7.4)
net.c:242:tds_open_socket: connect(2) returned "Operation now in progress"
net.c:343:tds_open_socket() succeeded
packet.c:741:Sending packet
[blah blah]
login.c:1185:detected flag 0
tls.c:116:in tds_push_func_login
tls.c:86:in tds_pull_func_login
packet.c:741:Sending packet
[blah blah]
packet.c:639:Received packet
[blah blah... what?
xxx |..0.S.S. L._.S.e.|
xxx |l.f._.S. i.g.n.e.|
xxx |d._.F.a. l.l.b.a.|
xxx |c.k0...1 blahblah|
tls.c:116:in tds_push_func_login
packet.c:741:Sending packet
0000 12 01 00 0f 00 00 00 00-15 03 00 00 02 02 28    |........ ......(|
tls.c:923:handshake failed
login.c:530:login packet rejected
query.c:3796:tds_disconnect() 
util.c:165:Changed query state from IDLE to DEAD
util.c:322:tdserror(0x7fef2b403aa0, 0x7fef2b403ba0, 20002, 0)
util.c:352:tdserror: client library returned TDS_INT_CANCEL(2)
util.c:375:tdserror: returning TDS_INT_CANCEL(2)
mem.c:644:tds_free_all_results()

When I connect to other servers and look at freetds.log I can read the packets (sort of), e.g.:

xxx |.C.h.a.n .g.e.d. |
xxx |.d.a.t.a .b.a.s.e|
xxx |. .c.o.n .t.e.x.t|
xxx |. .t.o.  .'.m.a.s|
xxx |.t.e.r.' 

unlike DB01 where the packets are lines and lines of }.???G?? .?T???٠

Here's the freetds Compile-time settings -- do I need GnuTLS = yes?:

$ tsql -C
Compile-time settings (established with the "configure" script)
                            Version: freetds v1.00.9
             freetds.conf directory: /usr/local/Cellar/freetds/1.00.9/etc
     MS db-lib source compatibility: no
        Sybase binary compatibility: no
                      Thread safety: yes
                      iconv library: yes
                        TDS version: 7.3
                              iODBC: no
                           unixodbc: yes
              SSPI "trusted" logins: no
                           Kerberos: no
                            OpenSSL: yes
                             GnuTLS: no
                               MARS: no

here's my freetds.conf file:

[global]
    # TDS protocol version
    tds version = auto
    dump file = /tmp/freetds.log
    debug flags = 4FFF
    text size = 64512
[DB01]
    host = db01.mydomain.tld
    port = 1433
    tds version = 7.4
    database = DB_NAME
    # I added this in case it was a cert issue, see below
    check certificate hostname = no

Some other quick troubleshooting data points:

• Using TCPVew on the server I can see that my connections are being accepted (but the log confirms that as well)

• We have a windows server that serves PHP pages (connecting to DB01 via ODBC) and we've had no problems with that server connecting to DB01

• I can use jTDS (via IntelliJ and Pycharm) to connect to the db01 fine, this would have been the end of the investigation if I could wire jTDS up in a django app.

• Microsoft's JDBC Driver will not connect to db01 (this is also new), that driver gives this error:

  [08S01] The driver could not establish a secure connection to SQL Server 
  

  by using Secure Sockets Layer (SSL) encryption. Error: "Server chose SSLv3, but that protocol version is not enabled or not supported by the client." ... java.lang.RuntimeException: javax.net.ssl.SSLHandshakeException: Server chose SSLv3, but that protocol version is not enabled or not supported by the client.

Has anyone else seen this? Is there a way to specify TLS 1.2, etc. when connecting with freetds? (I haven't been able to find docs on this)

UPDATE:

I thought to look in the Windows event viewer for any errors, and this is what's in there:

    DB01    17836   Error   MSSQLSERVER Application 7/20/2016 2:52:18 PM
    The login packet used to open the connection is structurally invalid;
 the connection has been closed. Please contact the vendor of the client
 library. [CLIENT: [my ip address]]

    [and also]

    Length specified in network packet payload did not match number of 
bytes read; the connection has been closed. Please contact the vendor 
of the client library. [CLIENT: [my ip address]]

Answer 1

TLDR; I needed to re-install freetds with support for gnutls instead of openssl.

After a lot (no, really a lot) of trial and error I finally figured out a solution to freetds on the mac not connecting.

I still need to wire up the rest so pyodbc works, etc. but here's the basic fix:

brew edit freetds

Replace the freetds formula with this https://gist.github.com/hanleybrand/dfb7b9004aae250fabd01cd2466251c4

In short, it adds the option --with-gnutls to the brew install and makes sure that if it exists it occurs before --with-openssl. I haven't looked into it heavily, but I suspect that openssl/gnutls is either/or and not and/or.

brew rm freetds && brew install freetds --with-gnutls --with-unixodbc

After that, tsql worked fine - as I mentioned above I still have to setup the rest (unixodbc, pyodbc), but I'm pretty confident that if tsql works that the rest will too, although I can't be entirely sure.

This may be related to cipher sets in the two packages (openssl agains gnutls) as @FlipperPA points out