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: @[email protected] 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) > [email protected] 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) > [email protected] T: 905-336-6239 / C: 289-253-7857 > > Environment Canada > > *From:* Seth G <[email protected]> > *Sent:* Thursday, September 19, 2024 3:00 AM > *To:* Pascoe,Tim (ECCC) <[email protected]>; MapServer Users > <[email protected]> > *Subject:* Re: [MapServer-users] Issue with ODBC Connection on 8.2.2 for OGR > VRT Layer > > > You don't often get email from [email protected]. 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 >> [email protected] >> https://lists.osgeo.org/mailman/listinfo/mapserver-users >> >
_______________________________________________ MapServer-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/mapserver-users
