How can I create a remote server to access a MySQL database?

[reimer_pods]

SA 12.0.0.2589, Win XP SP3
I'm trying to create a remote server to retrieve data from a MySQL database another machine in our LAN. The MySQL ODBC 5.1 Driver is installed, a ODBC data source named TestMySQL created, test connection successfull. But creating a remote server always fails while testing the connection trying to use localhost!?.

Connection failed.
Unable to connect to server 'TestMySQL': [MySQL][ODBC 5.1 Driver]Can't connect to MySQL server on 'localhost' (10061)
[Sybase][ODBC Driver][SQL Anywhere]Unable to connect to server 'TestMySQL': [MySQL][ODBC 5.1 Driver]Can't connect to MySQL server on 'localhost' (10061)
SQLCODE: -656
SQLSTATE: HY000
SQL Statement: SELECT FIRST table_name FROM dbo.sp_remote_tables( 'TestMySQL', NULL, NULL, NULL, 1 ) ORDER BY 1

Sample statement:

CREATE SERVER "TestMySQL" CLASS 'MYSQLODBC' USING 'TestMySQL';

Tried variations (server:port/database, driver=...) to no avail. The documentation is rather terse.
Any ideas what I'm doing wrong?

[reimer_pods]

Directly after posting this question I remembered my old motto: double check twice! I went through all again, but this time incidentally recreated the ODBC data source as a system dsn (previously it was a user dsn).
Lo and behold! That was the solution, everything runs smoothly, my question is answered. But still I think, the docs could use some more detail on that topic.

[VolkerBarth]

So you say the user DSN doesn't work since the SA database server runs under a different account (e.g. the local system)? - Then at least your solution seems very comprehensible...

[former_SQLA_member1694868]

I've run into that before as well, not with the remote server but the issue that User DSN is not the same as System DSN.

Also on Windows boxes that are 64 bit there are at that point 2 different ODBC managers, one for 32 bit drivers and one for 64 bit drivers and to make it even more difficult, the 32 bit version is in a folder called c:WindowssysWOW64.

[VolkerBarth]

@Siger: I always thought the even more difficult point with Windows 64 bit is that the 64 bit ODBC Admin is still named Odbcad32.exe...

[VolkerBarth]

That's why I generally prefer to create System DSNs...

[justin_willey]

@volker re 32/64 wonderful isn't it. The 64bit version is called odbcad32.dll and is in a folder called system32 and the 32 bit one is also called odbcad32.dll but is in a folder called syswow64. It's like trying to explain cricket!

[reimer_pods]

@Volker: yes, the engine runs as a service and has no access to my user dsn (HKCU for registry freaks), but only to system dsn (HKLM). It should have been clear to me from the start, but sometimes even coffee doesn't help.

[VolkerBarth]

@Reimer: Agreed - and it's fine that now anyone else running into this (not uncommon) issue may find the solution here:)