<!DOCTYPE html><html><head><title></title><style type="text/css">#qt p.qt-MsoNormal,#qt li.qt-MsoNormal{margin-top:0cm;margin-right:0cm;margin-bottom:0cm;margin-left:0cm;font-size:12pt;font-family:"Aptos", sans-serif;}
#qt a:link{color:blue;text-decoration-color:currentcolor;text-decoration-line:underline;text-decoration-style:solid;text-decoration-thickness:auto;}
#qt p.qt-qt-msonormal1{margin-top:0cm;margin-right:0cm;margin-bottom:0cm;margin-left:0cm;font-size:11pt;font-family:"Aptos", sans-serif;}
#qt ol{margin-bottom:0cm;}
p.MsoNormal,p.MsoNoSpacing{margin:0}</style></head><body><div><div>Hi Tim,<br></div><div><br></div><div>If ogrinfo is returning the same error, then it likely rules out MapServer as the issue. I'd probably troubleshoot along the following lines:<br></div><div><br></div><ol><li>Try ogrinfo without an OVF file and a direct connection string. Maybe there are permission issues around accessing the system DSN<br></li><li>If that works, switch to OVF with a connection string, then with a DSN.<br></li><li>If it doesn't work, then use SQL Server Profiler to see if the user is making any requests to the database at all. <br></li><li>Try recreating the DSN - is the password saved correctly?<br></li></ol><div>For the CREATE TABLE issue, I **think** you should be able to set that MSSQL environment variable by adding the following to your Mapfile:<br></div><div><br></div><div>CONFIG "MSSQLSPATIAL_USE_GEOMETRY_COLUMNS" "NO"<br></div><div><br></div><div>Seth<br></div><div><br></div><div class="signature">--<br></div><div class="signature">web:<a href="https://geographika.net">https://geographika.net</a> & <a href="https://mapserverstudio.net">https://mapserverstudio.net</a><br></div><div class="signature">mastodon: @<a href="mailto:geographika@mastodon.social">geographika@mastodon.social</a><br></div><div><br></div><div>On Thu, Sep 19, 2024, at 2:58 PM, Pascoe,Tim (ECCC) wrote:<br></div></div><blockquote type="cite" id="qt" style="overflow-wrap:break-word;"><div class="qt-WordSection1"><p class="qt-MsoNormal"><span class="size" style="font-size:11pt;">Seth,</span><br></p><p class="qt-MsoNormal"><span class="size" style="font-size:11pt;"> </span><br></p><p class="qt-MsoNormal"><span class="size" style="font-size:11pt;"> I can connect via OGRINFO using the MSSQL driver, after setting the environment variable for:</span><br></p><p class="qt-MsoNormal"><span class="size" style="font-size:11pt;"> </span><br></p><p class="qt-MsoNormal"><span class="size" style="font-size:11pt;">MSSQLSPATIAL_USE_GEOMETRY_COLUMNS=NO</span><br></p><p class="qt-MsoNormal"><span class="size" style="font-size:11pt;"> </span><br></p><p class="qt-MsoNormal"><span class="size" style="font-size:11pt;">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.</span><br></p><p class="qt-MsoNormal"><span class="size" style="font-size:11pt;"> </span><br></p><div><p class="qt-MsoNormal"><b><span lang="EN-CA" style="color:rgb(31, 73, 125);"><span class="font" style="font-family:"Trebuchet MS", sans-serif;"><span class="size" style="font-size:11pt;">Timothy Pascoe</span></span></span></b><b><span lang="EN-CA" style="color:rgb(31, 73, 125);"><span class="font" style="font-family:"Trebuchet MS", sans-serif;"></span></span></b><br></p><p class="qt-MsoNormal"><span lang="EN-CA" style="color:rgb(31, 73, 125);"><span class="font" style="font-family:"Trebuchet MS", sans-serif;"><span class="size" style="font-size:9pt;"> </span></span></span><br></p><p class="qt-MsoNormal"><span lang="EN-CA" style="color:red;"><span class="font" style="font-family:"Trebuchet MS", sans-serif;"><span class="size" style="font-size:9pt;">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. </span></span></span><span lang="FR-CA" style="color:red;"><span class="font" style="font-family:"Trebuchet MS", sans-serif;"><span class="size" style="font-size:9pt;">Mon adresse Canada.ca continuera également à fonctionner pour le moment.</span></span></span><br></p><p class="qt-MsoNormal"><span lang="FR-CA" style="color:rgb(31, 73, 125);"><span class="font" style="font-family:"Trebuchet MS", sans-serif;"><span class="size" style="font-size:9pt;"> </span></span></span><br></p><p class="qt-MsoNormal"><span lang="FR-CA" style="color:rgb(31, 73, 125);"><span class="font" style="font-family:"Trebuchet MS", sans-serif;"><span class="size" style="font-size:9pt;">Environmental Scientist, Water Quality Monitoring & Surveillance<br> Environment & Climate Change Canada (ECCC)</span></span></span></p><p class="qt-MsoNormal"><span lang="EN-CA" style="color:black;"><span class="font" style="font-family:"Verdana", sans-serif;"><span class="size" style="font-size:10pt;"><a href="mailto:tim.pascoe@ec.gc.ca"><span lang="FR-CA">tim.pascoe@ec.gc.ca</span></a></span></span></span><span lang="EN-CA" style="color:black;"><span class="font" style="font-family:"Verdana", sans-serif;"><span class="size" style="font-size:10pt;"> </span></span></span><span lang="FR-CA" style="color:black;"><span class="font" style="font-family:"Verdana", sans-serif;"><span class="size" style="font-size:10pt;">T: 905-336-6239 / C: 289-253-7857</span></span></span><br></p><p class="qt-MsoNormal"><span lang="FR-CA" style="color:black;"><span class="font" style="font-family:"Verdana", sans-serif;"><span class="size" style="font-size:10pt;"> </span></span></span><br></p><p class="qt-MsoNormal"><span lang="FR-CA" style="color:rgb(31, 73, 125);"><span class="font" style="font-family:"Trebuchet MS", sans-serif;"><span class="size" style="font-size:9pt;">Scientifique de l’environement, Surveillance de la qualité de l'eau</span></span></span><span lang="FR-CA" style="color:rgb(31, 73, 125);"><span class="font" style="font-family:"Calibri", sans-serif;"><span class="size" style="font-size:11pt;"></span></span></span><br></p><p class="qt-MsoNormal"><span lang="FR-CA" style="color:rgb(31, 73, 125);"><span class="font" style="font-family:"Trebuchet MS", sans-serif;"><span class="size" style="font-size:9pt;">Environnement et Changement climatique Canada (ECCC)</span></span></span><br></p><p class="qt-MsoNormal"><span lang="EN-CA" style="color:black;"><span class="font" style="font-family:"Verdana", sans-serif;"><span class="size" style="font-size:10pt;"><a href="mailto:tim.pascoe@ec.gc.ca"><span lang="FR-CA">tim.pascoe@ec.gc.ca</span></a></span></span></span><span lang="EN-CA" style="color:black;"><span class="font" style="font-family:"Verdana", sans-serif;"><span class="size" style="font-size:10pt;"> </span></span></span><span lang="FR-CA" style="color:black;"><span class="font" style="font-family:"Verdana", sans-serif;"><span class="size" style="font-size:10pt;">T: 905-336-6239 / C: 289-253-7857</span></span></span><br></p><p class="qt-MsoNormal"><span lang="FR-CA" style="color:black;"><span class="font" style="font-family:"Verdana", sans-serif;"><span class="size" style="font-size:10pt;"> </span></span></span><br></p><p class="qt-MsoNormal"><span lang="EN-CA" style="color:black;"><span class="size" style="font-size:11pt;"><img border="0" width="372" height="20" style="width:3.875in;height:0.2083in;" id="qt-Picture_x0020_1" src="cid:image001.gif@01DB0A72.25B6D450" alt="Environment Canada"></span></span><span lang="EN-CA" style="color:rgb(31, 73, 125);"><span class="font" style="font-family:"Calibri", sans-serif;"><span class="size" style="font-size:11pt;"></span></span></span><br></p></div><p class="qt-MsoNormal"><span class="size" style="font-size:11pt;"> </span><br></p><div><div style="border-right-width:medium;border-right-style:none;border-right-color:currentcolor;border-bottom-width:medium;border-bottom-style:none;border-bottom-color:currentcolor;border-left-width:medium;border-left-style:none;border-left-color:currentcolor;border-image-outset:0;border-image-repeat:stretch;border-image-slice:100%;border-image-source:none;border-image-width:1;border-top-width:1pt;border-top-style:solid;border-top-color:rgb(225, 225, 225);padding-top:3pt;padding-right:0cm;padding-bottom:0cm;padding-left:0cm;"><p class="qt-MsoNormal"><b><span class="font" style="font-family:"Calibri", sans-serif;"><span class="size" style="font-size:11pt;">From:</span></span></b><span class="font" style="font-family:"Calibri", sans-serif;"><span class="size" style="font-size:11pt;"> Seth G <sethg@geographika.co.uk> <br> <b>Sent:</b> Thursday, September 19, 2024 3:00 AM<br> <b>To:</b> Pascoe,Tim (ECCC) <Tim.Pascoe@ec.gc.ca>; MapServer Users <mapserver-users@lists.osgeo.org><br> <b>Subject:</b> Re: [MapServer-users] Issue with ODBC Connection on 8.2.2 for OGR VRT Layer</span></span></p></div></div><p class="qt-MsoNormal"> <br></p><table class="qt-MsoNormalTable" border="0" cellspacing="0" cellpadding="0" align="left" width="100%" style="width:100%;"><tbody><tr><td width="0" style="width:0.3pt;background-color:rgb(166, 166, 166);background-position-x:0%;background-position-y:0%;background-repeat:repeat;background-attachment:scroll;background-image:none;background-size:auto;background-origin:padding-box;background-clip:border-box;padding-top:5.25pt;padding-right:1.5pt;padding-bottom:5.25pt;padding-left:1.5pt;"><br></td><td width="100%" style="background-color:revert !important;background-position-x:revert !important;background-position-y:revert !important;background-repeat:revert !important;background-attachment:revert !important;background-image:revert !important;background-size:revert !important;background-origin:revert !important;background-clip:revert !important;border-top-width:revert !important;border-top-style:revert !important;border-top-color:revert !important;border-right-width:revert !important;border-right-style:revert !important;border-right-color:revert !important;border-bottom-width:revert !important;border-bottom-style:revert !important;border-bottom-color:revert !important;border-left-width:revert !important;border-left-style:revert !important;border-left-color:revert !important;border-image-outset:revert !important;border-image-repeat:revert !important;border-image-slice:revert !important;border-image-source:revert !important;border-image-width:revert !important;bottom:revert !important;color:revert !important;direction:revert !important;display:revert !important;font-size:revert !important;height:revert !important;letter-spacing:revert !important;line-height:revert !important;margin-top:revert !important;margin-right:revert !important;margin-bottom:revert !important;margin-left:revert !important;opacity:revert !important;order:revert !important;outline-color:revert !important;outline-style:revert !important;outline-width:revert !important;overflow-x:revert !important;overflow-y:revert !important;padding-top:revert !important;padding-right:revert !important;padding-bottom:revert !important;padding-left:revert !important;table-layout:revert !important;text-align:revert !important;text-indent:revert !important;text-orientation:revert !important;text-overflow:revert !important;text-shadow:revert !important;text-transform:revert !important;text-wrap-style:revert !important;top:revert !important;transition-property:revert !important;transition-duration:revert !important;transition-timing-function:revert !important;transition-delay:revert !important;transition-behavior:revert !important;vertical-align:revert !important;visibility:revert !important;text-wrap-mode:revert !important;width:revert !important;word-break:revert !important;word-spacing:revert !important;zoom:revert !important;"><div><p class="qt-MsoNormal" style=""><span style="color:rgb(33, 33, 33);"><span class="font" style="font-family:wf_segoe-ui_normal;"><span class="size" style="font-size:9pt;">You don't often get email from <a href="mailto:sethg@geographika.co.uk">sethg@geographika.co.uk</a>. <a href="https://aka.ms/LearnAboutSenderIdentification"> Learn why this is important</a></span></span></span><br></p></div></td><td width="75" style="background-color:revert !important;background-position-x:revert !important;background-position-y:revert !important;background-repeat:revert !important;background-attachment:revert !important;background-image:revert !important;background-size:revert !important;background-origin:revert !important;background-clip:revert !important;border-top-width:revert !important;border-top-style:revert !important;border-top-color:revert !important;border-right-width:revert !important;border-right-style:revert !important;border-right-color:revert !important;border-bottom-width:revert !important;border-bottom-style:revert !important;border-bottom-color:revert !important;border-left-width:revert !important;border-left-style:revert !important;border-left-color:revert !important;border-image-outset:revert !important;border-image-repeat:revert !important;border-image-slice:revert !important;border-image-source:revert !important;border-image-width:revert !important;bottom:revert !important;color:revert !important;direction:revert !important;display:revert !important;font-size:revert !important;height:revert !important;letter-spacing:revert !important;line-height:revert !important;margin-top:revert !important;margin-right:revert !important;margin-bottom:revert !important;margin-left:revert !important;opacity:revert !important;order:revert !important;outline-color:revert !important;outline-style:revert !important;outline-width:revert !important;overflow-x:revert !important;overflow-y:revert !important;padding-top:revert !important;padding-right:revert !important;padding-bottom:revert !important;padding-left:revert !important;table-layout:revert !important;text-align:revert !important;text-indent:revert !important;text-orientation:revert !important;text-overflow:revert !important;text-shadow:revert !important;text-transform:revert !important;text-wrap-style:revert !important;top:revert !important;transition-property:revert !important;transition-duration:revert !important;transition-timing-function:revert !important;transition-delay:revert !important;transition-behavior:revert !important;vertical-align:revert !important;visibility:revert !important;text-wrap-mode:revert !important;width:revert !important;word-break:revert !important;word-spacing:revert !important;zoom:revert !important;"><br></td></tr></tbody></table><div><div><p class="qt-MsoNormal">Hi Tim,<br></p></div><div><p class="qt-MsoNormal"> <br></p></div><div><p class="qt-MsoNormal">A couple of suggestions to try.<br></p></div><div><p class="qt-MsoNormal"> <br></p></div><ol start="1" type="1"><li class="qt-MsoNormal" style="">Can you connect with the same connection string details using ogrinfo?<br></li><li class="qt-MsoNormal" style="">Are you able to test with the MSSQL driver in GDAL? <a href="https://gdal.org/en/latest/drivers/vector/mssqlspatial.html"> https://gdal.org/en/latest/drivers/vector/mssqlspatial.html</a><br></li></ol><div><p class="qt-MsoNormal">Seth<br></p></div><div><p class="qt-MsoNormal"> <br></p></div><div id="qt-sig62266145"><div><p class="qt-MsoNormal">--<br></p></div><div><p class="qt-MsoNormal">web:<a href="https://geographika.net/">https://geographika.net</a> & <a href="https://mapserverstudio.net/">https://mapserverstudio.net</a><br></p></div><div><p class="qt-MsoNormal">twitter: @geographika<br></p></div></div><div><p class="qt-MsoNormal"> <br></p></div><div><p class="qt-MsoNormal">On Wed, Sep 18, 2024, at 11:12 PM, Pascoe, Tim (ECCC) via MapServer-users wrote:<br></p></div><blockquote style="margin-top:5pt;margin-bottom:5pt;overflow-wrap:break-word;" id="qt-qt"><div><p class="qt-qt-msonormal1">I’ve spent a fair amount of time trying to track down an ODBC connection issue after upgrading to Mapserver 8.2.2<br></p><p class="qt-qt-msonormal1"> <br></p><p class="qt-qt-msonormal1">Platform:<br></p><p class="qt-qt-msonormal1">Windows Server 2016<br></p><p class="qt-qt-msonormal1">IIS<br></p><p class="qt-qt-msonormal1">MS SQL Server 2016<br></p><p class="qt-qt-msonormal1">Mapserver 8.2.2 (binaries from GISInternals)<br></p><p class="qt-qt-msonormal1"> <br></p><p class="qt-qt-msonormal1">Creating an ODBC connection using the MS administrator works, and the connection test is successful.<br></p><p class="qt-qt-msonormal1">The DSN is a System DSN using the ODBC Driver 17 for SQL Server<br></p><p class="qt-qt-msonormal1">The user account for the connection is confirmed to have access to the SQL View being used for a VRT layer using OGR<br></p><p class="qt-qt-msonormal1"> <br></p><p class="qt-qt-msonormal1">Testing with OGRINFO on the following .ovf file:<br></p><p class="qt-qt-msonormal1"> <br></p><p class="qt-qt-msonormal1"><OGRVRTDataSource><br></p><p class="qt-qt-msonormal1"> <OGRVRTLayer name='CABINsite'><br></p><p class="qt-qt-msonormal1"> <SrcDataSource>ODBC:****/***** @Cabinservice,qryWMSInternal</SrcDataSource><br></p><p class="qt-qt-msonormal1"> <SrcSQL>SELECT * FROM qryWMSInternal WHERE sitedetailsid = ****</SrcSQL><br></p><p class="qt-qt-msonormal1"> <FID>sitedetailsid</FID><br></p><p class="qt-qt-msonormal1"> <GeometryType>wkbPoint</GeometryType><br></p><p class="qt-qt-msonormal1"> <LayerSRS>WGS84</LayerSRS><br></p><p class="qt-qt-msonormal1"> <GeometryField encoding='PointFromColumns' x='longitude' y='latitude'/><br></p><p class="qt-qt-msonormal1"> </OGRVRTLayer><br></p><p class="qt-qt-msonormal1"></OGRVRTDataSource><br></p><p class="qt-qt-msonormal1"> <br></p><p class="qt-qt-msonormal1">Generates the error “Failed to open datasource ‘ODBC:****/****…..”<br></p><p class="qt-qt-msonormal1"> <br></p><p class="qt-qt-msonormal1">The same test in my production environment, using Mapserver 7.x, connects to the VRT layer and reports as expected.<br></p><p class="qt-qt-msonormal1"> <br></p><p class="qt-qt-msonormal1">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?<br></p><p class="qt-qt-msonormal1"> <br></p><p class="qt-qt-msonormal1">Thanks,<br></p><p class="qt-qt-msonormal1"> <br></p><p class="qt-qt-msonormal1">Tim<br></p><p class="qt-qt-msonormal1"> <br></p></div><div><p class="qt-MsoNormal">_______________________________________________<br></p></div><div><p class="qt-MsoNormal">MapServer-users mailing list<br></p></div><div><p class="qt-MsoNormal"><a href="mailto:MapServer-users@lists.osgeo.org">MapServer-users@lists.osgeo.org</a><br></p></div><div><p class="qt-MsoNormal"><a href="https://lists.osgeo.org/mailman/listinfo/mapserver-users">https://lists.osgeo.org/mailman/listinfo/mapserver-users</a><br></p></div><div><p class="qt-MsoNormal"> <br></p></div></blockquote><div><p class="qt-MsoNormal"> <br></p></div></div></div></blockquote><div><br></div></body></html>