search

MaxRev-Dev / gdal.netcore

GDAL 3.x C#/F# bindings for .NET apps
MIT License
158 stars 36 forks source link

[BUG] MSSQL Unable to initialize connection to the server #133

Closed bjornharrtell closed 5 months ago

bjornharrtell commented 5 months ago

Describe the bug

Ogr.Open with a valid MSSQL data source results in:

ERROR 1: Unable to initialize connection to the server

To Reproduce

https://github.com/bjornharrtell/gdal.netcore-issue-133 has full reproduction and CI setup with reproduction run.

Expected behavior

Functional MSSQL format support.

Environment information:

bjornharrtell commented 5 months ago

Relevant output from reproduction CI run which first runs with the same connection string with ogrinfo that succeeds to connect then with a simple console app which fails even though MSSQLSpatial is listed as supported driver.

INFO: Open of `MSSQL:server=localhost;database=TestDB;uid=sa;***;driver={ODBC Driver 17 for SQL Server}'
      using driver `MSSQLSpatial' successful.
Hello, World!
OGR Vector Drivers: 80
"AAIGrid","ACE2","ADRG","AIG","AirSAR","AmigoCloud","ARG","AVCBin","AVCE00","BAG","BIGGIF","BLX","BMP","BSB","BT","BYN","CAD","CALS","Carto","CEOS","COASP","COG","COSAR","CPG","CSV","CSW","CTable2","CTG","DAAS","DERIVED","DGN","DIMAP","DIPEx","DOQ1","DOQ2","DTED","DXF","ECRGTOC","EDIGEO","EEDA","EEDAI","EHdr","EIR","ELAS","Elasticsearch","ENVI","ERS","ESAT","ESRI Shapefile","ESRIC","ESRIJSON","FAST","FIT","FITS","FlatGeobuf","GenBin","Geoconcept","GeoJSON","GeoJSONSeq","GeoRSS","GFF","GIF","GML","GMLAS","GNMDatabase","GNMFile","GPKG","GPSBabel","GPX","GRASSASCIIGrid","GRIB","GS7BG","GSAG","GSBG","GSC","GTFS","GTiff","GTX","GXF","HDF[4](https://github.com/bjornharrtell/gdal.netcore-issue-133/actions/runs/9357544961/job/25757508711#step:8:4)","HDF[4](https://github.com/bjornharrtell/gdal.netcore-issue-133/actions/runs/9357544961/job/25757508711#step:8:5)Image","HDF5","HDF5Image","HF2","HFA","HTTP","Idrisi","ILWIS","Interlis 1","Interlis 2","IRIS","ISCE","ISG","ISIS2","ISIS3","JAXAPALSAR","JDEM","JML","JP2OpenJPEG","JPEG","JPEGXL","JSONFG","KML","KMLSUPEROVERLAY","KRO","L1B","LAN","LCP","Leveller","LIBKML","LOSLAS","LVBAG","MAP","MapInfo File","MapML","MBTiles","MEM","Memory","MFF","MFF2","MRF","MSGN","MSSQLSpatial","MVT","
ERROR 1: Unable to initialize connection to the server for MSSQL:server=localhost;database=TestDB;uid=sa;***;driver={ODBC Driver 1[7](https://github.com/bjornharrtell/gdal.netcore-issue-133/actions/runs/9357544961/job/25757508711#step:8:8) for SQL Server},
Try specifying the driver in the connection string from the list of available drivers:
Unhandled exception. System.ApplicationException: Unable to initialize connection to the server for MSSQL:server=localhost;database=TestDB;uid=sa;***;driver={ODBC Driver 17 for SQL Server},
Try specifying the driver in the connection string from the list of available drivers:
   at OSGeo.OGR.Ogr.Open(String utf[8](https://github.com/bjornharrtell/gdal.netcore-issue-133/actions/runs/9357544961/job/25757508711#step:8:9)_path, Int32 update)
   at Program.<Main>$(String[] args) in /app/Program.cs:line 19
Error: Process completed with exit code 134.

Full run at https://github.com/bjornharrtell/gdal.netcore-issue-133/actions/runs/9357544961/job/25757508711.

bjornharrtell commented 5 months ago

Might be relevant to note that I've had this working in the past with a custom compilation of gdal.netcore (due to that I had to include FileGDB at that time) based on 3.3.3.116 which wasn't published. So I tried downgrade to 3.3.3.120 in https://github.com/bjornharrtell/gdal.netcore-issue-133/pull/1 and it does seem to work. So looks like this regressed at some point.

bjornharrtell commented 5 months ago

Might also be relevant that I can reproduce from 3.6.4.200. Versions before that fail due to other reasons. See https://github.com/bjornharrtell/gdal.netcore-issue-133/pulls.

bjornharrtell commented 5 months ago

Closest versions as tags I can find to produce a diff is https://github.com/MaxRev-Dev/gdal.netcore/compare/v3.3.3.110...v3.6.4.100 which is (expectedly) not a trivial changeset.

bjornharrtell commented 5 months ago

The only thing I can find as of yet is https://github.com/MaxRev-Dev/gdal.netcore/compare/v3.3.3.110...v3.6.4.100#diff-22dfb81b9fcba330f9e547b46df3f36b07f2bd1016c57509cc0b0e0101282cf7R229 but it seems to be about the windows build.

MaxRev-Dev commented 5 months ago

Appreciate the efforts in investigating this issue and creating an excellent repro.

Apparently, there was a big jump from 3.3.3 to 3.6.0, mainly related to the GDAL build system moving from configure to CMake. Also, I have restructured the repo, removing the SWIG interface. The 3.3 version was built on CentOS7 with odbc pulling from APT packages.

I assume there is an issue with unix-odbc coming from system packages versus the VCPKG one. Or the internal problem of ODBC driver. I'm not sure here, because all those releases were compiled without any linker issues. There's always a linker problem when we have invalid library binaries or even different versions.

I'm going to release the new version soon. Once I figure out what to do with EXR driver missing only on osx-arm64 (locally it exists and binds). So I hope it will be possible to test on 3.9.0 in CI.

Not sure if it worth investigating on older releases and doing a backport for them. The easiest way would be to recompile the current version with different drivers. And it will be possible to check in CI - we could move your test setup somewhere here if you don't mind.

bjornharrtell commented 5 months ago

Thanks for your efforts @MaxRev-Dev. I'll be more than happy if we can resolve this on just the latest or next upcoming release, no reason to patch any old release (at least for me). Feel free to use the reproduction in any way you like and let me know if there is anything more I can do to help out.

MaxRev-Dev commented 5 months ago

@bjornharrtell the new release was published! Please, update your repo

bjornharrtell commented 5 months ago

@MaxRev-Dev done at https://github.com/bjornharrtell/gdal.netcore-issue-133/pull/6, problem is still there looks like unfortunately.

MaxRev-Dev commented 5 months ago

@bjornharrtell I was debugging for an hour or two and localized the issue.

So far, there is no issues with bindings. The library just doesn't know about the installed ODBC driver. By default, ODBC is looking for these two configuration files:

If the runtime says there is no drivers ( list of available drivers: <nothing>) then you need to set the ODBCSYSINST.

Here's the repro. https://github.com/MaxRev-Dev/gdal.netcore-issue-133/pull/1

Linux

All you need is to set ODBCSYSINST environment variable to point to your odbcinst.ini configuration. The default locations are /etc or user home directory.

On MacOS:

The path is ODBCSYSINST=/opt/homebrew/etc. ODBC driver installation

If the driver is available, but the connection still fails it should be an issue with the driver or connection string. I tried to do the same on MacOS and it needs some additional setup or installation. It failed the same with ogrinfo app and pyodbc on Python. When I ran sqlcmd -S localhost,1433 it gives me SSPI Provider: The operation or option is not available.. Hence, no luck on this for me. But it works on linux, tho.

Regarding if theODBCSYSINST should be set on configuration step:

There are several paths and different directories where it can be located (or not). Also, this driver is not a part of bindings, so there is no point to set this variable explicitly.

bjornharrtell commented 5 months ago

@MaxRev-Dev nice catch, confirmed. I wonder why it worked in 3.3.3.120 without setting ODBCSYSINST though... and any binary GDAL dist ogr2ogr. But I don't need to know. :D

Sorry for pulling you down this rabbit hole.

bjornharrtell commented 2 months ago

For the record it was env var ODBCSYSINI set to /etc that worked for me in linux env...

MaxRev-Dev commented 2 months ago

The default locations are /etc or user home directory.

Maybe it's not configured to point to /etc by default. Not sure about the defaults in this case. It can be set to this path, but only for linux so that should be handled as a workaround. Anyways, this should be configured manually by setting this env variable in user app.

bjornharrtell commented 2 months ago

Agree, it was just reminder to myself for ODBCSYSINST vs ODBCSYSINI.