openssl vs. gnutls

[bf4]

I'm on the Heroku-22 stack and had been using the buildpack https://github.com/rails-sqlserver/heroku-buildpack-freetds.git#v1.1.5

After removing the buildpack to rely on the vendored freetds present in tiny_tds 3.2.0, some, but not all, of our database connections began to fail at login.

After comparing the outputs of tsql -C prior to removing the buildpack and bundle exec tsql-ttds -C afterwards, I noticed

- OpenSSL: yes
- GnuTLS: no
+ OpenSSL: no
+ GnuTLS: yes
+ MARS: yes

which led me to believe that, at least on Heroku-22, for some connections, GnuTLS works and OpenSSL doesn't.

I'm not sure why the tradeoff is for changing the build args so for now I'm just reporting this for discussion.

This is against tiny_tds v3.2.0 on Ruby 3.3.4

Related rails-sqlserver/heroku-buildpack-freetds#20
On topic #521 (comment)

#### Buildpack
# FREETDS_VERSION=1.4.26
# https://github.com/rails-sqlserver/heroku-buildpack-freetds.git#v1.1.5
tsql -C

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

#### Vendored
# FREETDS_VERSION=1.4.26
bundle exec tsql-ttds -C

[TinyTds][v3.2.0][tsql]: /app/vendor/bundle/ruby/3.3.0/gems/tiny_tds-3.2.0-x86_64-linux-gnu/ports/x86_64-linux-gnu/bin/tsql
Compile-time settings (established with the "configure" script)
                            Version: freetds v1.4.26
             freetds.conf directory: /home/runner/work/tiny_tds/tiny_tds/ports/x86_64-linux-gnu/etc
     MS db-lib source compatibility: no
        Sybase binary compatibility: no
                      Thread safety: yes
                      iconv library: yes
                        TDS version: auto
                              iODBC: no
                           unixodbc: no
              SSPI "trusted" logins: no
                           Kerberos: no
                            OpenSSL: yes
                             GnuTLS: no

Example of connection failing at password auth

TDSVER="${TSQL_TDSVER}" bundle exec tsql-ttds -H "${test_ip}" -p "${test_port}" -U "$TSQL_USERNAME"

[TinyTds][v3.2.0][tsql]: /app/vendor/bundle/ruby/3.3.0/gems/tiny_tds-3.2.0-x86_64-linux-gnu/ports/x86_64-linux-gnu/bin/tsql
Password:

locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Error 20017 (severity 9):
	Unexpected EOF from the server
Error 20002 (severity 9):
	Adaptive Server connection failed
Error 20002 (severity 9):
	Adaptive Server connection failed

[andyundso]

there have been various reports mainly in the FreeTDS issue tracker that for some connections, GnuTLS appears to work while OpenSSL does not. but it seems it was mostly against older MSSQL servers, where OpenSSL seems to drop support for older ciphers faster.

I personally would not expect any troubles if we switch from OpenSSL to GnuTLS, although we should likely couple it with a major release.

What is the MSSQL version you are connecting against?

[bf4]

What is the MSSQL version you are connecting against?

Good question. I'll collect those versions.

Thanks for the quick review.

[bf4]

Here's what I found

Major Version	Patches in use
SQL Server 2012 (11.x)	SP4
SQL Server 2014 (12.x)	RTM, SP3
SQL Server 2016 (13.x)	RTM, SP2, SP3
SQL Server 2017 (14.x)	CU29, CU31
SQL Server 2019 (15.x)	RTM, GDR, CU27, CU29, CU32
SQL Server 2022 (16.x)	RTM, CU15

and

OS family	Supported SQL Server releases
Linux (Amazon Linux, RHEL, etc.)	2019 only
Windows Server (2012 R2 → 2022)	2012 → 2022
Windows 10 (various editions)	2014 → 2022

Various builds I've tried used in the last two days

Build/context	TLS backend
v1.00.109 (legacy buildpack)	GnuTLS only
v1.4.26 (standard buildpack)	GnuTLS only
v1.4.26 (tiny_tds port)	OpenSSL only
v1.4.24 (Homebrew on macOS)	OpenSSL only

and noting that my legacy buildpack version 1.00.109 worked with the most, such as Microsoft SQL Server 2014

However, that might be related to not supporting sybase or db lib. In brief, it may work better because it

• Always uses TDS 7.3 (universally supported by 2012–2022)
• Uses GnuTLS for TLS (falls back to older ciphers/protocols that older servers accept)
• Has no extra flags (no MARS, no Kerberos/SSPI, no Sybase or DB-Lib compatibility)

--> Minimalist login packet → highest compatibility with older Express/RTM instances

original buildpack build

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

1.4.26 buildpack build

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

tiny_tds v3.2.0 version

bundle exec tsql-ttds -C

Compile-time settings (established with the "configure" script)
                            Version: freetds v1.4.26
             freetds.conf directory: /home/runner/work/tiny_tds/tiny_tds/ports/x86_64-linux-gnu/etc
     MS db-lib source compatibility: no
        Sybase binary compatibility: no
                      Thread safety: yes
                      iconv library: yes
                        TDS version: auto
                              iODBC: no
                           unixodbc: no
              SSPI "trusted" logins: no
                           Kerberos: no
                            OpenSSL: yes
                             GnuTLS: no

my local OSX version for completeness

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

[andyundso]

well, we no longer support anything below SQL Server 2017 with tiny_tds v3, because we cannot reliably test it. so unless your failing connections are against SQL Server 2017 or above, I would refrain from changing the SSL for now.

[bf4]

well, we no longer support anything below SQL Server 2017 with tiny_tds v3, because we cannot reliably test it. so unless your failing connections are against SQL Server 2017 or above, I would refrain from changing the SSL for now.

So, to consider GnuTLS, you'd want a self-report that we're having failing connections on 2017+? I'll have a look at the logs

[andyundso]

I mean we can also just switch to GnuTLS. if this means for some use-cases it will work with SQL Server 2017 and older, so be it. I can still close new bug reports for these old versions with Won't Do :)