cannot make the mssql framework to connect to the db - missing package error

[fnagy]

Hello,

I am trying to connect to a mssql database with the following code:

import ibis

ibis.options.interactive = True

con = ibis.connect('mssql://ipaddress')

but i get this error>

ImportError: Failed to import the mssql backend due to missing dependencies.

You can pip or conda install the mssql backend as follows:

  python -m pip install -U "ibis-framework[mssql]"  # pip install
  conda install -c conda-forge ibis-mssql           # or conda install

I have installed the ibis-framework[mssql] through poetry add. I have tried also the example with duckdb listed in the get started page and that runs without any issues.

How can i solve this?

[gforsyth]

Hey @fnagy --

The mssql backend uses pyodbc which requires installing an ODBC driver separately if you install using a Python-only package manager (i.e. something that isn't conda, mamba, pixi, or nix).

If you scroll up in the error message, you should see something about a failure to load the shared object file libodbc.so.2 (or similar).

If you are on OSX, you should be able to brew install unixodbc -- there's a similar package for Linux distributions, too. You can see the pyodbc install wiki for more information on how to install the non-Python dependency part of this: https://github.com/mkleehammer/pyodbc/wiki/Install

[fnagy]

Hi Gil,

thanks for your answer. I managed to make it work somehow, i.e. it was resulting me in a different error
pyodbc.InterfaceError: ('28000', "[28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'xxxx'. (18456) (SQLDriverConnect)") by installing unixodbc (as you suggested) and the SQL Server drivers.

My connection string looks like this:

con = ibis.mssql.connect(
    user="xxxxx",
    password="xxx",
    host="xxxx", # i have also tried with specifying the port 1433
    driver="{ODBC Driver 17 for SQL Server}", 
    # TrustServerCertificate="yes",
)

I have two mssql servers: one on premise, (v. 15.0.2110.4) and one hosted and managed by azure, and the issue is present only on the former. We assume is a driver problem after having troubleshooted a bunch of config options and openssl versions.

[gforsyth]

I think your diagnosis is probably correct -- sorry, it's very hard to help troubleshoot those sorts of issues remotely. If you get it working, we would really appreciate hearing back about what options were configured server-side and how you got it working from the Ibis / PyODBC side. We can't readily replicate different enterprise setups, so user reports are very valuable.

[fnagy]

Im already telling you we gave up :D it was planned to copy the mssql database onto a postgres instance, so I am going to wait just for it to happen. Thanks anyway for your support!

P.S. I was using an M1 MacbookPro, so i would have needed to jump through hoops to install drivers <17