I am trying to configure MySQL on a Linux 6.2 box to accept remote connections so that users with MyODBC can connect to it. Any suggestions on what I need to do?

Thanks!

not too much!

Just configure MySQL to accept connections from an external IP address....

The MyODBC link can just be setup in the data source section of the control panel ;)

Cheers,

Originally posted by jonny b
not too much!

Just configure MySQL to accept connections from an external IP address....

The MyODBC link can just be setup in the data source section of the control panel ;)

Cheers,

What do you mean Johnny? Like which control panel?

Bert!

The control panel in Windows!

Just the standard control panel, there should be a link under ' Administrative Tools ' for ' Data Sources ( ODBC ) '

In there, you just create a user DSN which points to the IP address of the MySQL host !

Cheers,

Originally posted by jonny b
Bert!

The control panel in Windows!

Just the standard control panel, there should be a link under ' Administrative Tools ' for ' Data Sources ( ODBC ) '

In there, you just create a user DSN which points to the IP address of the MySQL host !

Cheers,

That is what I did in the first place, but it would not connect. I though MySQL in the "server" had to be configured to accept remote connections. As I understand it MySQL will only accept connections through the local host.

So don't I have to configure MySQL on the server itself?

You will need to configure mySQL on the server to accept connections from other than 'localhost'. Assuming that you are using the latest and greatest mySQL:

/usr/bin/mysql mysql -u root -p*****

(open as small a hole as possible - one IP would be best, if at all feasible)

mysql>insert into user values("$ipofuser","$user",password("$password"),
"N","N","N","N","N","N","N","N","N","Y","N","N","N","N");

mysql>q;

Make sure you reload mySQL after making these types of changes:
/usr/bin/mysqladmin reload -u root -p*****

Alabanza systems can use the path /usr/local/mysql/bin/ to access mySQL functions instead of the more usual path above.

Thanks Annette. One last question:

Why two passwords in the syntax? Is that only if I am changing the user password? Does that password affect the user connecting through localhost as well or it is only for remote connections?

Last but not least, what if the user dials up and his IP changes constantly, can a block or class be used?

Thanks for all your help. :)

Originally posted by Annette
You will need to configure mySQL on the server to accept connections from other than 'localhost'. Assuming that you are using the latest and greatest mySQL:

/usr/bin/mysql mysql -u root -p*****

(open as small a hole as possible - one IP would be best, if at all feasible)

mysql>insert into user values("$ipofuser","$user",password("$password"),
"N","N","N","N","N","N","N","N","N","Y","N","N","N","N");

mysql>q;

Make sure you reload mySQL after making these types of changes:
/usr/bin/mysqladmin reload -u root -p*****

Alabanza systems can use the path /usr/local/mysql/bin/ to access mySQL functions instead of the more usual path above.

If there are currently active users on this server and you don't restrict access to certain programs like "ps", than users can see this process running and the MySQL root password in the environment of the command. (Bert, I know you have this so user's can't execute that or any similar commands, so don't worry). However, mysql will work just fine with the -p switch where it'll prompt you for the password without it being in the command line. Try to avoid doing that, ever, unless there's no users on the system or only user's that you know are trusted (and hope that no one's compromised their account). Finally, be sure, since I'm mentioning this, to not use your servers root password for the MySQL root user.

I tried what Annette said though and it didn't work. I must have done something wrong with the syntax. :(

I have yet to see the mySQL root password listed in the command environment under ps in conjunction with running this command set. YMMV.

Bert, the first password is the root password for mySQL. The second password is the user's own mySQL password.

I have successfully entered blocks of IPs for people in the format 1.2.3.* for those users who have dynamic IP addressing. I'm not a big fan of it, since remote myODBC stuff is painfully slow - and on dialup I can't even imagine - but it can be done.

If you run into errors, try putting the INSERT stuff in notepad first, then cutting and pasting it into your session. I had to do that with a couple, and now I do it with all of them. Easier that way. Don't forget the semicolon at the end, either - very common.

You guys are really going to kill me, but I can't get this thing to work:

mysql>insert into user values("$192.168.1.1","$admin_user1",rootpass("$userpass"),
"N","N","N","N","N","N","N","N","N","Y","N","N","N","N");

ERROR 1064: You have an error in your SQL syntax near: '("$userpass"),"N","N","N","N","N","N","N","N","N","Y","N","N","N","N")' at line 1

Help :(

OK, I see the problem. The $ are just placeholders to define field entries names. The root password does not enter into the INSERT statement.

Example:

If you have a user bert, with a password of bertspass, connecting from an IP of 1.2.3.4, you'd enter:

mysql>insert into user values("1.2.3.4","bert",password("bertspass"),
"N","N","N","N","N","N","N","N","N","Y","N","N","N","N");

Originally posted by Annette
I have yet to see the mySQL root password listed in the command environment under ps in conjunction with running this command set. YMMV.

[SNIP]



Looks like you're correct about YMMV, because it simply shows -pxxxxxxx in the process on ver. 3.23.38. It seems this is dependant on the version. I do wonder if there's a means to grab that from the process though. Hmmm.

Never mind. My fault. It was password not the actual password.

Thanks for the help!!! :)

Still have problems. I configured mysql, it appeared to have accepted the new user values, but still my customer can't connect. He keeps getting a message that says 'not allowed to access this MySQL server'

Any suggestions? Anything else that I need to do?

Thanks!

Things to check:

Is the customer really connecting from the IP (range) that they gave you? Was the IP entered correctly?
Is the customer behind a firewall?
Has the entry been made on the right server (I was adding two users and added them both on the same system, when they should have been on servers 3 and 5 instead - duh)?
Has the correct username and password been entered?
Is the customer using the right username and password?

Originally posted by Annette
Things to check:

Is the customer really connecting from the IP (range) that they gave you? Was the IP entered correctly?
Is the customer behind a firewall?
Has the entry been made on the right server (I was adding two users and added them both on the same system, when they should have been on servers 3 and 5 instead - duh)?
Has the correct username and password been entered?
Is the customer using the right username and password?

I don't really know what I would do if it wasn't for you. No one I ask knows how to do this.

This customer used to connect before with his old host, so I guess he knows how to do it. OK, his server username is getting and his mysql username is results, so his mysql username should be getting_results. This is how I entered the info:

First I logged into mysql:

/usr/bin/mysql mysql -u root -p*****

Then, I modified the user:

mysql>insert into user values("192.168.*.*","getting_results",password("hispasswordhere"),
"N","N","N","N","N","N","N","N","N","Y","N","N","N","N");

Then I got a message that said something like "row modified" or something like that.

So, I exited mysql:

mysql>q;

Then, I restarted mysql:

/usr/bin/mysqladmin reload -u root -p*****


He is now connecting through port 3306 with his IP address and his username of "getting_results" and his password. He still gets that message. I checked all the points that you mention above and still does not work. Is there anything that I need to install on the server like a module or something?

Drop the "getting_" from the username. The only thing we're after here is the connection to the db itself, as that's all the system will use to compare against its own entries.

I guess I am going to shoot myself :(

I did everything again without the "getting_"

This is what my customer says:

Same thing. Host 'hostname' is not allowed to connect to this MySQL server. At least you know you're security's nice and tight! Here are the options I specify for the ODBC driver: hostname or IP (192.168.1.1), database name (getting_results), user (getting_results), password (***), port (blank defaults to 3306). I tried "getting" for the username, but it's asking for the db username, not my shell name. But even if I leave those blank, it doesn't matter: the connection itself is refused. If the connection was fine and the login info was okay, I'd get an authentication error. The driver works fine when I use 'localhost' so at least I know MyODBC itself is working.

Anything else you can think of? :(

OK, here are some more things:

He's using the wrong mySQL username (getting_results instead of just results). His hostname should be the IP or name of his site (unless you're using 192.168.* as a sub for the actual IP).

Originally posted by Annette
OK, here are some more things:

He's using the wrong mySQL username (getting_results instead of just results). His hostname should be the IP or name of his site (unless you're using 192.168.* as a sub for the actual IP).

I could swear he was mentioned using results only, but no, he tried "getting" which is his regular username. I will tell him and let you know. Thanks again. I will owe you big time! ;)

I guess I give up! I told him to try with "results" only and this is what he said:

"Same result: "Host 'hostname' is not allowed to connect to this MySQL server." I've tried getting_results, results, getting, blank, everything. I don't think the username or password is the problem. The connection is refused before I even get that far. If the username was wrong, I'd see: "Access denied for user: 'username@host' (Using password: YES) (#1045)". So I'm still at a loss at this end."

Well, I just don't know what else to do. :(

If it helps, this server runs Linux 6.2 with the standard Cpanel/WHM install and it also has portsentry.

Have you tried setting it up for yourself (with your IP) to see if you can get connected with myODBC?

Originally posted by Annette
Have you tried setting it up for yourself (with your IP) to see if you can get connected with myODBC?

Yes I have. I set it up on a Windows 98 machine that is connected via a T1 and I just get an error the same way he does.

Isn't there anything that needs to be installed on the server? What about Portsentry, could that be blocking the port? It looks as if it is not allowing access at all, it does not seem to be an issue of authentication. :(

Interesting. I wonder if the default port for remote ODBC is open on boxes running cPanel/WHM. Perhaps Matt can shed some light on this issue for us. I'll have to test it on an account on the servers we have running that combination to see what I get.

Originally posted by Annette
Interesting. I wonder if the default port for remote ODBC is open on boxes running cPanel/WHM. Perhaps Matt can shed some light on this issue for us. I'll have to test it on an account on the servers we have running that combination to see what I get.

Well, It might be that. I will send an email to Matt asking him to help a little. We'll see what he says.

Bert, I don't think you can use '*' as wildcard charachters in MySQL. I believe you need to use a percent sign '%' instead (I'm talking about the IP address string you setup). Give that a shot and see if it works.

Best Regards,
Matt Lightner
mlightner@site5.com

That's an idea. If it works, that would be a difference between these servers and others I've worked with (i.e., * as wildcard works on Alabanza systems and another generic linux box I've built).

Nope, did not work:


mysql> insert into user values("24.165.234.%","results",password("hispasswordhere"),"N","N","N","N","N","N","N","N","N","Y","N","N","N","N");
Query OK, 1 row affected (0.00 sec)

mysql> \q
Bye

Restarted MySQL and still he can't connect. I did it actually without the wildcard or % symbol to test it with my own user. I have a static IP, I entered my IP and I can't connect to it either. It is like MySQL refuses to allow an external connection through port 3306. Could it be something with portsentry or with WHM/CPanel? :(