<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:p="urn:schemas-microsoft-com:office:powerpoint" xmlns:a="urn:schemas-microsoft-com:office:access" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema" xmlns:b="urn:schemas-microsoft-com:office:publisher" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet" xmlns:odc="urn:schemas-microsoft-com:office:odc" xmlns:oa="urn:schemas-microsoft-com:office:activation" xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:q="http://schemas.xmlsoap.org/soap/envelope/" xmlns:rtc="http://microsoft.com/officenet/conferencing" xmlns:D="DAV:" xmlns:Repl="http://schemas.microsoft.com/repl/" xmlns:mt="http://schemas.microsoft.com/sharepoint/soap/meetings/" xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml" xmlns:ppda="http://www.passport.com/NameSpace.xsd" xmlns:ois="http://schemas.microsoft.com/sharepoint/soap/ois/" xmlns:dir="http://schemas.microsoft.com/sharepoint/soap/directory/" xmlns:ds="http://www.w3.org/2000/09/xmldsig#" xmlns:dsp="http://schemas.microsoft.com/sharepoint/dsp" xmlns:udc="http://schemas.microsoft.com/data/udc" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sub="http://schemas.microsoft.com/sharepoint/soap/2002/1/alerts/" xmlns:ec="http://www.w3.org/2001/04/xmlenc#" xmlns:sp="http://schemas.microsoft.com/sharepoint/" xmlns:sps="http://schemas.microsoft.com/sharepoint/soap/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:udcs="http://schemas.microsoft.com/data/udc/soap" xmlns:udcxf="http://schemas.microsoft.com/data/udc/xmlfile" xmlns:udcp2p="http://schemas.microsoft.com/data/udc/parttopart" xmlns:wf="http://schemas.microsoft.com/sharepoint/soap/workflow/" xmlns:dsss="http://schemas.microsoft.com/office/2006/digsig-setup" xmlns:dssi="http://schemas.microsoft.com/office/2006/digsig" xmlns:mdssi="http://schemas.openxmlformats.org/package/2006/digital-signature" xmlns:mver="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns:mrels="http://schemas.openxmlformats.org/package/2006/relationships" xmlns:spwp="http://microsoft.com/sharepoint/webpartpages" xmlns:ex12t="http://schemas.microsoft.com/exchange/services/2006/types" xmlns:ex12m="http://schemas.microsoft.com/exchange/services/2006/messages" xmlns:pptsl="http://schemas.microsoft.com/sharepoint/soap/SlideLibrary/" xmlns:spsl="http://microsoft.com/webservices/SharePointPortalServer/PublishedLinksService" xmlns:Z="urn:schemas-microsoft-com:" xmlns:st="" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 12 (filtered medium)">
<style>
<!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.EmailStyle18
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.EmailStyle19
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.EmailStyle20
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.EmailStyle21
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.EmailStyle22
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.Section1
{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><span style='color:#1F497D'>Well… A couple weeks and many
testing scenarios later, we have figured out what was happening in this
instance of connecting to SQL Server through ODBC. So I thought I would
post my findings in the event that it might help somebody at some other time.<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'>The SQL Server username password
that we were using contained ‘special characters’ like &, @, !,
and MapGuide apparently could not pass these properly. Remember, the
original error message thrown was: <o:p></o:p></span></p>
<p class=MsoNormal style='text-indent:.5in'><span style='color:#1F497D'>response
returned status code 502 (BadGateway)<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'>It wasn’t until we tested
on a completely localized environment that a more meaningful, only somewhat cryptic
error message was thrown: <o:p></o:p></span></p>
<p class=MsoNormal style='text-indent:.5in'><span style='color:#1F497D'>Feature
Source - Unterminated entity reference, 'Bob'<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'>‘Bob’ happened to be
part of the SQL Server password we were using, and more importantly, it was
preceded by an ‘&’ character. I can only assume that MapGuide
could not properly pass the password.<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'>Warren M</span><b><span
style='font-size:9.0pt;font-family:"Tahoma","sans-serif";color:black'><o:p></o:p></span></b></p>
<p class=MsoNormal><span style='font-size:9.0pt;font-family:"Arial","sans-serif";
color:black'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in'>
<p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span
style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>
mapguide-users-bounces@lists.osgeo.org
[mailto:mapguide-users-bounces@lists.osgeo.org] <b>On Behalf Of </b>Warren
Medernach<br>
<b>Sent:</b> Tuesday, March 16, 2010 10:18 AM<br>
<b>To:</b> MapGuide Users Mail List<br>
<b>Subject:</b> RE: [mapguide-users] RE: MapGuide connection to SQL Server
throughODBC<o:p></o:p></span></p>
</div>
</div>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal><span style='color:#1F497D'>Unfortunately, this is not what
we are finding.<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'>The DSN connection works fine
from the MG server, but Studio (on a clients desktop) is still failing when
attempting to connect through that same DSN. There is an ISA server in
the mix here and it seems to be blocking the request from MapGuide. Still
working to resolve…<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'>Warren M</span><b><span
style='font-size:9.0pt;font-family:"Tahoma","sans-serif";color:black'><o:p></o:p></span></b></p>
<p class=MsoNormal><span style='font-size:9.0pt;font-family:"Arial","sans-serif";
color:black'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in'>
<p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span
style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>
mapguide-users-bounces@lists.osgeo.org
[mailto:mapguide-users-bounces@lists.osgeo.org] <b>On Behalf Of </b>Dave Wilson<br>
<b>Sent:</b> Monday, March 15, 2010 3:40 PM<br>
<b>To:</b> MapGuide Users Mail List<br>
<b>Subject:</b> RE: [mapguide-users] RE: MapGuide connection to SQL Server
throughODBC<o:p></o:p></span></p>
</div>
</div>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal><span style='color:#1F497D'>The issue I described would
prevent you from testing the ODBC connection within the ODBC Database
Administrator in Windows. Basically if this connection works then using the DSN
in Studio should work. If you are trying a connection string that might be a
different problem. The DSN definitely works.<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'>Dave<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in'>
<p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span
style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>
mapguide-users-bounces@lists.osgeo.org
[mailto:mapguide-users-bounces@lists.osgeo.org] <b>On Behalf Of </b>Warren
Medermach (External)<br>
<b>Sent:</b> Monday, March 15, 2010 3:12 PM<br>
<b>To:</b> MapGuide Users Mail List<br>
<b>Subject:</b> RE: [mapguide-users] RE: MapGuide connection to SQL Server
through ODBC<o:p></o:p></span></p>
</div>
</div>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal><span style='color:#1F497D'>Thanks for the speedy response
Dave!<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'>I think I should clarify more.<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'>If I create a UDL, or ODBC,
connection on the MG Server machine to the SQL Server on the other machine it
works fine. I’m trying to create a connection to SQL Server from
Studio via the ODBC FDO Provider and choosing the ‘Connect to a database’
and ‘Microsoft SQL Server’ and then specifying the credentials and
proper connection string. This is failing. So I’m not sure
what MG is doing differently to communicate with the SQL Server?<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'>As for port 1433, that would
need to be open on both machines for the ‘handshake’
communications, correct? I wonder if that’s the problem…<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'>Warren M</span><b><span
style='font-size:9.0pt;font-family:"Tahoma","sans-serif";color:black'><o:p></o:p></span></b></p>
<p class=MsoNormal><span style='font-size:9.0pt;font-family:"Arial","sans-serif";
color:black'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in'>
<p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span
style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>
mapguide-users-bounces@lists.osgeo.org
[mailto:mapguide-users-bounces@lists.osgeo.org] <b>On Behalf Of </b>Dave Wilson<br>
<b>Sent:</b> Monday, March 15, 2010 2:29 PM<br>
<b>To:</b> MapGuide Users Mail List<br>
<b>Subject:</b> [mapguide-users] RE: MapGuide connection to SQL Server through
ODBC<o:p></o:p></span></p>
</div>
</div>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal><span style='color:#1F497D'>I have an instance in house
where the connectivity fails because of a setting to do with the port in the
ODBC configuration.<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'>When setting up the ODBC
configuration there is a button for Client Configuration, click this and
uncheck the dynamically determine port. 1433 should be shown in a grayed out
box.<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'>That might be the issue.<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'>Dave<o:p></o:p></span></p>
<p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p>
<div>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in'>
<p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>From:</span></b><span
style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'> mapguide-users-bounces@lists.osgeo.org
[mailto:mapguide-users-bounces@lists.osgeo.org] <b>On Behalf Of </b>Warren
Medermach (External)<br>
<b>Sent:</b> Monday, March 15, 2010 2:16 PM<br>
<b>To:</b> MapGuide Users Mail List<br>
<b>Subject:</b> [mapguide-users] MapGuide connection to SQL Server through ODBC<o:p></o:p></span></p>
</div>
</div>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>Hello all,<o:p></o:p></p>
<p class=MsoNormal>I’m trying to make a connection to SQL Server through
ODBC using MG Studio.<o:p></o:p></p>
<p class=MsoNormal>This works fine on a local test system where MG server &
SQL Server are installed on the same machine.<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>When deployed, MG Server is NOT installed on the same
machine as SQL Server.<o:p></o:p></p>
<p class=MsoNormal>In this scenario, I can create a UDL on the MG server
machine and successfully connect to SS with no problems.<o:p></o:p></p>
<p class=MsoNormal>However, when I try to create a data connection in MG, it
fails with the following error:<o:p></o:p></p>
<p class=MsoNormal style='text-indent:.5in'>response returned status code 502
(BadGateway)<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>It would seem that something on the SQL Server machine is
blocking the connection request from MG. So the question is, what is MG
doing differently than a simple UDL connection to communicate with the SQL
Server machine? <o:p></o:p></p>
<p class=MsoNormal>Any ideas, suggestions would be greatly appreciated.<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>MGE 2010<o:p></o:p></p>
<p class=MsoNormal>SQL Server 2005<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>Warren M<span style='font-size:9.0pt;font-family:Consolas'><o:p></o:p></span></p>
<p class=MsoNormal><o:p> </o:p></p>
</div>
</body>
</html>