Use access with MYSQL

[Banana]

Be aware this is a security risk. If you have this:

'root'@'%'

this means the root user can connect to the server from anywhere in the world and is an open invitation to malicious users. This is why it's usually best to do a GRANT and restrict the IP range or host name accordingly.

 

[AJordan]

I understand completely I just did it this way as an inhouse test between two computers. Before this becomes adopted I will research the information you provided and set up the permission appropriately.

Maybe I should start another thread but I am playing around with the ODBC linked table (in Access 2007) and I tried to manipulate a record (Change a name from John to Jane). However I recieved a nice alert and a message at the bottom left corner of the screen that "This Recordset is not updateable". We will use it mostly for running reports but if we are migrating the entire backend of our DB we will need to be able to update records in a non MySQL environment such as our Access front end.

 

[Banana]

Ah, yes, probably few more settings to configure.

You may find it beneficial to read this:

Beginner's Guide to ODBC

MySQL also has few specific configurations needed to work with Access - it should be covered at their site on the Connector/ODBC, under 'Configuration' and 'Note and Tips' (they have a section for Access there as well).

HTH.

 

[AJordan]

Thanks, informative read (and thanks for putting it in a pdf format). Inside my two computer set-up, everything works great and as it should. I expanded the network to 3 computers and no problems, and I am happy with performance.

Although I did try to connect to MySQL database via WAN using my public IP address (found at whatismyip.com) and I couldnt get a connection. It could be that the test network I set up was using a cable modem and a router and the router was blocking the connection (not sure). I think I will try by just connecting the modem directly to the PC and see what happens.

 

[Banana]

No.

You never need to modify the security setting for the client, only the server because that's the place where it's listening for the connection. Therefore the router on the MySQL network has to be configured to forward data on port 3306 (or any port you use instead) to the MySQL's machine.

A better alternative is to use VPN or SSH or similar connection to make your machine "local" to the network so it then can access MySQL. This provides an extra protection and doesn't require you to expose MySQL to the internet.

 

[AJordan]

Looks like I hit the wall. I have tried everything I have read and know to connect to my pc via WAN. I constantly get the unable to connect to 68.34.xx.x . I can connect using the private IP when I'm on the network, but when I get I use another internet connection it just wont connect.

I guess my only option is to find a mysql host that will allow a remote connection.

 

[Banana]

Did you investigate the routers or the network? This involves more than just your PC.

 

[AJordan]

I have been using a desktop and a laptop. The Desktop is running the MySQL Community Server and I have the ODBC Connector installed on both the laptop and desktop.

When working within the LAN on the laptop I have no problem accessing the MySQL database files located on the desktop PC using the local IP address.

I then tried using an outside internet connection on the laptop to access the database, now using the public address of the network the PC is attached to. Found this address on whatismyip.com. This attempt failed.

Next, I have also configured the Dynex Wireless G Router for port forwarding on 3306.
I have made exceptions for the port within Mcafee and Windows Firewalls on both computers.

I try again to access the MySQL database over ODBC, using the public ip address and again I am unable to connect. I have also tried shutting down all firewalls just to see if that was a problem.

I have not been able to find a lot of information on this subject, but I can’t understand how I can connect through LAN but not WAN.

 

[Banana]

It still sounds to me you've not configured the routers.

To provide an example.

If I have a home network, and work network and I want to connect to the work network from home, I need to configure the routers or use a VPN software. To provide an example:

Home network:

My Computer (192.168.0.100) <- private IP

My router/modem (192.168.0.1) <- also private IP but is known as 'gateway IP'

My ISP (123.123.123.123) <- the public IP, assigned by ISP.


Then in my work network:

MySQL server (10.0.0.100) <- also a private IP

My router (10.0.0.1) <- a gateway, too.

Work ISP (231.231.231.231) assigned by ISP.


The bridge between two network is of course across the network, meaning one network only see the other's public IP.

If I tried this connection setup:

My home computer (192.168.0.100) --> MySQL Server (10.0.0.100)

what actually happens is this:

My home computer asks router (192.168.0.1) "Where is the 10.0.0.1?", and the router would reply "I don't have any machine here with that address." Thus the connection fails.

Now if I tried to use public IP:

My home computer (192.168.0.100) --> MySQL Server (231.231.231.231)

What happens is this:

My home computer asks the router "Where's the IP 231.231.231.231?", the router will then ask the ISP's DSN server, "Where is the IP 231.231.231.231?", and the ISP DSN will then ask other DSN server until it reaches the work ISP's DSN server which will reply, "here it is." and thus forward the connection request to IP 231.231.231.231.

But the work router (which is listening at 231.231.231.231 public iP) will say, "I do not allow any outside connection, so I'm rejecting and closing this connection."

Thus, you need to access the router directly and tell it to set up port forwarding. The port forwarding would be something like "TCP 3306 Any source IPs ==> 10.0.0.100".
So doing the connection above again, then the work router will be able to say, "Yeah, I have a rule telling me to forward this data to the 10.0.0.100" and it does, finally reaching the MySQL server and interacting with the MySQL.


To configure the router... keep in mind that it is dependent on which router you have, but we'll use a simple router you usually buy from Best Buy as an example. Hopefully you can then figure out how to configure your work router. Anyway, usually, if you buy say a D-Link router, it has a default IP of "192.168.0.1", or maybe "192.168.1.1". If you are uncertain, use the ipconfig and look up "Gateway IP".

Then in the internet browser, you type in "192.168.0.1" for the address bar. It will then connect to the router, and you will be prompted to log in. Usually the default setting is something like "Admin/(blank password", or "Admin/Admin" or "Admin/password". Again, consult your router's manual for the correct default login, if it never has been configured before. Then that's where you set up the port forwarding so it can then redirect all data on port 3306 to MySQL.

Now, you also need to be aware of the risk this places on your work network and for this reason, it is usually good idea to require some kind of secure connection, perhaps by using SSL, or VPN. VPN is different from the above steps because when you open a VPN software & successfully connect to the work router, VPN will then request your work router (10.0.0.1) for a local IP and get one... say, (10.0.0.200). Therefore, your home computer becomes "local" and thus can access MySQL server at 10.0.0.100, without need to go through the public IP.

I hope this helps....

 

[AJordan]

I truly appreciate your help and support and patience, it has been great! I only wish I could just download your knowledge into my brain, lol.

I tried this very thing yesterday (logged on to my dynex router using http://192.168.2.1, and went to the section for port forwarding) I added a new rule "MySQL Server", TCP, Port 3306 and then I needed to enter a private IP address. I don't believe the MySQL Server already had a private IP so I entered ".5" (192.168.2.5) as the IP address for MySQL Server (Desktop was .2, the laptop was .3 another computer was .4).

However, it still wouldnt allow me to connect, I will attempt to rework and see if I can have success.

I have been switching to another ISP source to try to see if WAN would work. Is it possible to do an ODBC connection via WAN while connected to the LAN?

***Just found a website for port forwarding information with my router, I will try their instructions for MySQL Server
http://portforward.com/english/routers/port_forwarding/Dynex/DX-WEGRTR/MySQL_Server.htm

 

[Banana]

For the port forwarding to work reliably, the dynex router has to give MySQL server same private IP address every time- if the dynex router is capable, you can configure it to assign only 192.168.2.5 to MySQL server based on its MAC address.

What is the error you get now?

I'm pretty sure there's shouldn't be a problem with looping back using the public IP- it's the router's job to appropriately forward the request at port 3306 to the MySQL server, then it's MySQL's job to authenicate that request (which may be now 'yourusername'@'yourpublicIP' )

 

[AJordan]

If you lived anywhere near me I'd buy you a beer!

 

[Banana]

So I take you got it working now?

Glad to be of help, and I will drink my beer tonight in the honors.

Also, just so you didn't forget- be aware about the security ramifications of opening the port to the WAN. If you can work out a solution that requires secure connection, then you are probably better off.

Nonetheless, best of luck!