Error connecting to MSSQL

[mccarthyryanc]

Issue:
Error when trying to connect to MSSQL database:

Warning: QSqlDatabase: QODBC driver not loaded
Warning: QSqlDatabase: available drivers: QSQLITE

Steps to Reproduce:

1. MSSQL > New Connection ..
2. Filled in Host
3. Unchecked "Trusted connection"
4. Entered a username and password
5. Clicked List Databases and Test Connection, both result in error.

I tested the same host/creds using pymssql (freetds) from the QGIS python console and was able to connect and make queries.

Environment (conda list):

# packages in environment at /home/ubuntu/miniconda3/envs/qgis-env:
#
# Name                    Version                   Build  Channel
asn1crypto                0.24.0                py37_1003    conda-forge
atk                       2.25.90           hb9dd440_1002    conda-forge
attrs                     19.1.0                     py_0    conda-forge
boost-cpp                 1.68.0            h11c811c_1000    conda-forge
bzip2                     1.0.6             h14c3975_1002    conda-forge
ca-certificates           2019.3.9             hecc5488_0    conda-forge
cairo                     1.16.0            ha4e643d_1000    conda-forge
certifi                   2019.3.9                 py37_0    conda-forge
cffi                      1.12.3           py37h8022711_0    conda-forge
cfitsio                   3.430             h0bd2449_1002    conda-forge
chardet                   3.0.4                 py37_1003    conda-forge
coverage                  4.5.3            py37h14c3975_0    conda-forge
cryptography              2.7              py37h72c5cf5_0    conda-forge
curl                      7.64.1               hf8cf82a_0    conda-forge
dbus                      1.13.6               he372182_0    conda-forge
decorator                 4.4.0                      py_0    conda-forge
exiv2                     0.27.1               had08079_0    conda-forge
expat                     2.2.5             hf484d3e_1002    conda-forge
fontconfig                2.13.1            he4413a7_1000    conda-forge
freetds                   1.1rc3               h4fe99da_0    conda-forge
freetype                  2.10.0               he983fc9_0    conda-forge
freexl                    1.0.5             h14c3975_1002    conda-forge
future                    0.17.1                py37_1000    conda-forge
gdal                      2.4.1            py37h5f563d9_4    conda-forge
gdk-pixbuf                2.36.12           h49783d7_1002    conda-forge
geos                      3.7.1             hf484d3e_1000    conda-forge
geotiff                   1.5.1                h560c3f3_2    conda-forge
gettext                   0.19.8.1          hc5be6a0_1002    conda-forge
giflib                    5.1.7                h516909a_1    conda-forge
glib                      2.58.3            hf63aee3_1001    conda-forge
gobject-introspection     1.58.2          py37h2da5eee_1000    conda-forge
graphite2                 1.3.13            hf484d3e_1000    conda-forge
gsl                       2.4               h294904e_1006    conda-forge
gst-plugins-base          1.14.5               h0935bb2_0    conda-forge
gstreamer                 1.14.5               h36ae1b5_0    conda-forge
gtk2                      2.24.31           hb68c50a_1001    conda-forge
harfbuzz                  2.4.0                h37c48d4_0    conda-forge
hdf4                      4.2.13            h9a582f1_1002    conda-forge
hdf5                      1.10.5          nompi_h3c11f04_1100    conda-forge
httplib2                  0.13.0                   py37_0    conda-forge
icu                       58.2              hf484d3e_1000    conda-forge
idna                      2.8                   py37_1000    conda-forge
ipython_genutils          0.2.0                      py_1    conda-forge
jinja2                    2.10.1                     py_0    conda-forge
jpeg                      9c                h14c3975_1001    conda-forge
json-c                    0.13.1            h14c3975_1001    conda-forge
jsonschema                3.0.1                    py37_0    conda-forge
jupyter_core              4.4.0                      py_0    conda-forge
kealib                    1.4.10            h58c409b_1005    conda-forge
krb5                      1.16.3            h05b26f9_1001    conda-forge
libblas                   3.8.0                7_openblas    conda-forge
libcblas                  3.8.0                7_openblas    conda-forge
libcurl                   7.64.1               hda55be3_0    conda-forge
libdap4                   3.19.1            hd48c02d_1000    conda-forge
libedit                   3.1.20170329      hf8c457e_1001    conda-forge
libffi                    3.2.1             he1b5a44_1006    conda-forge
libgcc-ng                 9.1.0                hdf63c60_0  
libgdal                   2.4.1                h654d158_4    conda-forge
libgfortran-ng            7.3.0                hdf63c60_0  
libiconv                  1.15              h516909a_1005    conda-forge
libkml                    1.3.0             h328b03d_1009    conda-forge
liblapack                 3.8.0                7_openblas    conda-forge
libnetcdf                 4.6.2             h056eaf5_1002    conda-forge
libpng                    1.6.37               hed695b0_0    conda-forge
libpq                     11.3                 h4e4e079_0    conda-forge
libspatialindex           1.9.0                he1b5a44_1    conda-forge
libspatialite             4.3.0a            h4c5cd43_1028    conda-forge
libssh2                   1.8.2                h22169c7_2    conda-forge
libstdcxx-ng              9.1.0                hdf63c60_0  
libtiff                   4.0.10            h57b8799_1003    conda-forge
libuuid                   2.32.1            h14c3975_1000    conda-forge
libxcb                    1.13              h14c3975_1002    conda-forge
libxml2                   2.9.9                h13577e0_0    conda-forge
libxslt                   1.1.32            h4785a14_1002    conda-forge
libzip                    1.5.1             h1a52d62_1005    conda-forge
lxml                      4.3.4            py37h7ec2d77_0    conda-forge
lz4-c                     1.8.3             he1b5a44_1001    conda-forge
markupsafe                1.1.1            py37h14c3975_0    conda-forge
mock                      3.0.5                    py37_0    conda-forge
nbformat                  4.4.0                      py_1    conda-forge
ncurses                   6.1               hf484d3e_1002    conda-forge
nose2                     0.9.1                      py_0    conda-forge
numpy                     1.16.4           py37h95a1406_0    conda-forge
openblas                  0.3.5             h9ac9557_1001    conda-forge
openjpeg                  2.3.1                h58a6597_0    conda-forge
openssl                   1.1.1b               h14c3975_1    conda-forge
owslib                    0.17.1                     py_0    conda-forge
pango                     1.40.14           h4ea9474_1004    conda-forge
pcre                      8.41              hf484d3e_1003    conda-forge
pip                       19.1.1                   py37_0    conda-forge
pixman                    0.34.0            h14c3975_1003    conda-forge
plotly                    3.10.0                     py_0    conda-forge
poppler                   0.67.0               h2ad7f00_6    conda-forge
poppler-data              0.4.9                         1    conda-forge
postgresql                11.3                 hbeee2d4_0    conda-forge
proj4                     6.1.0                he751ad9_2    conda-forge
psycopg2                  2.8.2            py37h72c5cf5_0    conda-forge
pthread-stubs             0.4               h14c3975_1001    conda-forge
pycparser                 2.19                     py37_1    conda-forge
pygments                  2.4.2                      py_0    conda-forge
pymssql                   2.1.4           py37hf484d3e_2001    conda-forge
pyopenssl                 19.0.0                   py37_0    conda-forge
pyproj                    2.2.0            py37hc44880f_0    conda-forge
pyqt                      5.9.2            py37hcca6a23_0    conda-forge
pyrsistent                0.15.2           py37h516909a_0    conda-forge
pysocks                   1.7.0                    py37_0    conda-forge
python                    3.7.3                h5b0a415_0    conda-forge
python-dateutil           2.8.0                      py_0    conda-forge
pytz                      2019.1                     py_0    conda-forge
pyyaml                    5.1.1            py37h516909a_0    conda-forge
qca                       2.2.1                h279c94e_1    conda-forge
qgis                      3.6.3            py37h758d154_2    conda-forge
qjson                     0.9.0             h279c94e_1005    conda-forge
qscintilla2               2.11.1           py37hd2e5223_2    conda-forge
qt                        5.9.7                h52cfd70_2    conda-forge
qtkeychain                0.9.1             h279c94e_1001    conda-forge
qtlocation                5.9.7                he1b5a44_0    conda-forge
qtserialport              5.9.8                h279c94e_0    conda-forge
qwt                       6.1.4             h279c94e_1003    conda-forge
qwtpolar                  1.1.1                h279c94e_6    conda-forge
readline                  7.0               hf8c457e_1001    conda-forge
requests                  2.22.0                   py37_0    conda-forge
retrying                  1.3.3                      py_2    conda-forge
setuptools                41.0.1                   py37_0    conda-forge
sip                       4.19.8          py37hf484d3e_1000    conda-forge
six                       1.12.0                py37_1000    conda-forge
sqlite                    3.28.0               h8b20d00_0    conda-forge
tk                        8.6.9             hed695b0_1002    conda-forge
traitlets                 4.3.2                 py37_1000    conda-forge
tzcode                    2019a             h516909a_1002    conda-forge
unixodbc                  2.3.7             h227dcee_1000    conda-forge
urllib3                   1.24.3                   py37_0    conda-forge
wheel                     0.33.4                   py37_0    conda-forge
xerces-c                  3.2.2             hac72e42_1001    conda-forge
xorg-inputproto           2.3.2             h14c3975_1002    conda-forge
xorg-kbproto              1.0.7             h14c3975_1002    conda-forge
xorg-libice               1.0.9             h516909a_1004    conda-forge
xorg-libsm                1.2.3             h84519dc_1000    conda-forge
xorg-libx11               1.6.7             h14c3975_1000    conda-forge
xorg-libxau               1.0.9                h14c3975_0    conda-forge
xorg-libxdmcp             1.1.3                h516909a_0    conda-forge
xorg-libxext              1.3.4                h516909a_0    conda-forge
xorg-libxrender           0.9.10            h516909a_1002    conda-forge
xorg-libxt                1.1.5             h14c3975_1002    conda-forge
xorg-renderproto          0.11.1            h14c3975_1002    conda-forge
xorg-xextproto            7.3.0             h14c3975_1002    conda-forge
xorg-xproto               7.0.31            h14c3975_1007    conda-forge
xz                        5.2.4             h14c3975_1001    conda-forge
yaml                      0.1.7             h14c3975_1001    conda-forge
zlib                      1.2.11            h14c3975_1004    conda-forge
zstd                      1.4.0                h3b9ef0a_0    conda-forge

Details about conda and system ( conda info ):

     active environment : qgis-env
    active env location : /home/ubuntu/miniconda3/envs/qgis-env
            shell level : 2
       user config file : /home/ubuntu/.condarc
 populated config files : 
          conda version : 4.6.14
    conda-build version : not installed
         python version : 3.7.1.final.0
       base environment : /home/ubuntu/miniconda3  (writable)
           channel URLs : https://repo.anaconda.com/pkgs/main/linux-64
                          https://repo.anaconda.com/pkgs/main/noarch
                          https://repo.anaconda.com/pkgs/free/linux-64
                          https://repo.anaconda.com/pkgs/free/noarch
                          https://repo.anaconda.com/pkgs/r/linux-64
                          https://repo.anaconda.com/pkgs/r/noarch
          package cache : /home/ubuntu/miniconda3/pkgs
                          /home/ubuntu/.conda/pkgs
       envs directories : /home/ubuntu/miniconda3/envs
                          /home/ubuntu/.conda/envs
               platform : linux-64
             user-agent : conda/4.6.14 requests/2.19.1 CPython/3.7.1 Linux/4.15.0-50-generic ubuntu/18.04.2 glibc/2.27
                UID:GID : 1000:1000
             netrc file : None
           offline mode : False

[ceholden]

Hi @mccarthyryanc -- I haven't used MSSQL before so apologies if I'm incorrect, but I'm pretty sure what's going on here is that QGIS depends on Qt for the MSSQL support, and our build of Qt doesn't generate this component. Specifically, Qt uses the Open Database Connectivity (ODBC) driver for MSSQL connections which isn't in our Linux/Mac builds but is included on Windows.

According to the Qt docs, this driver is built by default on Windows but requires a 3rd party implementation of ODBC on Linux/Mac. They recommend unixODBC and we have a recipe for this package, so it may be possible to include. Looks like ODBC can be used as a backup for anything without a native driver, so it may be worth trying if it's not too difficult to build (perhaps as a separate package).

@ocefpaf any thoughts on if enabling the ODBC SQL driver for Linux/Windows would be welcome in the Qt recipe (adds unixodbc as dep), or if a separate feedstock for it would be better?

[ocefpaf]

@ocefpaf any thoughts on if enabling the ODBC SQL driver for Linux/Windows would be welcome in the Qt recipe (adds unixodbc as dep), or if a separate feedstock for it would be better?

I believe it is OK. Do you want to send a PR to the Qt feedstock?

[ocefpaf]

Also, if the Qt team rejects the idea we can create a variant for it. But let's keep that as a plan B.

[ceholden]

Thanks for the thoughts, @ocefpaf. Sounds like a plan. I should have some time to work on getting a PR to the Qt feedstock this weekend (assuming adding it in is straightforward...)

[ocefpaf]

Thanks for the thoughts, @ocefpaf. Sounds like a plan. I should have some time to work on getting a PR to the Qt feedstock this weekend

Ping me if you need anything.

(assuming adding it in is straightforward...)

Nothing that is Qt related is straightforward :-(

[ceholden]

Nothing that is Qt related is straightforward :-(

How true 😢... haven't made much headway beyond being able to build qt and have it fail the first test.

/home/conda/feedstock_root/build_artifacts/qt_1561481294095/test_tmp/run_test.sh: line 10:  4190 Segmentation fault      (core dumped) qmake hello.pro
Tests failed for qt-5.9.7-h52cfd70_2.tar.bz2 - moving package to /home/conda/feedstock_root/build_artifacts/broken
WARNING:conda_build.build:Tests failed for qt-5.9.7-h52cfd70_2.tar.bz2 - moving package to /home/conda/feedstock_root/build_artifacts/broken

Can't get it to pass this test whether I modify the recipe to include unixodbc or not. I suppose that the build got through building against the unixodbc stuff and failed just the same as unmodified is okay news. Will try to create a separate recipe

[mccarthyryanc]

@ceholden , it's been a while and I kind of lost track of this. Any chance you've had success in a separate recipe?

[SrNetoChan]

@mccarthyryanc can you try with a newer version of QGIS? maybe that was fixed (or not...)

[botanize]

@SrNetoChan as of QGIS 3.34.3 I'm still unable to connect to MSSQL databases. There's some work in the QGIS-Mac-Packager repo that might be helpful, but I don't really know anything about conda packaging.

https://github.com/qgis/QGIS-Mac-Packager/blob/7431116e89796a991f90ead2946a99057eab560f/qgis_deps/recipes/unixodbc/recipe.sh

[gillins]

Seems like this is an issue for qt-feedstock. Can someone raise an issue there?

[luispuerto]

Hey!

I'm also interested in this… have been any update? I can see that in the regular version of QGIS this is included, but not in the conda one… it's a pity.