[MapServer-users] Issue with ODBC Connection on 8.2.2 for OGR VRT Layer

[Seth G]

Hi Tim,

If ogrinfo is returning the same error, then it likely rules out MapServer as the issue. I'd probably troubleshoot along the following lines:

 1. Try ogrinfo without an OVF file and a direct connection string. Maybe there are permission issues around accessing the system DSN
 2. If that works, switch to OVF with a connection string, then with a DSN.
 3. If it doesn't work, then use SQL Server Profiler to see if the user is making any requests to the database at all. 
 4. Try recreating the DSN - is the password saved correctly?
For the CREATE TABLE issue, I **think** you should be able to set that MSSQL environment variable by adding the following to your Mapfile:

CONFIG "MSSQLSPATIAL_USE_GEOMETRY_COLUMNS" "NO"

Seth

--
web:https://geographika.net & https://mapserverstudio.net
mastodon: @geographika at mastodon.social

On Thu, Sep 19, 2024, at 2:58 PM, Pascoe,Tim (ECCC) wrote:
> Seth,
>  
>                 I can connect via OGRINFO using the MSSQL driver, after setting the environment variable for:
>  
> MSSQLSPATIAL_USE_GEOMETRY_COLUMNS=NO
>  
> Since the user account for this application does not have CREATE TABLE permissions. The layer is a VRT taken from a non-spatial SQL table (just lat/long columns). Sadly, when accessing the layer via MapServer through the web application using the full map file, the Create Table error persists.
>  
> *Timothy Pascoe***
>  
> Please note the return to our departmental E-mail address below. My Canada.ca address will also continue to work for the time being/Veuillez noter le retour à notre adresse électronique départementale ci-dessous. Mon adresse Canada.ca continuera également à fonctionner pour le moment.
>  
> Environmental Scientist, Water Quality Monitoring & Surveillance
> Environment & Climate Change Canada (ECCC)
> tim.pascoe at ec.gc.ca T: 905-336-6239 / C: 289-253-7857
>  
> Scientifique de l’environement, Surveillance de la qualité de l'eau
> Environnement et Changement climatique Canada (ECCC)
> tim.pascoe at ec.gc.ca T: 905-336-6239 / C: 289-253-7857
>  
> Environment Canada
>  
> *From:* Seth G <sethg at geographika.co.uk> 
> *Sent:* Thursday, September 19, 2024 3:00 AM
> *To:* Pascoe,Tim (ECCC) <Tim.Pascoe at ec.gc.ca>; MapServer Users <mapserver-users at lists.osgeo.org>
> *Subject:* Re: [MapServer-users] Issue with ODBC Connection on 8.2.2 for OGR VRT Layer
>  
> 
> You don't often get email from sethg at geographika.co.uk. Learn why this is important <https://aka.ms/LearnAboutSenderIdentification>
> 
> Hi Tim,
>  
> A couple of suggestions to try.
>  
>  1. Can you connect with the same connection string details using ogrinfo?
>  2. Are you able to test with the MSSQL driver in GDAL? https://gdal.org/en/latest/drivers/vector/mssqlspatial.html
> Seth
>  
> --
> web:https://geographika.net & https://mapserverstudio.net
> twitter: @geographika
>  
> On Wed, Sep 18, 2024, at 11:12 PM, Pascoe, Tim (ECCC) via MapServer-users wrote:
>> I’ve spent a fair amount of time trying to track down an ODBC connection issue after upgrading to Mapserver 8.2.2
>> 
>>  
>> 
>> Platform:
>> 
>> Windows Server 2016
>> 
>> IIS
>> 
>> MS SQL Server 2016
>> 
>> Mapserver 8.2.2 (binaries from GISInternals)
>> 
>>  
>> 
>> Creating an ODBC connection using the MS administrator works, and the connection test is successful.
>> 
>> The DSN is a System DSN using the ODBC Driver 17 for SQL Server
>> 
>> The user account for the connection is confirmed to have access to the SQL View being used for a VRT layer using OGR
>> 
>>  
>> 
>> Testing with OGRINFO on the following .ovf file:
>> 
>>  
>> 
>> <OGRVRTDataSource>
>> 
>>                                 <OGRVRTLayer name='CABINsite'>
>> 
>>                                                 <SrcDataSource>ODBC:****/***** @Cabinservice,qryWMSInternal</SrcDataSource>
>> 
>>                                                 <SrcSQL>SELECT * FROM qryWMSInternal WHERE sitedetailsid = ****</SrcSQL>
>> 
>>                                     <FID>sitedetailsid</FID>
>> 
>>                                                 <GeometryType>wkbPoint</GeometryType>
>> 
>>                                                 <LayerSRS>WGS84</LayerSRS>
>> 
>>                                 <GeometryField encoding='PointFromColumns' x='longitude' y='latitude'/>
>> 
>>                                 </OGRVRTLayer>
>> 
>> </OGRVRTDataSource>
>> 
>>  
>> 
>> Generates the error “Failed to open datasource ‘ODBC:****/****…..”
>> 
>>  
>> 
>> The same test in my production environment, using Mapserver 7.x,  connects to the VRT layer and reports as expected.
>> 
>>  
>> 
>> Anyone have something else I can look for? I’ve seen a number of posts in various forums with similar issues, but they all seem to resolve with an error in the connection string. Since I’m sure the format/credentials is correct, I’m not sure where else to look. Is there a change in how OGR/GDAL operates in the 8.2.2 binaries I have (security changes?) that would result in this issue?
>> 
>>  
>> 
>> Thanks,
>> 
>>  
>> 
>> Tim
>> 
>>  
>> 
>> _______________________________________________
>> MapServer-users mailing list
>> MapServer-users at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/mapserver-users
>>  
>  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20240919/7db762e4/attachment.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.gif
Type: image/gif
Size: 2185 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20240919/7db762e4/attachment.gif>