Cannot connect to SQL Server on QGis 3.34.3

[mmartinello]

What is the bug or the crash?

I upgraded my QGis Server from version 3.26.2 on Debian Bullseye to version 3.34.3 on Debian Bookworm. After this, I cannot connect to SQL Server no more, and I'm trying to understand why this happens.

I am getting this error in the Apache error log file:

QSqlDatabasePrivate::database: unable to open database: "[FreeTDS][SQL Server]Unable to connect to data source [FreeTDS][SQL Server]Login failed for user 'sqlserver_user'. QODBC3: Unable to connect"

If I try to add a new MS SQL Server Connection in QGis I get the following error:

Error opening connection: [FreeTDS][SQL Server]Unable to connect to data source [FreeTDS][SQL Server]Login failed for user 'testuser'. QODBC3: Unable to connect

If I try to connect using the Windows Authentication it works:

Please note that the project was working without any problem with both Windows and SQL Sever authentication on QGis Server 3.26.2 on Debian Bullseye before the upgrade.

What I understand is that QGis is using FreeTDS to connect to SQL Server, so I installed the Microsoft SQL Server ODBC drivers:

root@qgis-test-dev:~# dpkg -l|grep msodbcsql
ii  msodbcsql18                                   18.3.2.1-1                          amd64        ODBC Driver for Microsoft(R) SQL Server(R)

I configured them in /etc/odbcinst.ini:

root@qgis-test-dev:~# cat /etc/odbcinst.ini
[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.3.so.2.1
UsageCount=1

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

If I configure a new system DSN and tell QGis to use it, it works:

root@qgis-test-dev:~# cat /etc/odbc.ini
[ODBC Data Sources]
MSSQLQGIS = ODBC Driver 18 for SQL Server

[MSSQLQGIS]
Driver=ODBC Driver 18 for SQL Server
Description=QGIS Sql Server
Server=tcp:sqldev1,1433
TrustServerCertificate=Yes

But I don't want to use DSN and I would prefer to set the host into the connection properties in QGis, as I don't need to deal with the DSN system configuration which could be system dependent.

So I hacked ODBC trying to force it to use the Microsoft ODBC drivers as FreeTDS:

root@qgis-test-dev:~# cat /etc/odbcinst.ini
[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.3.so.2.1
UsageCount=1

[FreeTDS]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.3.so.2.1
UsageCount=1

and surprisingly I'm getting the following error:

Error opening connection: [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: [error:0A000086:SSL routines::certificate verify failed:self-signed certificate] [Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection. For solutions related to encryption errors, see https://go.microsoft.com/fwlink/?linkid=2226722 [Microsoft][ODBC Driver 18 for SQL Server]Invalid connection string attribute QODBC3: Unable to connect

In summary, after the upgrade I can make connection to SQL Server working only setting a system DSN and specifying it in QGis.

So, the question is: how I can connect to SQL Server using the SQL Server authentication on QGis 3.34.3? Is this a bug or am I making something wrong?

Steps to reproduce the issue

Install Microsoft ODBC Driver Try to configure a new MS SQL Server connection as in the previous description and screenshots.

Versions

QGIS version 3.34.3-Prizren QGIS code revision 47373234acd Qt version 5.15.8 Python version 3.11.2 GDAL/OGR version 3.6.2 PROJ version 9.1.1 EPSG Registry database version v10.076 (2022-08-31) GEOS version 3.11.1-CAPI-1.17.1 SQLite version 3.40.1 PostgreSQL client version 15.5 (Debian 15.5-0+deb12u1) SpatiaLite version 5.0.1 QWT version 6.1.4 QScintilla2 version 2.13.3 OS version Debian GNU/Linux 12 (bookworm)

Active Python plugins grassprovider 2.12.99 MetaSearch 0.3.6 processing 2.12.99 db_manager 0.1.20

Supported QGIS version

• [X] I'm running a supported QGIS version according to the roadmap.

New profile

• [X] I tried with a new QGIS profile

Additional context

No response

[carlospsig]

I have the same problem.

SO: Windows 10 QGIS 3.28.14 LTR SQL Server 12

I don't connect using QGIS and GDAL

[elpaso]

To be honest, this doesn't look like a QGIS bug but rather a configuration issue. AFAIK nothing has changed in how QGIS handles the ODBC connections to MSSQL.

[elpaso]

I just tested QGIS master on Linux Ubuntu 22.04.4 LTS and there is no issue.