mySQL ODBC Connection Failure with CF MX7

[RTB1]

Installed mySQL 4.1 from XAMPP packages that comes with PHP, FileZilla, and
Mercury. Everything works very well with PHP and BlueDragon Cold Fusion;
however, I also want to use Macromedia Cold Fusion MX. When I attempted to add
a datasource from the CF Administrator page using mySQL (3.x) as the chosen
driver, I get the following error. I tried "localhost" and "rtb3" as names of
the computer; it didn't work.

Connection verification failed for data source: rtb
java.sql.SQLException: Server configuration denies access to data source
The root cause was that: java.sql.SQLException: Server configuration denies
access to data source

Any solutions. Thanks in advance,
R. Baker
RTB Design:confused;:Q

[mxstu]

I know this doesn't answer your question, but why use an ODBC connection with mySQL instead of JDBC?

[RTB1]

Thanks for the questions. For JDBC, would you know what connection string that I should use for mySQL 4.1?

[mxstu]

You don't use a connection string for JDBC. You use a jdbc url. Since you're
using mySQL 4.1, see the instructions in this tech note:
[url]http://www.macromedia.com/cfusion/knowledgebase/index.cfm?id=6ef0253[/url]

Important - Athough the current version of the jdbc connector is 3.1.11 there
have been some compatability problems with MX6 and MX7. So I would recommend
using a different version of the jar. I have used versions 3.0.17ga, 3.1.9 and
3.1.17 successfully.

Mirror sites with other versions of jdbc connector jar
[url]ftp://ftp.orst.edu/pub/mysql/Downloads/Connector-J/[/url]
[url]ftp://mirror.services.wisc.edu/mirrors/mysql/Downloads/Connector-J/[/url]

[RTB1]

Thanks again... tried the following.
1. In mysql, ran mysql>grant all privileges on *.* rtb@localhost identified
by 'pw' with grant option;
2. Next, extracted mysql-connector-java-3.0.17-ga-bin.jar from the downloaded
zip file and placed it WEB-INF/lib folder.
3. Finally, used the following in the CF Admin.
CF Data Source Name: tester
JDBC URL: jdbc:mysql://localhost:3306/test
Driver Class: com.mysql.jdbc.Driver
Driver Name: mysql-connector-java-3.0.17-ga-bin.jar
User Name: rtb (as established in mySQL)
Password: 1234

The following error displayed:

Connection verification failed for data source: tester
java.sql.SQLException: Invalid authorization specification message from
server: "Access denied for user 'rtb'@'localhost' (using password: NO)"
The root cause was that: java.sql.SQLException: Invalid authorization
specification message from server: "Access denied for user 'rtb'@'localhost'
(using password: NO)"

It appears to me that the URL is incomplete; however, would need further
clarification. - rtb

[mxstu]

1) Make sure you restarted the CF server after adding the my sql jar to the
WEB-INF/lib folder

2) It may not make a difference, but in the mySQL docs there are single quotes
around the user and host in the grant statement, an a "to" command after the
*.*.
mysql> grant all privileges on *.* to 'rtb'@'localhost' identified by 'pw'
with grant option

[url]http://dev.mysql.com/doc/refman/5.0/en/adding-users.html[/url]

3) The DSN password in CF Admin is "1234" but in the grant statement it is
"pw". Assuming that is not a typo, just make sure the passwords are the same.

[RTB1]

Your statement #3 revealed the problem: I had a mismatch on the passwords for the grant all privileges statement in mySQL. Should had '1234' and not 'pw'. It now works! Thanks for the help!