[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source

Question

I know this question is asked many times and I have tried all but nothing worked. I am trying to connect to a MSSQL database on a different server from Ubuntu 14.04.

Content from /etc/odbcinst.in

 [ODBC]
 Trace = No
 TraceFile = /tmp/odbc.log

 [FreeTDS]
 Description = FreeTDS
 Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
 Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
 UsageCount = 1
 fileusage=1
 dontdlclose=1

Content from /etc/odbc.in

 [mssql]
 Driver = FreeTDS
 ServerName = mssql
 Port = 1433
 Database = My Database //database has space
 Driver=/usr/local/lib/libtdsodbc.so
 UsageCount = 1
 TDS_Version = 7.3
  instance = SQLEXPRESS

[Default]
Driver=/usr/local/lib/libtdsodbc.so

And mssql section in /etc/freetds/freetds.conf

[mssql]

 host = server ip
 port = 1433
  database = My Databas
 instance = SQLEXPRESS

  tds version = 7.3
  client charset = UTF-8

When I run following command it generates numbers which increases

  tsql -S mssql -U username -P password

But when I run

  isql -v mssql username password

After almost 30 sections it returns

[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[08S01][unixODBC][FreeTDS][SQL Server]Unable to connect: Adaptive Server is unavailable or does not exist
[ISQL]ERROR: Could not SQLConnect

net.c:205:FAILED Connecting to 54.214.30.231 port 1433 (TDS version 4.2)

The error is showing TDS version 4.2 that is installed in my machine when I check it with tsql -C but I specified 7.3 in conf files. Is it the reason? If so how can I upgrade the version? If it isn't the reason then Can some one please let me know what I am doing wrong?

What version of FreeTDS are you running? I'm guessing that 7.3 might not be supported, try 7.2? But I don't think that's the problem. Normally when the numbers are increasing as you describe, it means it can't connect. Can you try using telnet to connect to your SQL Server instance? Try the command telnet sqlserver.domain.com 1433 with your server name and port and see if it connects, or just hangs trying to connect. — FlipperPA
@FlipperPA telnet didn't work. It says , couldn't not open connection, connection failed But I can create connection using SQL database client using same credentials. So what is wrong? — Awais Qarni
instance = SQLEXPRESS sounds weird to me, the MSSQL is generally used for standard server installation which is designed to serve clients outside of the machine where the system is installed, unlike the express edition that by default is allowing connections only from the same machine. Did you enabled the remote tcp connections on your SQL like described here ? support.webecs.com/kb/a868/… — A. Lion

tukan tukan · Accepted Answer · 2019-08-30T07:19:22

I think you are missing a letter i in your odbc configuration file. It should be instead of odbc.in a odbc.ini

From the isql man pages:

isql, iusql — unixODBC command-line interactive SQL tool Synopsis

isql DSN [USER [PASSWORD]] [options] Description

isql is a command line tool which allows the user to execute SQL in batch or interactively. It has some interesting options such as an option to generate output wrapped in an HTML table.

iusql is the same tool with built-in Unicode support. Arguments

DSN

The Data Source Name, which should be used to make connection to the database. The data source is looked for in the /etc/odbc.ini and $HOME/.odbc.ini files in that order, with the latter overwriting the former.

USER Specifies the database user/role under which the connection should be made.

PASSWORD password for the specified USER.

The same goes for /etc/odbcinst.in. Should be /etc/odbcinst.ini

Edit based on comment

Adrian in your case I think it would be better to create a new question as this error number is different from OP.

The error message: [IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified [ISQL]ERROR: Could not SQLConnect.

Based on your error message you need to add Server=ip_address option to your odbc.ini (I recommend adding description too). Don't forget that the name in the brackets [mssql] must be a ServerDSN! Are you sure you have the correct TDS version specified at TDS_Version = 7.3? Driver directive should be enough in /etc/odbcinst.ini. Why there are two different drivers - Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so and Driver=/usr/local/lib/libtdsodbc.so is the second one only symlink?

Now the configuration would look something like this (I have to guess as I did not see your configuration):

[mssql]
Description = "My MSSQL DB for data science"
Driver = FreeTDS
ServerName = mssql
Server = <ip_address>
Port = 1433
Database = My Database //database has space
UsageCount = 1
TDS_Version = 7.3
instance = <my_mssql_instance>

[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source

1 Answers