Connecting Redhat to SQL Server 2008 for Ruby on Rails

Question

I'm trying to connect Redhat Linux to a Microsoft SQL Server 2008. I already had trouble setting it up on windows (my test machine) but now I need to deploy it on the Linux machine where it will be in production.

So I've installed unixODBC and FreeTDS (with a lot of effort, not even sure if it was installed correctly :S), and the outcome of that is that I have 3 files in /usr/local/etc:

odbc.ini
odbcinst.ini
freetds.conf

I then edited the freetds.conf file and this is what I added:

[sqlServer]
host = servername
port = 4113
instance = sqlServer
tds version = 8.0
client charset = UTF-8

I had to find out the port number from my DBA, as it is set to dynamic in SQL Server 2008.

My odbcinst.ini file looks like this:

[FreeTDS]
Description     = TDS driver (Sybase/MS SQL)
Driver          = /usr/local/lib/libtdsodbc.so
Setup           = /usr/local/lib/libtdsS.so
CPTimeout       =
CPReuse         =
FileUsage       = 1

and my odbc.ini files looks like this:

[sqlServer]
Driver = FreeTDS
Description     = ODBC connection via FreeTDS
Trace           = 1
Servername      = sqlServer
Database        = RubyApp

So now I tried connecting to see if there is any connection by using

tsql -S sqlServer -U test -P test, however that only gives me the following error:

locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Error 20013 (severity 2):
        Unknown host machine name.
There was a problem connecting to the server

When I tried using isql, doing isql -v sqlServer test test, that spat out the following error:

[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[01000][unixODBC][FreeTDS][SQL Server]Unknown host machine name.
[ISQL]ERROR: Could not SQLConnect

Any ideas what I could be doing wrong?

Answer 1

If you cannot connect with tsql, then there is a connection issue with your SQL server or in freetds.conf.

First verify the port and named instance using tsql -LH [SQL server IP]

# tsql -LH 127.0.0.1
  ServerName HOME
InstanceName INSTANCE1
 IsClustered No
     Version 10.50.2500.0
         tcp 1434

If this doesn't work, there is a connection issue between your server and the SQL server.

If it does work, then set your port to match tcp above in freetds.conf.

[TDS]
host = 127.0.0.1
port = 1434
tds version = 7.0

Instance is set in your odbc.ini

[MSSQLExample]
Description = Example server
Driver = FreeTDS
Trace = No
Server = 127.0.0.1\INSTANCE1
Database = MyDatabase
port = 1434

And if all else fails, try using osql to get some feedback on which part is not working.

# osql -S MSSQLExample -U USERNAME -P PASSWORD

And for some good info on tsql error messages: http://freetds.schemamania.org/userguide/confirminstall.htm