ODBC not connection to database

[Bob Sanderson]

I am trying to set up an ODBC DSN to our company MySQL database so that I
can use a MS Access front end. I am using the MySQL ODBC 3.51 driver. The
database is running off of Apache on our Windows 2000 server (call it
'foobar'). The host is 'localhost', the user is 'root'. To access the
database via a web browser on a PC on our network, I use the address
'http://foobar:8080/". This works fine. If I set up a DSN on the server,
I connect to the database ok, but I have not been able to set up a DSN on
my PC.

If I use 'localhost' as the servername, I get the following message:
[MySQL][ODBC 3.51 Driver]Can't connect to MySQL server on 'localhost'

If I use foobar:8080, I get '[MySQL][ODBC 3.51 Driver]Unknown MySQL server
host 'foobar:8080'

If I use 'foobar', I get '[MySQL][ODBC 3.51 Driver]Host 'BSANDERSON' is not
allowed to connect to this MySQL server'

This is my first attempt to set up a DSN and I really don't know what I'm
doing. Any help will be greatly appreciated.

[Bill Karwin]

"Bob Sanderson" <sa*****@LUVSPAMsandmansoftware.com> wrote in message
news:Xn**********************************@207.69.1 89.191...

I am trying to set up an ODBC DSN to our company MySQL database so that I
can use a MS Access front end. I am using the MySQL ODBC 3.51 driver. .. . . If I use 'foobar', I get '[MySQL][ODBC 3.51 Driver]Host 'BSANDERSON' is
not
allowed to connect to this MySQL server'

This is correct, but you got an error suggesting that your PC is not allowed
to connect to the MySQL service on foobar. This is a privilege issue. This
indicates that your ODBC connection succeeded in contacting the MySQL
service on foobar, but was denied access due to lack of privileges.

Whoever set up the MySQL service did not grant your PC the privilege to
connect directly to the MySQL service via the network. It is a typical
policy to enabled access only from applications running on the same host
(foobar), applications such as phpmyadmin. Though it is technically
possible to enable access from remote applications, it may have been
disallowed by default policy.

You should talk to the person who is responsible for administering the MySQL
service on your foobar machine, and ask them if it is okay for you to
connect directly to your company MySQL database from your PC using ODBC.

Regards,
Bill K.

[Bob Sanderson]

"Bill Karwin" <bi**@karwin.com> wrote in
news:ds********@enews4.newsguy.com:

You should talk to the person who is responsible for administering the
MySQL service on your foobar machine, and ask them if it is okay for
you to connect directly to your company MySQL database from your PC
using ODBC.

Thanks for the reply. Unfortunately, that person is me and I don't know how
to do that.

[Bill Karwin]

"Bob Sanderson" <sa*****@LUVSPAMsandmansoftware.com> wrote in message
news:Xn**********************************@207.69.1 89.191...

You should talk to the person who is responsible for administering the
MySQL service on your foobar machine, and ask them if it is okay for
you to connect directly to your company MySQL database from your PC
using ODBC.

Thanks for the reply. Unfortunately, that person is me and I don't know
how
to do that.

You should read the MySQL documentation on the access privilege system:
http://dev.mysql.com/doc/refman/5.0/...ge-system.html

And also the reference documentation on the statements used to administer
accounts and grant privileges:
http://dev.mysql.com/doc/refman/5.0/...ement-sql.html

So basically you need to grant privilege to some account on your PC to
connect to one or more databases. For example:

GRANT ALL PRIVILEGES ON databasename.* TO 'username'@'bsanderson' IDENTIFIED
BY 'password';

Quick explanations (read the docs for more information):
- databasename.* means all tables in the specified database
("databasename"). You can also use a wildcard for the database name (*.*).
- 'username'@'bsanderson' means the named user account is permitted to
connect only from the client host bsanderson. MySQL accounts are completely
distinct from operating system accounts, and you get to make up the user
name. Granting privileges to that named user implicitly "creates" the
account for purposes of connecting to MySQL. The host is the client host,
not the MySQL server host. Hosts may use '%' as a wildcard.
- IDENTIFIED BY 'password' means that the password inside the single quotes
is required for the user to connect from the specified client host. If you
leave out this clause, then no password is required (be careful with this!).

Notice:
- databasename.* is not in quotes.
- username and hostname are each individually in quotes, separated by a @
symbol, which is not in quotes.
- the password string is in quotes.

Regards,
Bill K.

[Bob Sanderson]

"Bill Karwin" <bi**@karwin.com> wrote in
news:ds*********@enews1.newsguy.com:

GRANT ALL PRIVILEGES ON databasename.* TO 'username'@'bsanderson'
IDENTIFIED BY 'password';

Thanks, Bill. That solved my problem. Much appreciated.