Can't connect to ODBC system DSN

[CommunyAS]

What is the bug or the crash?

I want to connect to my FileMaker database using ODBC. The problems seem to be that QGIS is unable to connect to System DSN but only to User DSN.

I discovered this problem/solution earlier - see: QGIS not connecting to ODBC source anymore #40234.

However, I will have to connect to databases (primarily FileMaker, but others too) that are configured as System DSN in ODBC Manager (see screenshot). But when I want to establish such a connection I get this error, although everything is set up correctly (and work in other apps):

Connection failed - Check settings and try again. Extended error information: Unable to initialize ODBC connection to DSN for Admin@MatKomp_EneoFbzMbz,

Is this a bug in QGIS? or How can I tell QGIS to connect to System DSN?

Steps to reproduce the issue

1. Click on 'Add Vector Layer...'
2. under Source Type select Database
3. under Database select ODBC
4. create new connection with all specs Name: CONNECTIONNAME, Host: localhost, Database: DATABASENAME, Port: leave empty
5. click on 'Test Connection'

Versions

QGIS version	3.16.14-Hannover	QGIS code revision	4279198632
Compiled against Qt	5.14.2	Running against Qt	5.14.2
Compiled against GDAL/OGR	3.2.1	Running against GDAL/OGR	3.2.1
Compiled against GEOS	3.9.1-CAPI-1.14.2	Running against GEOS	3.9.1-CAPI-1.14.2
Compiled against SQLite	3.31.1	Running against SQLite	3.31.1
PostgreSQL Client Version	12.3	SpatiaLite Version	4.3.0a
QWT Version	6.1.4	QScintilla2 Version	2.11.4
Compiled against PROJ	6.3.2	Running against PROJ	Rel. 6.3.2, May 1st, 2020
OS Version	macOS 12.0

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

[gioman]

@CommunyAS it worked on Windows with a system connection to a DBF file. Seems complicated to test on macOS: I installed ODBC manager but it comes with no drivers and all drivers I found are paid and/or have trials that need a credential to download.

[CommunyAS]

@gioman Thanks for testing. I work a lot with Claris FileMaker, you can get an test license from their website. And you can download their ODBC drivers here.

Former QGIS versions worked with System DNS on Mac, the last one I used was QGIS 3.4.10.

Unfortunately, at least at the moment it is impossible to create User DNS with ODBC Manager (or iODBC) for whatever reason. However, it certainly will be very good to be able to read ODBC in QGIS using System DNS.

[gioman]

hanks for testing. I work a lot with Claris FileMaker, you can get an test license from their website. And you can download their ODBC drivers here.

@CommunyAS can you provide also a sample datasource?

[CommunyAS]

TestDB.fmp12.zip

[CommunyAS]

I just found a workaround to circumvent my specific problem:

Manually copy both files odbc.iniand odbcinst.ini

• from /Library/ODBC/
• to /Users/USERNAME/Library/ODBC/

and/or edit the specs for each connection manually in /Users/USERNAME/Library/ODBC/odbc.ini.

This will create the necessary User DSN connection that QGIS needs.

However, it certainly will be very good to be able to read ODBC in QGIS using System DSN, since this would enable several users on one PC use the same connections.

[gioman]

TestDB.fmp12.zip

@CommunyAS when creating the ODBC connection, how the field "database" must be filled?

it seems to look for an already registered db, not a file.

[CommunyAS]

First you have to enable ODBC sharing in FileMaker: File > Sharing > Enable ODBC/JDBC then turn sharing on.

The DB you want to connect to has to be running in FileMaker.

When creating the connection in ODBC Manager on the third screen of the FileMaker DSN Configuration dialog enable Connect to host to obtain the names of available databases. On the next screen you will be able to choose between database open in FileMaker.

[gioman]

@CommunyAS can I download filemaker freely?

[gioman]

I see there is a trial, but they want a registration which I'm not keen to do.

[CommunyAS]

Yes, you can get a trial version on https://www.claris.com/trial/

As far as I know you will get a download link then - you don't have to do a proper registration.

[gioman]

@CommunyAS it was a bit of a fight but I was able to test on Windows, and works as expected. Tomorrow I will test on macOS.

[gioman]

@CommunyAS seems really a macOS only issue.

[CommunyAS]

@gioman thanks for testing. I can live with the workaround, but generally I still think that the problem should be fixed on macOS, since reliance on System DSN becomes ever more important

[MargheNiso]

I just found a workaround to circumvent my specific problem:

Manually copy both files odbc.iniand odbcinst.ini

* from `/Library/ODBC/`

* to `/Users/USERNAME/Library/ODBC/`

and/or edit the specs for each connection manually in /Users/USERNAME/Library/ODBC/odbc.ini.

Hi, I'm trying to do the same thing and I've the same issue. Can you please explain me where I can find "/Library/ODBC/", "/Users/USERNAME/Library/ODBC/" and "/Users/USERNAME/Library/ODBC/odbc.ini". I don't understand if is some pc settings or QGIS settings. Thanks

[CommunyAS]

On macOS, these are the folders where the files, that contain the ODBC connection information, are stored. This workaround is only for Mac, it doesn’t work on WIN.

On 7. Aug 2022, 09:49 +0200, MargheNiso @.***>, wrote:

I just found a workaround to circumvent my specific problem: Manually copy both files odbc.iniand odbcinst.ini

• from /Library/ODBC/

• to /Users/USERNAME/Library/ODBC/ and/or edit the specs for each connection manually in /Users/USERNAME/Library/ODBC/odbc.ini. Hi, I'm trying to do the same thing and I've the same issue. Can you please explain me where I can find "/Library/ODBC/", "/Users/USERNAME/Library/ODBC/" and "/Users/USERNAME/Library/ODBC/odbc.ini". I don't understand if is some pc settings or QGIS settings. Thanks — Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you were mentioned.Message ID: @.***>

[MargheNiso]

@CommunyAS it was a bit of a fight but I was able to test on Windows, and works as expected. Tomorrow I will test on macOS.

@CommunyAS ok, now I understood why couldn't find them, 'cause I' m working on Windows. @gioman How could you be able to test it? I've already followed all steps, it works on filemaker, so it's not a FM issue, but when I try to test connection on qgis comes out the following issue: "Unable to initialize ODBC connection to DSN" and I don't know how to solve this

[gioman]

@gioman How could you be able to test it? I've already followed all steps, it works on filemaker, so it's not a FM issue, but when I try to test connection on qgis comes out the following issue: "Unable to initialize ODBC connection to DSN" and I don't know how to solve this

@MargheNiso it was months ago, I don't remember.

[AtmaMani]

I am hitting the same issue on QGIS 3.28 on Mac when trying to connect to Snowflake. It would be good to have this resolved. I have tried copying the DSN to User from System. I used the Snowflake doc to test both system and user DSNs. But I still cannot connect using QGIS

[ognjenzanskar]

@AtmaMani did you manage to make it work with Snowflake? If so, how? I also tried both system and user DSN, w/o any luck. It doesn't seem like QGIS even attempts the connection.

[jmak123]

Same here, I set up the DSN properly and can run in terminal but QGIS just doesn't see nor is able to use it.

[btamadio]

I'm having the same problem on MacOS. I configured and tested the DSN successfully with iODBCAdministrator, but QGIS gives me the same error as reported in this Issue. Using system or user DSN doesn't seem to make any difference.

[rafaelgoncalvs]

I'm facing the same problem trying to connect QGIS to Snowflake on MacOS. I configured the DSN for system and user DSN and I can successfully connect to Snowflake running iODBC commands (iodbctest). However, when I tried to connect from QGIS using the same DSN it always return: Extended error information: Unable to initialize ODBC connection to DSN for MY_USER/MY_PASS@DSN_NAME