1

I am trying to connect to a SQL Server (MSSQL) database from QGIS on Ubuntu.

As per the following link, I have created an odbc.ini file and the driver as per -

https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/connection-string-keywords-and-data-source-names-dsns?view=sql-server-ver15 and https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15#ubuntu18

[TEST]

Driver = ODBC Driver 17 for SQL Server

# Server = [protocol:]server[,port]  

Server = tcp:xxx,1433

And then in QGIS I get enter image description here

I am able to connect to it from QGIS on a windows machine after configuring windows ODBC so I am quite certain that it's not a permission issue on the server/db but something wrong with the driver/connection string on the Ubuntu 20.4 machine.

I have also tried the solutions in https://gis.stackexchange.com/a/216422/2891.

UPDATE

I have tried the following solution now to install QGIS using docker? https://gis.stackexchange.com/a/333581/2891

and also reviewed numerous suggestions around creating ODBC links in Ubuntu 20.4 like https://help.interfaceware.com/kb/904 and http://guywyant.info/log/206/connecting-to-ms-sql-server-from-ubuntu/

Where I get a reponse using tsql/telnet but

isql -v MSSQL devuser 'devpass'

gives me

[IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified

I am able to connect to the database from the machine I am adding an ODBC connection to using Azure Data Studio and Beekeeper studio without any issues. It is just the ODBC connection and software that needs this like QGIS that doesn't work.

Does anyone have this running on their machine? happy to reward with points for a complete answer...

UPDATE 2 So after running the following I got the basic connection to work but am still not able to connect to QGIS. What can be done for this final piece of the puzzle?

~# sudo curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add
~# sudo curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list > /etc/apt/sources.list.d/mssql-release.list
~# sudo apt-get update
~# sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18
~# isql -v -k "DRIVER={ODBC Driver 18 for SQL Server};SERVER=tcp:111.111.111.11,1433;UID=Test;PWD=*****;Authentication=SqlPassword;TrustServerCertificate=Yes"
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * from [Test].[dbo].[Optic_Fibre]

enter image description here

7
  • 1
    can you connect using OGR/GDAL utilities like ogrinfo, using the odbc driver ~ gdal.org/drivers/vector/odbc.html
    – nmtoken
    Feb 24, 2022 at 16:24
  • I just tried using the process in https://gdal.org/drivers/vector/mssqlspatial.html but it doesn't work. I think it needs an odbc driver. The code I tried was ogrinfo -al "MSSQLSpatial:server=111.111.111.11;database=xxx;trusted_connection=no;UID=Test;PWD=T1234"
    – GeorgeC
    Feb 24, 2022 at 17:01
  • 1
    Possibly related ~ github.com/qgis/QGIS/issues/46486
    – nmtoken
    Feb 25, 2022 at 14:59
  • Possibly...looking at the MS windows install guide I linked to, it looks like Ubuntu 18+ is not supported but surely there must be some way of doing this. I have installed learn.microsoft.com/en-gb/sql/azure-data-studio/… and it connects fine. So surely people must be using QGIS on Ubuntu 20.4 to connect in to SQL server on windows?
    – GeorgeC
    Feb 26, 2022 at 4:15
  • In your screen captures, the name of the ODBC connection is in mixed case and it’s all caps in your config files. It’s been a while since I’ve used UnixODBC so I don’t remember if it’s case sensitive. I also noticed in your first attempt to use isql to debug the issue (isql -v MSSQL devuser 'devpass') you didn’t mention the ODBC data source in the command. Try specifying the DSN in the command.
    – S M
    Feb 27, 2022 at 7:55

1 Answer 1

3

The key point here is a missing dependency: libqt5sql5-odbc and/or libqt5sql5-tds.

Here a complete guide:

  1. Install ODBC and FreeTDS packages:
    sudo apt install unixodbc odbcinst freetds-bin tdsodbc
  1. Install additional packages required by QGIS for ODBC connection:
    sudo apt install libqt5sql5-odbc libqt5sql5-tds
  1. Install Microsoft SQL Server driver following the instructions on https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server

  2. Check the content of /etc/odbcinst.ini to make sure FreeTDS and MS SQL Server drivers are listed:

[FreeTDS]
Description=v0.91 with protocol v7.2
Driver=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so

[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.0.so.1.1
UsageCount=1
  1. Create a MS SQL Server datasource in /etc/odbc.ini according to example below:
[ODBC Data Sources]

MSSQLQGIS = ODBC Driver 18 for SQL Server

[MSSQLQGIS]

Driver=ODBC Driver 18 for SQL Server
Description=QGIS Sql Server
Server=tcp:<mssql host name/ip>,1433
TrustServerCertificate=Yes
  1. Make sure Driver and the value after datasource name match the name of driver listed in /etc/odbcinst.ini

  2. Test your connection with isql command template:

    isql -v <datasource> <user> <password>

e.g.:

    isql -v MSSQLQGIS jhon pwd321
  1. Rename, delete or backup files /usr/local/etc/odbc.ini and /usr/local/etc/odbcinst.ini

  2. Create symbolics links to odbc ini files:

    sudo ln -s /etc/odbcinst.ini /usr/local/etc/odbcinst.ini sudo ln -s /etc/odbc.ini /usr/local/etc/odbc.ini

  3. In GIS Desktop, add a MSSQL connection as the example bellow:

connection

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.