2

This one is really starting to stump me. I'm running Ubuntu 12.04 and am trying to connect to a box running MS SQL server 2012. First I'll provide some information on my setup:

My freetds.conf:

[EXNAME]
    host = IP
    port = 1433
    tds version = 7.0
    client charset = UTF-8

After setting up my freetds.conf I first test my connection with tsql which produced the expected (and welcomed) prompt:

 ...
 using default charset "UTF-8"
 1>

So my FreeTDS setup appears to be working ok. Next I setup odbc.ini and obdcinst.ini:

odbc.ini:

 [EXNAME]
 Driver = FreeTDS
 ServerName = EXNAME
 UID = Me
 PWD = Pass

odbcinst.ini:

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

Following this setup I run osql to check. It outputs all the expected responses, it is able to find the drivers and the correct paragraphs in ondcb.ini for example:

 [EXNAME] found in /etc/odbc.ini
 found this section:
     [EXNAME]
     Driver = FreeTDS
     ServerName = EXNAME
     UID = Me
     PWD = Pass

     looking for driver for DSN [EXNAME] in /etc/odbc.ini
     found driver line: "   Driver = FreeTDS"

And so on until: 

     DSN [EXNAME] has servername "" (from /etc/odbc.ini)

osql does not seem to be detecting the servername in odbc.ini.

Following this check I am able to connect via isql but I cannot run any SQL commands. I receive the following error:

 SQL> SELECT name FROM master..sysdatabases;
 [37000][unixODBC][FreeTDS][SQL Server]Could not find stored procedure 'SELECT'.
 [ISQL]ERROR: Could not SQLExecute

What I've tried:

A.) Just about every configuration and naming convention one could think of across freetds.conf and both .ini files. Post changes I perform the same tests above and arrive at the same result

B.) Reproducing the error outside of isql. ROBC given me exactly the same error. Of course, this is expected but I figured why not

C.) SQL variants besides MS SQL Server. I figured just in case someone was mis-informing me I tried some MySQL commands there as well. This could be a SQL problem but I'm ot sure.

D.) The closest I've come to finding a similar error (well result I guess) to what osql spit out is here. As suggested in the last post, the problem, at least in so far as it relates to osql's response might relate to awk. As suggested I test awk by making a variable and trying to parse out the servername:

$ SERVER_LINE='ServerName = SERVER'
$ echo ${SERVER_LINE} | awk -F '=[[:space:]]*' '{print $2}'

This should result in SERVER but instead I get "".

That's all I have. In summary OSQL cannot seem to find the servername in odbc.ini and this may or may not result in me not being able to execute any commands from ISQL or ROBC. I'm beginning to suspect that these problems are disjoint but any help would be GREATLY appreciated.

EDIT:7/31/2014 Got this all working. There were some other issues going on in addition to what I highlighted. I ended up having to set up a similar connection on both a Mac and Linux box. Here are the instructions for both: http://www.joecjr.com/2014/07/27/install-and-use-freetds-unixodbc-and-rodbc-or-pyodbc/

JoeC
  • 53
  • 1
  • 6
  • My experience is that you are probably better off with a Java/JDBC based tool if you want to access SQL Server from a Linux environment. – a_horse_with_no_name Jul 18 '14 at 12:50
  • I was able to set this up in RedHat, but it was a beast... once it's working, it's worth it, though. What happens when (from the bin directory) you run "odbcinst -q -d" -- does it list your datasource? – Hambone Jul 18 '14 at 13:40
  • @Hambone would you mind looking at my, related question? It seems like you may have some insight into my issue. https://stackoverflow.com/questions/25774778/trouble-installing-freetbs-with-unixodbc-on-debian-servername – alternated direction Sep 10 '14 at 21:46

1 Answers1

2

It took me a LONG time to properly configure unixODBC with TDS, but for what it's worth, this set of instructions was my saving grace and what finally allowed me to get it working:

http://www.unixodbc.org/doc/FreeTDS.html

Pay particular attention to the second that references this command:

odbcinst -i -s -f tds.datasource.template

It's where my biggest problem resided. What happened was I was doing this as root (which is fine), but if you read carefully it indicates that every user that uses the driver has to run this command to 'register' the connection for their userid. Once I ran the same command under each other user, everything worked fine (since my actual code doesn't run as root).

One other thought... a quick and easy test to see if the connection is actually working with TDS (even before you try the ODBC piece) is to run bcp and export a really small table.

Try something like this, just to rule out the TDS install being the issue.

freebcp <database>.dbo.<tablename> out $HOME/foo.out -c -t '|' \
    -S <hostname>:<port> -U <userid> -P <password>

It's a shame Microsoft and Unix don't play nicer together.

Hambone
  • 13,222
  • 6
  • 37
  • 59
  • Hambone, thanks so much for your response. I'm pretty your permissions issue is exactly what I'm facing here. I'll need to check it out a little more. After I'm done I'll post the procedure that I used to (hopefully) connect. – JoeC Jul 18 '14 at 16:41
  • Is it possible that the reason I can't actually run any SQL statements serverside is that I wasn't given the correct permissions? I was told that I had the correct remote access/ODBC permissions but the freebcp procedure spat out: `Server ExServer, Line 1 The SELECT permission was denied on the object , database , schema 'dbo'.`
     – JoeC Jul 19 '14 at 22:06
  • It doesn't seem to fit with the error message, but you definitely should try to log on through a functional IDE, outside of the Unix environment. Through SQL Server Management Studio on a Windows server would make the most sense, of course. I assume you have an actual login and password. I don't think OS authentication is possible with unixODBC/TDS. – Hambone Jul 20 '14 at 00:20
  • @Hambone I think OS authentication is possible. You have to use the format "DOMAIN\username" in the -U section of the login string and FreeTDS will do the right thing with it. – Gregory Arenius Aug 27 '15 at 23:46