MySQL DSN-less connection issue

Posted on 2004-11-11
Last Modified: 2008-02-20
Hi experts, hopefully one of you can shed some light on my problem.

I am trying to connect to an external mysql database over the VPN on another machine.   I've tried all kinds
of connection strings in all manner of weird and wonderful ways.  I finally stumbled on something that
works, but  for all intents and purposes, should NOT work!!!  Using the following code I am able to
connect to the database remotely by using the root account with no password.  Now the person that
set up the database set up php & mysql to work on this remote machine and they insist that the root
account password was changed and definitely NOT blank!!  How is it that I am able to get in??

Also, when trying to use any username and password combo that the person gave me for valid accounts
to connect to the MySQL db, it gives me the following error for any user other than root:

Run-time error '-2147467259 (80004005)';
[MySQL][ODBC 3.51 Driver]Access denied for user:
'administrator@' (Using password: YES)

The above error stops the code below at the mySQL.Open line.

Does anyone know what is causing this and what has to be done to properly connect to a MySQL database
in a DSN-less way!??!   I feel I'm on the right track but something is missing somewhere... I also installed
MySQL server on my machine (the address that comes up in the error).  Would that have anything
to do with it?  I was simply trying to get the drivers necessary to access MySQL which I later found was a
different download which I've subsequently installed.  It's my first time working with MySQL BTW.

I was simply working with it in Visual Basic since it is easier to work with in there than in vbscript
which is where my code will end up.


Dim mySQL As New ADODB.Connection
Dim myrst As New ADODB.Recordset

mySQL.ConnectionString = "Provider=MSDASQL;DRIVER={MySQL ODBC 3.51 Driver};SERVER=;DATABASE=helpweb;UID=root;OPTION=3"

mySQL.CursorLocation = adUseClient
myrst.CursorLocation = adUseClient

myrst.Open "SELECT * FROM Users", mySQL
Question by:sharizod
    LVL 7

    Accepted Solution

    If you can connect to the database sometimes, but get different results with different username/password pairs, then the problem is not your connection.  This is a security issue.  Specifically you grant tables.

    MySql security uses 3 things to decide to give you access or not.  Your Username, Password AND Host.

    Here is the MySql manual on the grant syntax.

    Some people find the grant syntax a bit confusing.  The easier way to solve this would be to install the MySql administrator.  It's got an excellent user administration system that lets you see your users and assign rights based not only on their password, but also on their host.

    You can read about it and download it here.

    Download it and connect to the remote MySql server.  Then  you can use the Administrator GUI to configure your username, password and host access.  
    LVL 1

    Author Comment

    Thanks petoskey-001,

    I'm not sure if grant was at issue or not, but when we deleted the anonymous user was still defined in the database and created a new user
    for the script to login to, the problem disappeared.   Definitely permissions issue of some sort though!.

    Thanks for the help

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi ( had suggested a “sed” way, I actually shell …
    Both Easy and Powerful How easy is PHP? (  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now