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

Answer 2

We had this error intermittently after Linux and MS patches were applied. We could still connect from Linux to MSSQL server but randomly our connection would terminate with the EOF error...even in the middle of a query. I turned on the freetds log and saw an encryption handshake failure like this:

    net.c:1366:handshake failed: A TLS packet with unexpected length was received.          
    login.c:466:login packet rejected
    util.c:331:tdserror(0x1e752b0, 0x2c27f40, 20002, 0)

After much troubleshooting we rolled back KB3172605 on the MS Windows Server 2008 R2 server that runs our MS SQL Server DB...it resolved the problem. (KB 3172605 supercedes KB 3161639. )

Answer 3

I did a bunch of testing with this, and our solution was to roll back this patch:

https://support.microsoft.com/en-us/kb/3161639

Apparently, the cipher suite affects more than Edge and IE. :) I opened up a case with Microsoft and they are aware of the problems this has caused. From the horse's mouth:

Since the issue is no longer occurring after uninstalling that KB update resolved the issue, I did a quick search on that KB update for prior cases. At this point there has been 21 cases on issues with that update since last month. If you are not aware and based off my research, KB 3161639 adds additional cipher keys that utilize the TLS 1.2 protocol that was introduced in KB update 3161608. The following ciphers are most likely the ones that are causing the issue:

TLS_DHE_RSA_WITH_AES_128_CBC_SHA

TLS_DHE_RSA_WITH_AES_256_CBC_SHA

I suspect that either your Unix/Linux server and/or the FreeTDS ODBC does not either support these cipher suites or is not setup for it.
With that said, you have a couple of options:

1. Use the workaround of not having the KB 3161639 update installed
2. Reinstall the KB 3161639 update. Configured your cipher ordering to ensure the new cipher suites are not chosen.

We've gone with option 1, and rolled it out throughout our network, and have been seeing no ill effects. I hope this helps.

Answer 4

I was seeing the same behavior under FreeTDS (version 0.91) after recent Microsoft patches.

In short, updating FreeTDS to the nightly snapshot available from http://www.freetds.org/ (1.00.24 as of this post) has fixed the issue for me. I compiled the source and updated the FreeTDS driver line in the odbcinst.ini config file to point to the new shared object file (<path>/libtdsodbc.so), e.g.:

[TDS]
Description = FreeTDS Driver
Driver = /usr/local/lib/libtdsodbc.so

---

There were some OpenSSL related commits to FreeTDS in early Sept., 2016 including this commit, which references a Microsoft patch incompatibility:

Update OpenSSL cipher list

This increase security and also fix a recent incompatibility between OpenSSL and SChannel (SChannel versions patched in a couple of months).

Based on other comments here that point to a cipher suite compatibility issue, this may have been the commit that fixed things. However, as of this post, there has not yet been an officially released version of FreeTDS that includes this commit (the last official release was version 1.0 in May 2016).

References to Microsoft patches mentioned in other comments:

• KB3161639 from June 2016 patch rollups.

  This article describes an update in which new TLS cipher suites are added and cipher suite default priorities are changed in Windows RT 8.1, Windows 8.1, Windows Server 2012 R2, Windows 7, or Windows Server 2008 R2. These new cipher suites improve compatibility with servers that support a limited set of cipher suites.

• KB3172605 from July 2016 patch rollups (and re-released in Sept. 2016). Description of this patch from the Windows Server 2008 R2 SP1 update history article:

  July 21, 2016 — KB3172605 This update includes quality improvements. No new operating system features are being introduced and no new security updates are included. Key changes include:

  • Improved support in Microsoft Cryptographic Application Programming Interface (CryptoAPI) to help identify websites that use Secure Hash Algorithm 1 (SHA-1).
  • Addressed issue in Microsoft Secure Channel (SChannel) that sometime causes Transport Layer Security (TLS) 1.2 connections to fail depending on whether the root certificate is configured as part of the certificate chain for server authentication.

Answer 5

After trial and error, I had the opposite experience from Peter Hanley that is documented in one of the above posts, although as Peter points out, the error is a bit different. I'm experiencing the EOF error mentioned by Scott which is rooted in a TLS error code 20 (bad_record_MAC). On the MS side, it's reported as an SChannel error.

Since I was deploying to multiple servers and wanted an RPM, I edited the freetds.spec file to remove the --with-gnutls and replaced it with --with-openssl=yes and then rebuilt the RPMs with rpmbuild, verifying that rpmbuild in fact used the directive I added and did not contain --with-gnutls.

For testing, I wrote a small Perl utility that connects to the database / disconnects in rapid succession.

I verified several times that when --with-gnutls is used, the TLS / SChannel / EOF error is encountered between 0.5% and 1% of the time.

When only the --with-openssl=yes directive is used and without --with-gnutls present, I don't get any errors. These tests were run minutes apart and involved 5000 connection attempts in rapid succession (about 10 connections opened and closed per second). No other freetds configuration options were modified. The only difference between the two tests are the directives listed above. The build and installation of the RPM, freetds configuration files, etc. was all automated and did not change between the tests.

When the error occurs, it's reported on the client side as:

DBI connect('MYDBNAME','mydomain\myusername',...) failed:
[unixODBC][FreeTDS][SQL Server]Unable to connect to data source (SQL-08001) [state was 08001 now 01000]
[unixODBC][FreeTDS][SQL Server]Adaptive Server connection failed (SQL-01000)
[unixODBC][FreeTDS][SQL Server]Unexpected EOF from the server (SQL-01000) at ./dbconnectiontestlots.pl line 18

This test used freetds-1.00.24-1.x86_64, downloaded directly from freetds.org, built on RHEL6. The following RPMs were installed from the /root/rpmbuild/RPMS/ folder for this test after running rpmbuild each time:

386996 Jan 5 14:28 freetds-1.00.24-1.x86_64.rpm
187560 Jan 5 14:28 freetds-unixodbc-1.00.24-1.x86_64.rpm

unixODBC x86_64 2.2.14-14.el6 was also installed directly through yum.

Update: while this solution works on 32-bit and 64-bit RHEL6 systems it doesn't work on RHEL5 32-bit systems