Notes I took when trying to connect a Ruby on Rails web app to an existing on premises Microsoft SQL Server Database
me@jaykilleen.com wrote this over 7 years ago and it was last updated over 7 years ago.
It is a good idea to check your ubuntu version with lsb_release -a. I am running Ubuntu 16.04.3 LTS on a VirtualBox VM on Windows 10 (not that the last bit matters).
activerecord-sqlserver-adapter
I found it confusing installing freetds on ubuntu especially if Google the answer. It seemed the package available in xenial was outdated. There are gists around but I wasn't sure whether they were also going to be outdated. Or outdated to you as the reader of this article. The best information I found was on the tiny_tds gem github repo.
In particular this bit:
On all other platforms, we will find these dependencies. It is recommended that you install the latest FreeTDS via your method of choice. For example, here is how to install FreeTDS on Ubuntu. You might also need the build-essential and possibly the libc6-dev packages.
You should probably `sudo apt update` before you start. Also you might be using `apt` instead of `apt-get`.
apt-get wget
apt-get install build-essential
apt-get install libc6-dev
wget http://www.freetds.org/files/stable/freetds-1.00.21.tar.gz
tar -xzf freetds-1.00.21.tar.gz
cd freetds-1.00.21
./configure --prefix=/usr/local --with-tdsver=7.3
make
make install
If you want the latest stable release of free_tds that you can go here and replace 1.00.21 above with whatever version you want to go with.
I also had to sudo make and sudo make install instead of the instructions on the gem. I am a bit of a noob when it comes to unix though so I usually sudo most stuff which is probably wrong.
At this point I decided it would be a good idea to install the tiny_tds gem (which also included setting up my VM with ruby and rails. I usually follow this awesome Ruby on Rails Setup guide on GoRails and see if I can connect to the database using irb.
tsql is a utility to test FreeTDS connections and queries
This needs to be installed... or configured.
You can confirm tsql is installed by running tsql -C which will also output the version of tsql you have installed.
ubuntu@ubuntu-xenial:~$ tsql -C
Compile-time settings (established with the "configure" script)
Version: freetds v1.00.21
freetds.conf directory: /usr/local/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: no
MARS: no
At this point if you tried the following you would get an error that the Adaptive Server does not exist.
irb
> require 'tiny_tds'
> client = TinyTds::Client.new username: 'user', password: 'password', host: '123.456.7.8', database: 'database', encryption: 'require', tds_version: '7.1', contained: true
TinyTds::Error: Unable to connect: Adaptive Server is unavailable or does not exist
This is because we need to tell freetds where the servers are using the freetds.conf file. You can read about the freetds.conf file. In general it wants to know the ip address, port and tds version.
freetds.conf should be put in the same location as the freetds.conf directory: that you see when you run tsql -C. In this case it is /usr/local/etc.
If you cd or ls that folder you'll see the file.
ubuntu@ubuntu-xenial:~$ ls /usr/local/etc/
freetds.conf locales.conf pool.conf
And if you look at the defaults you see
# $Id: freetds.conf,v 1.12 2007-12-25 06:02:36 jklowden Exp $
#
# This file is installed by FreeTDS if no file by the same
# name is found in the installation directory.
#
# For information about the layout of this file and its settings,
# see the freetds.conf manpage "man freetds.conf".
# Global settings are overridden by those in a database
# server specific section
[global]
# TDS protocol version
tds version = auto
# Whether to write a TDSDUMP file for diagnostic purposes
# (setting this to /tmp is insecure on a multi-user system)
; dump file = /tmp/freetds.log
; debug flags = 0xffff
# Command and connection timeouts
; timeout = 10
; connect timeout = 10
# If you get out-of-memory errors, it may mean that your client
# is trying to allocate a huge buffer for a TEXT field.
# Try setting 'text size' to a more reasonable limit
text size = 64512
# A typical Sybase server
[egServer50]
host = symachine.domain.com
port = 5000
tds version = 5.0
# A typical Microsoft server
[egServer70]
host = ntmachine.domain.com
port = 1433
tds version = 7.0
We need to add a DNS to this file.
Let's try:
[mssql]
host = 123.456.7.8
post = 1433
tds version = 7.1
I am using 7.1 because tiny_tds has an issue and has advised 7.1. If you need to set tds to 7.1 you can use the environment variable. Run export TDSVER=7.1 and check using printenv | grep "TDS"
Test by bypassing freedtes.conf with TDSVER=7.1 tsql -H hostname.com -p 1433 -U user -P password
I see a count at this stage... 1,2,3,4,5,6,7,8 which means a connection is not being made and is hanging.
At this point I can't get tsq working as required so am not proceeding any further.
I can confirm that I can connect to my MS SQL database using tedious.js and also sqlcmd outside of the VM and back on the host Windows 10 machine.
I have no clue why I can't get free_tds and tsql to work.
My intention was to use Ruby on Rails because I am familiar with this MCV framework but am now looking at doing this with node.js seeings as it connects much easier than Ruby. It is a shame.