12

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?

Awais Qarni
  • 17,492
  • 24
  • 75
  • 137
  • 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 Nov 12 '17 at 03:18
  • 1
    @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 Nov 12 '17 at 09:37
  • 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 ? http://support.webecs.com/kb/a868/how-do-i-configure-sql-server-express-to-allow-remote-tcp-ip-connections-on-port-1433.aspx – A. Lion Dec 21 '18 at 15:22
  • If telnet fails you need to resolve that issue first. – Salman A Sep 03 '19 at 09:37

1 Answers1

2

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>
tukan
  • 17,050
  • 1
  • 20
  • 48
  • @AdrianKeister - this is actually answer for you, so I don't know if that actually answers your issue. – tukan Aug 30 '19 at 08:16
  • This will not solve my issue, I'm afraid, as my files were already named correctly. – Adrian Keister Aug 30 '19 at 11:28
  • @AdrianKeister do you have the Express SQL Server? If yes, is it configured to accept connections from outside? – tukan Aug 30 '19 at 11:36
  • I'm pretty sure it's not Express, as I'm working at an enterprise level. I can connect to the server from my Mac and Windows machines just fine. I can also connect to the MS SQL Server database using `tsql` from Xubuntu 16.04. I just can't get `isql` or `pyodbc` (my ultimate goal) to work on Xubuntu. – Adrian Keister Aug 30 '19 at 14:01
  • @AdrianKeister I still don't understand you setup. You have server on the same machine or remote? – tukan Aug 30 '19 at 18:16
  • Server is definitely remote. It's in network, and I have an Azure Data Science Virtual Machine running Xubuntu 16.04 from which I'm trying to connect to the MS SQL Server database. – Adrian Keister Aug 30 '19 at 18:20
  • @AdrianKeister I see. Next comes the configuration, do you have it **exactly** as the origional OP (including the verisons) or you have some difference there? – tukan Aug 31 '19 at 07:48
  • I used the same configuration, as much as possible, to the OP's, and got the error `[IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified [ISQL]ERROR: Could not SQLConnect.` Now I don't know if the permissions are set right on the config files. The `tsql` command worked, just like for the OP. – Adrian Keister Sep 03 '19 at 20:44
  • @AdrianKeister I have edited the answer, based on your error number. – tukan Sep 04 '19 at 03:53
  • Two things: 1. What should I have for `instance = `? 2. Currently getting (without the `instance` line): `[37000][unixODBC][FreeTDS][SQL Server]Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication.` To be clear, I used the command `isql -v mssql domain\\username password`. Here `mssql` is my server's DSN (which works in `tsql`). Further thoughts? Do I need to set up Kerberos or something like it, to use Windows authentication? I'm pretty sure everything at my organization uses Windows authentication. – Adrian Keister Sep 04 '19 at 14:06
  • @AdrianKeister That is better. I think we need to move to a chat room, resolve it there and move post the result here. I have created the chat room at https://chat.stackoverflow.com/rooms/198986/room-for-tukan-and-adrian-keister – tukan Sep 05 '19 at 07:02
  • I finally got it to work, with help from IT, using Kerberos authentication, the ODBC Driver 17 for SQL Server (not FreeTDS), and all the right settings. – Adrian Keister Sep 19 '19 at 16:02
  • @AdrianKeister glad to hear that. ODBC is a tricky business because on the inside it is a mess. – tukan Sep 20 '19 at 06:00