Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2006
    Posts
    6

    Unanswered: MYSQL connection in windows workgroup

    need help getting connectedn with ODBC from one machine to a new server. I suspect that the server is configured wrong or I have not set up permissions correctly.

    here is how I am configured.
    in a workgroup
    Host mysql computer
    - xp home edition
    - computer name = jason
    - mysql 5.xx
    - ip address = 192.168.1.121
    - DB=test
    (localhost odbc connects just fine)
    remote odbc connecting computer (in a workgroup not on the internet)
    - xp pro edition
    - computer name = wood
    - mysql is not installed
    - odbc 3.xx installed

    I do not understand how to configure the host and the connecting odbc machine so that I can read the remote mysql database.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    first off have you ensured that you can 'see' the pc "Jason" on the network, and that posrt 3306 is open. you may have a firewall blocking that port, or alterntively your network is seeing the PC.

    check what user permissions have been granted in MySQL. You can set permissions in the command line interface, but MySQL Administrator is just as effective. MySQl, like other db's takes user permissions very seriously - it could be that you can 'see' the server, but not be granted access to it.

    Take it one step at a time - proove it works on the server, then using the same tools see if you can replicate the same access on the client machine. If tools like administrator can 'see' the client but refuse access (usually they report "access deined to user blah from blah") then you knwo that the network topography is working, its just that you haven't given any rights to anyone to do anythin (including yourself).

    User permissions can take the form of userid@hostname, you can specify different levels of access for users, hostranges, ie if a request comes form a range of IP's it is allowed or disallowed access. Have a read through of the security section in the manual, or online.
    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2006
    Posts
    6

    Checking connection

    Mark:

    Thank you for the insight. Here is what has/does work. On the localhost, mysql server, mysql admin, free toad, odbc and openoffice db interface. 3306 looks to be working as the host/server system sees its self and I can work easily on the host.

    I have installed ODBC on the client side. This is where the glitch is. I can see the computer through the net and I can share drives and map them between systems just fine.

    From what I understand you to say, mysql admin can be loaded on the client and used to test as well. Is this correct? I will take it one step at a time as you suggest.

  4. #4
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    If your client can see the server (file sharing, mapped drives, etc.) and the servers port is open and configured correctly, then chances are that the problem is in the permissions like healdem said.

    As far as testing the connection, you should be able to do this in the ODBC manager in Windows. Open up the System DSN and click configure. Also check this out for a quick guide: http://dev.mysql.com/doc/refman/5.0/...onnection.html

  5. #5
    Join Date
    Jan 2006
    Posts
    6

    Test failed

    Here is some detailed information.

    I can see map and work on system A from system B through the network. The DB connection through ODBC is failing.

    Your help is appreciated.

    Steve

    Systems information to follow - - -

    ---------------ODBC system B ---------------------
    ODBC 1.51.12 – User DNS tab
    Data source name = steve
    Description = blank
    Server: 192.168.1.121 or Jason (many attempts using others as well)
    User: root ( try to ensure all powerful will work before the weak user)
    Password: faith (correct and tested on system A)
    Database: test ( cannot see the drop down tab )

    Connect options tab
    Port = 3306 default
    Socket = empty default
    Initial statement = empty default

    Advanced (everything default)

    Error message: request returned with sql_error Mysql odbs (3.51 driver) access denied for user ‘root’@’wood’(using password:YES)

    -----------network info system B ---------------
    XP Pro

    IP obtain automatic
    DNS obtain ip automatic
    192.168.1.127

    Computer name wood
    Workgroup = workgroup
    Dns servers 209.253.113.2 - 209.253.113.18
    Dhcp server 209.253.1.1

    ------------------other info system B ----------------
    No mysql database on this system.
    Only odbc to connect to system A
    Openoffice is loaded and functioning.



    ---------------ODBC system A ---------------------
    ODBC 1.51.12 – User DNS tab
    Data source name = steve
    Description = blank
    Server: blank default localhost
    User: root
    Password: faith (correct)
    Database: test (drop down tab works great)

    Connect options tab
    Port = 3306 default
    Socket = empty default
    Initial statement = empty default

    Advanced (everything default)

    Connection successful

    -----------network info system B ---------------
    XP home

    IP obtain automatic
    DNS obtain ip automatic
    192.168.1.121

    computer name jason
    Workgroup = workgroup
    Dns servers 209.253.113.2 - 209.253.113.18
    Dhcp server 209.253.1.1

    ------------------other info system B ----------------
    mysql database is on this system.
    odbc to connection
    Free toad
    Mysql admin
    Access
    Openoffice
    Everything is working like a champ

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what permissions have you given user root from computer wood, if you are suign DHCP to assign your IP addresses then it would make more sens to assign permissions based on the ip range. I'd normall expect to see that message when either that user doersn't exist, the password doesn't match OR that user doesn't have any rights to the db form that computer

    its something like
    grant all on *.* all to user@192.%" identified by 'blah'

    again: refer to you MySQL manual for details, the ref supplied by JFulton has soem usefull infor, probably the rest of what you want is hanging off there

    shoud go without saying GRANTing all permissions on all databases and all tables to user 'user' isn't neccesarily the smartest thing to do, but as you have pointed out the exercise is to get your client machine to talk to the server. once its awake then reconsider those permissions as soon as possible - its amazing how often I see such wide permsiions on a live server. not nice, not smart and a positive security risk.

    Using DHCP the problem may be being unable to resolve the machine id back to an IP address

    you can also set user permissions through mySQL admin - thats why I suggested you make sure you can use thise programs somwhere as they can make it easier to manage users

    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    access denied for user ‘root’@’wood’(using password:YES)
    This is definitely a db permissions issue. If you couldn't see the MySQL server, you would be getting a different error. You need to either set up permissions for the user root@wood, or for the user root@some domain(s)/ip(s).

    To be able to access as root user from anywhere this should work...in MySQL run:
    Code:
    GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY 'your_root_password';
    (Just a note: I believe that even if you grant privileges to root@%, you still need root@localhost in there as well.)

  8. #8
    Join Date
    Jan 2006
    Posts
    6

    Thumbs up Golden .... it worked

    Thanks to both of you for your support. I will oblige in kind in the future. I have had 10 minutes here and 15 minutes there to work on this stuff. Thanks for easing my concerns.

    Do I need to put this GRANT into a start up area someplace???

    I will be putting in Apache and PHP as well. OpenOffice will get my little group working with MYSQL for now.

    Thanks again:

    Steve Wood
    Salt Lake City, UT USA

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Steve
    I think there is some confusion here
    the GRANT is a one off operation, effectively it is the means by which the database administrator infroms the db of what permissions a particular user has. You can alter a users permissions with another grant at anytime, but those permissions remain in the db until another grant is issued.

    So it isn't needed at startup.

    When I first started using MySQL I had to issue the GRANTs myself at the command line. Initially the syntax was confusing and complex - I find setting user privileges via MYSQLAdmin a lot easier. Some of the confusion was because I hadn't understood what the GRANTs were actually doing, and how MySQL attempts to secure your data.

    I think it will repay you big time in the long term to spend a tiny amoutn of time time now reading about MySQL's security model and how it works, especially if your db is going to be containing sensitive data, or is going to be visible on an intranet or externally.
    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Jan 2006
    Posts
    6

    Thank you.

    Mark:

    Thank you for your insight. You were clear on the grant settings. I will read and diagram security as you suggest.

    Thank you again.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •