[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 727
  • Last Modified:

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
0
sharizod
Asked:
sharizod
1 Solution
 
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
 
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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Tackle projects and never again get stuck behind a technical roadblock.
Join Now