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.
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: response returned status code 502 (BadGateway) It wasn't until we tested on a completely localized environment that a more meaningful, only somewhat cryptic error message was thrown: Feature Source - Unterminated entity reference, 'Bob' '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. Warren M From: [email protected] [mailto:[email protected]] On Behalf Of Warren Medernach Sent: Tuesday, March 16, 2010 10:18 AM To: MapGuide Users Mail List Subject: RE: [mapguide-users] RE: MapGuide connection to SQL Server throughODBC Unfortunately, this is not what we are finding. 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... Warren M From: [email protected] [mailto:[email protected]] On Behalf Of Dave Wilson Sent: Monday, March 15, 2010 3:40 PM To: MapGuide Users Mail List Subject: RE: [mapguide-users] RE: MapGuide connection to SQL Server throughODBC 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. Dave From: [email protected] [mailto:[email protected]] On Behalf Of Warren Medermach (External) Sent: Monday, March 15, 2010 3:12 PM To: MapGuide Users Mail List Subject: RE: [mapguide-users] RE: MapGuide connection to SQL Server through ODBC Thanks for the speedy response Dave! I think I should clarify more. 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? 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... Warren M From: [email protected] [mailto:[email protected]] On Behalf Of Dave Wilson Sent: Monday, March 15, 2010 2:29 PM To: MapGuide Users Mail List Subject: [mapguide-users] RE: MapGuide connection to SQL Server through ODBC I have an instance in house where the connectivity fails because of a setting to do with the port in the ODBC configuration. 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. That might be the issue. Dave From: [email protected] [mailto:[email protected]] On Behalf Of Warren Medermach (External) Sent: Monday, March 15, 2010 2:16 PM To: MapGuide Users Mail List Subject: [mapguide-users] MapGuide connection to SQL Server through ODBC Hello all, I'm trying to make a connection to SQL Server through ODBC using MG Studio. This works fine on a local test system where MG server & SQL Server are installed on the same machine. When deployed, MG Server is NOT installed on the same machine as SQL Server. In this scenario, I can create a UDL on the MG server machine and successfully connect to SS with no problems. However, when I try to create a data connection in MG, it fails with the following error: response returned status code 502 (BadGateway) 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? Any ideas, suggestions would be greatly appreciated. MGE 2010 SQL Server 2005 Warren M
_______________________________________________ mapguide-users mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/mapguide-users
