Connect to MS SQL Server Database Using isql on Ubuntu

Question

Ultimate goal: connect my Xubuntu 16.04 machine to a remote MS SQL Server 2017 database using pyodbc.

Intermediate goal: get isql to work; tsql works, but not isql.

I tried mirroring the settings on my Mac - see this question for what worked. Unfortunately, no joy. I have looked through many SO questions, such as: One, Two, and Three, and tried their approaches, none of which work. I'm confident that the Linux machine has the right ports open, because tsql works from my Xubuntu 16.04 machine. Unfortunately, I cannot get isql to work. I get the dreaded

[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[01000][unixODBC][FreeTDS][SQL Server]Adaptive Server connection failed
[ISQL]ERROR: Could not SQLConnect

errors. Also unfortunately, much of the documentation I've seen seems woefully out-of-date and contradictory. For example:

In the odbc.ini file, should it be Server = stuff, 1433 or Server = stuff or ServerName = stuff with a separate port = line, or should I go with instance? There are too many options, and not enough documentation to figure this out.
What exactly should go in the odbcinst.ini file? I don't have a libtdsS.so file in /usr/lib/x86_64-linux-gnu/odbc directory. Should I leave out the Setup line?
What exactly should go in the freetds.conf file? Should I use the actual IP address?
If I want to force FreeTDS to use a particular protocol version, how do I do that? Changing freetds.conf doesn't appear to affect the results of tsql -C. Or do I just need to log out and back in?
Taking a step back: is there a better approach to the whole darn thing? I'm not interested in pymssql, as it appears to be abandonware at this point, in favor of pyodbc.
I note that the tsql -LH stuff command produces no output. Is that command deprecated? Or how could I get the right settings from any command?
Is there up-to-date, clear documentation available anywhere? Or an up-to-date, clear installation and usage guide anywhere, with trouble-shooting steps for the more common problems?

Many thanks for your time!

[EDIT] Here are the contents of a tracelog after enabling:

[ODBC][34479][1567549751.158470][__handles.c][460]
        Exit:[SQL_SUCCESS]
            Environment = 0x1913750
[ODBC][34479][1567549751.158517][SQLAllocHandle.c][377]
        Entry:
            Handle Type = 2
            Input Handle = 0x1913750
[ODBC][34479][1567549751.158532][SQLAllocHandle.c][493]
        Exit:[SQL_SUCCESS]
            Output Handle = 0x1914060
[ODBC][34479][1567549751.158551][SQLConnect.c][3721]
        Entry:
            Connection = 0x1914060
            Server Name = [ROEFDN806Q][length = 10 (SQL_NTS)]
            User Name = [mfad\m210028][length = 12 (SQL_NTS)]
            Authentication = [********][length = 8 (SQL_NTS)]
[ODBC][34479][1567549751.158987][SQLConnect.c][3929]Error: IM002
[ODBC][34479][1567549751.159017][SQLError.c][424]
        Entry:
            Connection = 0x1914060
            SQLState = 0x7ffd099daf10
            Native = 0x7ffd099daf0c
            Message Text = 0x7ffd099daf20
            Buffer Length = 500
            Text Len Ptr = 0x7ffd099daf0a
[ODBC][34479][1567549751.159034][SQLError.c][474]
        Exit:[SQL_SUCCESS]
            SQLState = IM002
            Native = 0x7ffd099daf0c -> 0
            Message Text = [[unixODBC][Driver Manager]Data source name not found and no default driver specified]
[ODBC][34479][1567549751.159066][SQLError.c][424]
        Entry:
            Connection = 0x1914060
            SQLState = 0x7ffd099daf10
            Native = 0x7ffd099daf0c
            Message Text = 0x7ffd099daf20
            Buffer Length = 500
            Text Len Ptr = 0x7ffd099daf0a
[ODBC][34479][1567549751.159079][SQLError.c][474]
        Exit:[SQL_NO_DATA]
[ODBC][34479][1567549751.159089][SQLError.c][424]
        Entry:
            Environment = 0x1913750
            SQLState = 0x7ffd099daf10
            Native = 0x7ffd099daf0c
            Message Text = 0x7ffd099daf20
            Buffer Length = 500
            Text Len Ptr = 0x7ffd099daf0a
[ODBC][34479][1567549751.159099][SQLError.c][474]
        Exit:[SQL_NO_DATA]
[ODBC][34479][1567549751.159130][SQLFreeHandle.c][290]
        Entry:
            Handle Type = 2
            Input Handle = 0x1914060
[ODBC][34479][1567549751.159144][SQLFreeHandle.c][339]
        Exit:[SQL_SUCCESS]
[ODBC][34479][1567549751.159156][SQLFreeHandle.c][220]
        Entry:
            Handle Type = 1
            Input Handle = 0x1913750

Here is my current odbc.ini file:

[ROEFDN806Q]
Description = "EOAM_RA Server"
Driver = FreeTDS
ServerName = ROEFDN806Q
Server = 10.146.186.7
Port = 1433
Database = EOAM_RA
UsageCount = 1
TDS_Version = 7.3

And here's my odbcinst.ini file:

[FreeTDS]
Description = "FreeTDS ODBC Driver"
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so

[ODBC]
Trace = yes
TraceFile = /tmp/odbctrace.txt

Recommendation #1: Use Microsoft's [ODBC Driver 17 for SQL Server](https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017) instead of FreeTDS ODBC. — Gord Thompson, Aug 30 '19 at 14:07
Have you seen [these instructions](https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQL-Server-from-Linux) from the pyodbc Wiki? — Gord Thompson, Aug 30 '19 at 16:40
I tried those instructions, and after I finally got every step to execute without errors, start to finish, I'm still getting errors connecting. In this case, I'm getting this error: `[28000][unixODBC][Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'domain\username'. [ISQL]Error: Could not SQLConnect.` Additional ideas? I know I can log into the server, because `tsql` still works. — Adrian Keister, Aug 30 '19 at 19:18
Oh, so you need to connect using Windows credentials, do you? — Gord Thompson, Aug 30 '19 at 19:54
I have Windows credentials, yes. I don't think I can use the Windows sign-on; I think I have to use SQL Authentication. By the way, my `isql` command is looking like this: `isql -v servername domain\\username password`. Is that correct? The `tsql` command that works is `tsql -S servername -U domain\\username -P password`, where `servername, domain, username,` and `password` are the same. — Adrian Keister, Aug 30 '19 at 19:57

score 3 · Accepted Answer · answered Aug 30 '19 at 20:27

3

Microsoft's "ODBC Driver 17 for SQL Server" is the preferred driver for connecting to SQL Server. However, if you need to pass Windows credentials from a Linux box to a SQL Server instance then you do in fact need to use FreeTDS, at least for now.

We get the required components via

sudo apt install tdsodbc unixodbc

Then we use sudo nano -w /etc/odbcinst.ini to create an entry like this:

[FreeTDS]
DESCRIPTION=FreeTDS ODBC driver
DRIVER=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so

Next we use sudo nano -w /etc/odbc.ini to create an entry like this:

[SQLServer01]
DRIVER=FreeTDS
SERVER=192.168.0.179
PORT=49242
DATABASE=myDb
TDS_Version=7.2

Finally, we can connect via isql (note the double backslash):

isql SQLServer01 mydomain\\myusername mypassword

answered Aug 30 '19 at 20:27

Gord Thompson

98,607
26
164
342

Doesn't work, yet, though +1. Questions: 1. What are the permissions on `/etc/odbc.ini`, `/etc/odbcinst.ini` and `usr/local/bin/isql`? 2. Who is the owner of those files? 3. Should there be a Setup line in `odbcinst.ini`? I do have a setup file in the right folder, next to the `.so` file you mentioned. – Adrian Keister Aug 30 '19 at 20:37
You can add the setup line to odbcinst.ini if you like, but I never bother with it. The files you mentioned should all be owned by root, with rw (and maybe x) permissions for root but no w for anybody else. – Gord Thompson Aug 30 '19 at 21:25
Hmm. Well, changing the permissions to octal `744`, which would give what you describe, doesn't help with the problem. Incidentally, I need the `isql` to work, most likely, without the `sudo` prefix. Any more ideas? Thanks for all your help, by the way! This is just a really difficult problem. I've been at it for over a week, now. – Adrian Keister Aug 30 '19 at 22:01
You definitely shouldn't need sudo to run isql. I'll verify the permissions once I get home and can remote back into the office. – Gord Thompson Aug 30 '19 at 22:09
/etc/odbcinst.ini and /etc/odbc.ini are both 644, /usr/bin/isql is 755 – Gord Thompson Aug 31 '19 at 02:24
So, those settings, unfortunately, still don't get the connection to happen. I know the target database is running on the default port 1433. Are there any other ports `isql` uses? Or do you have any other ideas as to why this isn't working? – Adrian Keister Sep 03 '19 at 20:32
You could [enable an ODBC trace](https://github.com/mkleehammer/pyodbc/wiki/Troubleshooting-%E2%80%93-Generating-an-ODBC-trace-log#linux) and see if anything in there gives a clue as to why the connection is failing. – Gord Thompson Sep 03 '19 at 22:15
I've added one login's worth of tracing to the question. Anything suspicious? – Adrian Keister Sep 03 '19 at 22:31
"Message Text = [[unixODBC][Driver Manager]Data source name not found and no default driver specified]" - isql either cannot find the DSN itself or can't find the driver associated with it. – Gord Thompson Sep 03 '19 at 22:34
I've been experimenting with lots of different solutions. Let me switch back in yours, and re-run. – Adrian Keister Sep 03 '19 at 22:35
Still getting the same error. What has to be true for `isql` to find the data source and the driver? I've got `[FreeTDS]` section in `odbcinst.ini`, and `DRIVER = FreeTDS` in my `odbc.ini` file. – Adrian Keister Sep 03 '19 at 23:02
Check the output from `odbcinst -j` to see the version of unixODBC and the locations of the config files. – Gord Thompson Sep 03 '19 at 23:26
I got `unixODBC 2.3.7` and `DRIVERS...: /etc/odbcinst.ini` and `SYSTEM DATA SOURCES: /etc/odbc.ini`. – Adrian Keister Sep 04 '19 at 14:23
1

Okay, that all looks right. So when you try `isql` does the ODBC trace still contain the "Data source name not found and no default driver specified" error? – Gord Thompson Sep 04 '19 at 15:08
Can you do a direct copy and paste into your question to show the contents of /etc/odbcinst.ini and /etc/ODBC.ini ...? – Gord Thompson Sep 04 '19 at 15:37
Done. I've been playing around with version numbers, and whether an `instance=` line is required, as you can see. All to no effect. – Adrian Keister Sep 04 '19 at 15:41
When I try `isql ROEFDN806Q myusername mypassword -v` I get "[S1000][unixODBC][FreeTDS][SQL Server]You cannot specify both SERVERNAME and SERVER". Try deleting the `SERVERNAME` line and try again. – Gord Thompson Sep 04 '19 at 15:56
On my Xubuntu 16.04 box I had to use `TDS_Version = 7.2`. 7.3 wouldn't work, presumably because the version of FreeTDS ODBC was too old. – Gord Thompson Sep 04 '19 at 16:14
Update: it's definitely SQL Server 2017 to which I'm trying to connect. – Adrian Keister Sep 04 '19 at 16:28
If you do `hexdump /etc/odbc.ini | head -n 1` do you get `0000000 525b 454f 4446 384e 3630 5d51 440a 7365` ? – Gord Thompson Sep 04 '19 at 17:13
I'll try that. I have to say, I think my virtual machine's state is unknown/compromised by now with everything I've tried. As soon as I get my other virtual machine powered on, I'm going to try a standard install procedure from Microsoft, and see if that works. – Adrian Keister Sep 04 '19 at 17:20
That is precisely what I get from the `hexdump`. – Adrian Keister Sep 04 '19 at 17:21
If you really do need to use FreeTDS ODBC (for NTLM authentication with Windows credentials) then it might be better to avoid installing msodbcsql17. The steps in my answer were done on a fresh Xubuntu 16.04 x64 virtual machine and they work fine for me. Good luck. – Gord Thompson Sep 04 '19 at 17:30
Thanks for all your help! I really appreciate it! I'll either go with your solution or the Microsoft procedure. – Adrian Keister Sep 04 '19 at 18:25
Just bear in mind that msodbcsql17 won't do NTLM authentication so if the SQL Server only supports Windows authentication then you'd need to use Kerberos to connect from Linux using msodbcsql17. – Gord Thompson Sep 04 '19 at 18:35
Finally got it to work, with the help of IT. Used Kerberos and the ODBC Driver 17 for SQL Server, not FreeTDS. – Adrian Keister Sep 19 '19 at 16:02

Connect to MS SQL Server Database Using isql on Ubuntu

1 Answers1