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

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?

asked 10 Nov '10, 14:25

Reimer Pods
4.5k●38●48●91
accept rate: 11%

edited 11 Nov '10, 14:30

Siger Matt
3.3k●56●72●101

flat view

One Answer:

active answers oldest answers newest answers popular answers

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.

permanent link

answered 10 Nov '10, 14:34

Reimer Pods
4.5k●38●48●91
accept rate: 11%

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...

(10 Nov '10, 14:54) Volker Barth

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.

(10 Nov '10, 15:37) Siger Matt

@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...

(10 Nov '10, 16:07) Volker Barth

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

(10 Nov '10, 16:40) Volker Barth

@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!

(10 Nov '10, 17:09) Justin Willey

@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.

(11 Nov '10, 13:57) Reimer Pods

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

(11 Nov '10, 14:22) Volker Barth

More comments hidden

showing 5 of 7 show all flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

odbc ×145
remote-server ×56
dsn ×17
mysql ×16

question asked: 10 Nov '10, 14:25

question was seen: 5,547 times

last updated: 11 Nov '10, 14:30

SAP SQL Anywhere

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

Follow this question

Related questions