HELP! Problems accessing a Microsoft SQL server database from Linux

Introduction

I need to prepare a complex report from a big table database. I’m running Linux Mint 18.3, the database resides on a Microsoft SQL Server. I have success with sqsh - I can run queries and I have even mirrored all tables to local CSV files, but I need to be able to access the database in a more user-friendly environment.
I have to explore table relationships and reverse-engineer the database structure to be able to produce that report.
As I’m learning SQL as I go, I don’t really feel I can get any further using just sqsh for my exploration.

ODBC

I managed to connect to the database with LibreOffice Base 5.1.6.2 - I can list the tables but trying to open them I get SQL syntax errors:

Maybe I should set some flags somewhere?

I have installed LibreOffice 6.0. Whe nI open the same database file I created with LibreOffice 5 - it prompts me for user and password, but then it fails to load the ODBC driver:

Even though it’s present in my system:

$ file '/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so'
/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so: ELF 64-bit LSB shared object, x86-64, version 1 (SYSV), dynamically linked, BuildID[sha1]=307e2b20ed1be01cab757c3ecd4d6e85f4a18b7b, stripped

I’ve tried creating a new database file to define the connection anew, but it fails the same way when I press “Test Connection” - so that’s not a thing with loading LO 5 file.

I’ve tried preloading that library:

$ LD_PRELOAD="/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so" libreoffice6.0 
ERROR: ld.so: object '/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so' from LD_PRELOAD cannot be preloaded (wrong ELF class: ELFCLASS64): ignored.

Maybe I should reinstall the ODBC driver?

JDBC

I tried using JDBC instead of ODBC to avoid this problem. From what I found Microsoft SQL Server has JDBC support since version 2008, so I should be able to use that driver.
However - I have no idea how to configure that. I got the JRE installed and the Microsoft JDBC driver .jre file added to class path. I’ve tried various things and dug on forums but didn’t have any success:

What should I put in these fields? I am really out of ideas for now.

Apache OpenOffice

I’ve tried the ODBC connection with another package, but it has the same behaviour. However, it showned me more detail about the problem:

The SQL command leading to this error is:

SELECT * FROM "DATABASE"."dbo"."A4B_IntTmpE_DBA_View"

I’ve tried this exact query with sqsh and it worked just fine. Not sure why LibreOffice chokes on that.

Bugs?

I have a feeling that what I am struggling with often is cause by some problems in the software, so I have filed two bug reports:

https://bugs.documentfoundation.org/show_bug.cgi?id=117798

https://bugs.documentfoundation.org/show_bug.cgi?id=117800

Finale

I’ve put many hours now into trying to solve this. I really need to make some progress finally. Any help will be greatly appreciated. Maybe I’m just doing something stupid here? Or maybe there are other open-source tools that I can use to explore an MSSQL database?

PS

It’s strange but I realized I can create Queries and Reports - Base lists all the keys in the Tables, but so far wasn’t able to evaluate any query without a syntax error near the database name. Maybe I should not prove the database name in my /etc/odbc.ini configuration file?

PPS

As suggested I’ve tried using ODBC Driver for Microsoft SQL Server for Linux.
This one to be precise:
https://packages.microsoft.com/ubuntu/17.10/prod/pool/main/m/msodbcsql17/msodbcsql17_17.1.0.1-1_amd64.deb

Unfortunately Libre Office 5 fails to read the driver library file. It works with the FreeTDS driver though. LibreOffice 6 fails with both drivers in the same way.

I’ve even tried running LibreOffice as Root, but that’s not making any difference.

In the image above - my ODBC configuration, error message form LibreOffice Base 5 and proof that the library file exists.

PPPS

LibreOffice 6 under Windows 10 works with no problems so far using Windows built-in ODBC configuration wizard, but I’d much prefer to have this work on Linux too.

Did you get this working?
There are other drivers to try.

Not yet. I’m now back to bang my head against this after the weekend.

Hello,

Don’t have access to your DB type but do know (from another question I dealt with) an ODBC connection does work.

However, from what you show & what I see in the MS connector for Ubuntu (Mint 18) you are using the wrong connector. From the Ubuntu 17.10 packages here → Download ODBC Driver for SQL Server, the driver is libmsodbcsql-17.

Sorry, but that is probably the most I can help with.

Edit:

Also found this which may be of some help → SQL Server on Linux: Using LibreOffice Base as a Client Application

Thanks! I’ve installed the driver you mentioned. However using that one - LibreOffice 5 says it can’t find the library file just like LibreOffice 6 does with the FreeTDS driver. With FreeTDS driver, LO 5 still partially works under Linux. On Windows 10 it just works with built-in ODBC sources manager. I’d like to avoid the overhead of using a virtual machine though.