Is there any way currently to install tiny_tds on a rails 7 app in Heroku on the latest stack? All the workarounds seem to be quite old / inoperable. I am trying to get an Azure MSSQL plugin up and running on Heroku.
I have been able to get the buildpack installed, but every time I use TDS (even making a connection via a heroku rails console), I get a timeout error. If I run the exact same command locally, it works.
client = TinyTds::Client.new host: '<host>.database.windows.net', database: "<database>",username: "<username>@<host>.database.windows.net",password: "<password>", port: 1433, azure: tr
ue, tds_version: 7.4, timeout: 300, login_timeout: 300
Adaptive Server connection timed out ([server].database.windows.net:1433)
Additionally, I can run the following successfully on a heroku bash prompt:
~ $ nslookup
> <host>.database.windows.net
Server: <server ip address>
Address: <server ip address>#53
Non-authoritative answer:
<host>.database.windows.net canonical name = <other host name>.eastus.database.windows.net.
<other host name>.eastus.database.windows.net canonical name = <other host name>.trafficmanager.net.
<other host name>.trafficmanager.net canonical name = <another host name>.control.database.windows.net.
Name: <another host name>.control.database.windows.net
Address: <ip address>
~ $ nc -zv <host>.database.windows.net 1433
Connection to <host>.database.windows.net (<ip address>) 1433 port [tcp/ms-sql-s] succeeded!
I have also tried using freetds on heroku (which is required for tinytds) and get timeouts, so I believe the error traces back to freetds' interaction with heroku or the heroku buildpack, of which I have tried a variety of versions (https://github.com/rails-sqlserver/heroku-buildpack-freetds):
On my local macbook, I can run the freetds tsql connection command to connect almost instantaneously to a variety of mssql databases (one on ec2 and two on azure), but the same command times out on heroku bash prompt:
~ $ tsql -H ***.rds.amazonaws.com -p 1433 -U *** -P ***
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
103Error 20002 (severity 9):
Adaptive Server connection failed
Error 20002 (severity 9):
Adaptive Server connection failed
There was a problem connecting to the server
FreeTDS version local and remote. I did no special "configuration" file settings other than setting the TDS_VERSION to 7.3 when installing the buildpack on heroku.
# LOCAL:
$ tsql -C
Compile-time settings (established with the "configure" script)
Version: freetds v1.3.13
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
# Heroku:
$ heroku run bash -a <app>
Running bash on ⬢ <app>... up, run.4557 (Hobby)
~ $ tsql -C
Compile-time settings (established with the "configure" script)
Version: freetds v1.3.13
freetds.conf directory: /app/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: yes
GnuTLS: no
MARS: yes
Any ideas would be greatly appreciated!
This was due to a TLS/openssl compatibility issue. Using gnutls fixes the issue for now.
Some other notes on the issue: https://github.com/FreeTDS/freetds/issues/336 https://github.com/FreeTDS/freetds/issues/299
If you need to use freetds on heroku-22, see this pull request. https://github.com/rails-sqlserver/heroku-buildpack-freetds/pull/20
Many thanks to @engineersmnky for some great questions that led to discovering this answer.