FreeTDS fails to connect to SQL Server after recent Windows patches and TLS1 being disabled
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 Serverby 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
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