Sharepoint Designer 2007 ODBC database connection to MySQL impossible?

Question

I'm trying to create a Database Connection to ODBC data sources in SPD 2007 and am having absolutely no luck. I've had some success using an SqlDataSource control, however. I don't know if SPD's database connection support is just broken or what. Googling has turned up others having issues but no definitive answers.

So, my two data sources are MySQL 5.1 ODBC and ProvideX ODBC. I pretty much have the same trouble with either, so I'll just focus on MySQL.

I've got a system DSN on the machine running SPD that is successfully connecting to the MySQL database. (This DSN also exists on the server running Sharepoint, if that matters at this time).

In SPD, I go the Data Source Library, expand Database Connection, and choose Connect to a Database. I then click on Configure Database Connection. Since neither of the providers in this dialog are appropriate, I choose "Use a custom connection string". I then set the provider to "Microsoft .Net....ODBC" and at this point I've tried the following connection strings.

First the DSN attempts:

Dsn=TheOneICreated  (this one works from the SqlDataSource control)

and

Data Source=TheOneICreated

Then the more direct approach:

Driver={MySQL ODBC 5.1 Driver};Server=myServerIP;Database=dbName;User=username;Password=password;Option=3

The error I get back is always the same -

"Server Error: An error occurred while retrieving the list of Databases from : The server for the data source returned a non-specific error when trying to execute your query. Check the format and content of your query and try again. If the problem persists, contact the server administrator."

Am I fighting a losing battle here?

Answer 1

It looks like SD could be enumerating databases and/or tables so it has a list of tables and fields to 'help' you along....

Just a clarifying question or two..

1. Could this happen because SD is expecting a different way of enumerating tables? (Does it actually work with MySQL or anything ?)

2. What does your ODBC trace look like? (If #1 is true, you'll be able to see it and possibly create supporting views...if you truly need it to work with this particular dbms...)

3. Is this connection accessed from the sharepoint server? If so, do you need to define your dsn & driver there?