MySQL DSN-less connection issue

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@10.0.0.34' (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 10.0.0.34 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.

TIA,
Bob

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

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

mySQL.CursorLocation = adUseClient
myrst.CursorLocation = adUseClient

mySQL.Open
myrst.Open "SELECT * FROM Users", mySQL
LVL 1
sharizodAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

petoskey-001Commented:
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.  
http://dev.mysql.com/doc/mysql/en/GRANT.html

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.
http://www.mysql.com/products/administrator/

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sharizodAuthor Commented:
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
Bob
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.