ODBC over a WAN question

[LittleSmudge]

I'm not convinced this is the right forum - so if there's a better one please direct me.


I have many MSAccess 2k front ends providing user interfaces to a MySQL datastore via ODBC.

The MySQL db is on one server and users are distributed over 6 sites connected via VPN.

We now have another SERVER that is NOT 'trusted' by the other servers ( It is a client's server ).

However there is a UserName and Password combination that will allow connection between the servers. ( This is totally different to the User Name and Password for MySQL that exists in the ODBC connection string )


Is there any way to set up the ODBC connection so that it will pass the network UserName and Password and get through the restricted tunnel ?







G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[leslie746]

Correct me if I am wrong. ODBC connection string contains reference to the host on which MySQL resides, why can't you point to it by means of its IP Address?

 

[LittleSmudge]

Yes leslie the ODBC connect string contains a

Server=Xyz

line in it and I do use

Server=123.567.890.123


However, that IP address is not accessible without a UserName and Password


I've tried

Server=123.567.890.123 /{Username} /{Password}

but that doesn't work.


I've also tried

Server=\\ServerName \UserName \Password

that someone else suggested elsewhere.

I expect there 'is a way' - but I can't find it.







G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[leslie746]

I am a bit puzzled by what you are saying about connection string containing a string "Server".

If a MySQL ODBC Driver is used, then you should refer to "host" rather than "Server" in your connection string.

I have just tried to connect to my own MySQL situated somewhere else, by replacing the domain name (hosting my MySQL) with its IP address (all this within the ODBC Manager).
I am using MySQL ODBC Driver ver.3.51 which allows you to check connectivity within the ODBC Manager (ODBC Data Source Administrator) while using it as System DSN rather than User DSN. Connection was successful.

The other thing to consider would be to ask the Network Manager to open the port 3306 (normally used by MySQL) on the server hosting MySQL.

 

[LittleSmudge]

Okay Leslie - your comments have helped me realise there are two layers here.

I set up an ODBC driver ( via Control Panel, Admin Tools, ODBC, etc.. ) as you say using MyODBC 3.51

I set up a System DSN and it asks for
"Host/Server Name(or IP)"


Within the Network here I can use either the '123.567.890.123' or the 'ServerName' format and it works fine.

However - I cannot put a
'123.567.890.123 /{Username} /{Password}'

in here because the UserName and Password is then readable by anyone who cares to go and look.

The User: and Password: boxes on this screen refer to the MySQL user ( not the Network ) and these are left blank.


In the Access database I set up an ADO Connection using

    DbConn.ConnectionString = "ODBC;" _
                            & "DESC=MySQL ODBC 3.51 Driver DSN;" _
                            & "DSN=" & DSNNameD & ";" _
                            & "DATABASE=" & DatabaseNameD & ";" _
                            & "SERVER=123.567.890.123;" _
                            & "UID=" & MyUserName & ";" _
                            & "PASSWORD=" & MyUserPass & ";" _
                            & "PORT=3306;" _
                            & "OPTION=3;" _
                            & "STMT=;"
    DbConn.Open

Because I can lock down the Access db to prevent users from viewing the contents it is safe to put the Network User Name and Password in here.
This is where I've tried the
'Server=123.567.890.123 /{Username} /{Password}'

but to no avail.


Does that clarify ?
Any ideas for a way forward ?



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[DanJR]

LittleSmudge,

I use the same approach as descripted above. I have a DSN with just the user name and password blank.

Here's a working example of my ADO connection string (with supporting code). Note, the connection string in this example contains the minimum fields required. All other info is in the DSN file :

Public Function connectsql()
  Dim con As ADODB.Connection
  Dim rs  As ADODB.Recordset
  
  Set con = New ADODB.Connection
  Set rs = New ADODB.Recordset
  
  con.ConnectionString = "Data Source=Halls Study 2004; UID=xxxxxx; PWD=xxxxxxx"
  con.Open

  rs.Open "select count(*) from survey", con, adOpenForwardOnly, adLockOptimistic
  
  Debug.Print rs.Fields(0).Value
  
End Function

Here's some code that uses a DSN file which doesn't specify any parameters:

Public Function connectsql2()
  Dim con As ADODB.Connection
  Dim rs  As ADODB.Recordset
  
  Set con = New ADODB.Connection
  Set rs = New ADODB.Recordset
  
  con.ConnectionString = "Data Source=Halls Study 20042; UID=xxxx; " & _
                         "PWD=xxxx; Database=halls; " & _
                         "SERVER=localhost; PORT=3306;"
  con.Open

  rs.Open "select count(*) from survey", con, adOpenForwardOnly, adLockOptimistic
  
  Debug.Print rs.Fields(0).Value
  
End Function

Does this help?

Cheers,
Dan

 

[DanJR]

I forgot to meantion, the website below is handy to know about for connection strings to various dbs for ado:

http://www.able-consulting.com/ADO_Conn.htm

although, I didn't find it useful for what you wanted...

cheers,
dan

 

[PHV]

Another site for connection strings:

http://www.connectionstrings.com/

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[LittleSmudge]

Dan, I see what you are saying. Thats just what I've got already ( except that my connection variable is globally defined )

I need to be able to open up the secured connection between the server that the users are logged on to and the server that holds the MySQL database.
It is not a issue of passing the MySQL UserName and Password




I've tried replacing the
& "SERVER=123.567.890.123;"

line with
& "HOST=123.567.890.123;"

and then doing all sort of
& "Host=123.567.890.123 /{Username} /{Password};"

permutations on that but Host = ... just gets ignored entirely.

So Server= is the right section - if it is possible at all.






G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[leslie746]

Dear LittleSmudge,
I have just had some "spark of inspiration":
Can't you create a tiny batch file which contains the line that authenticates the users to your Server (hosting MySQL) prior to accessing the database, e.g.:

net use \\1.2.3.4\MyShare$ /user:username password
....

and then the string as per your existing shortcut pointing to MS Access (like <path to your Access Front End>)

Does it make any sense?

Best Regards,

Leslie.

PS
If this works for you then I could create an encrypted executable containing the above lines to your specs and could include the dialog box for the user to put their credentials (user, password).

 

[DanJR]

sorry about the confusion LittleSmudge. However, i still don't understand.

All this talk about 'net use' and /{username}/{password}........ isn't this refering to connecting to shared resources, thus isn't it using the application layer SMB protocol! As far as i'm aware the mysql deamon listens for/sends tcp and udp traffic on port 3306 (by default). Yes, i know that smb sits on top of tcp/ip but i'm pretty sure mysql has nothing to do with smb (why would it!).

So....you can either 'see' port 3306 or you cant (pinging the computer wont necessarily tell you this)

ie, i'm confused when you say "However, that IP address is not accessible without a UserName and Password"....because the IP protocol doesnt have any fields for usernames and passwords (but, i haven't seen the IPv6 fields).

thus i would check if:
- you can successfully log into mysql from the server computer ie using root@localhost
- the mysql server has grant privileges for <username>@<computername> or <username>@% and not just <username>@localhost for the given database
- are the two computers on the same physical network segment and if not, does the router have any extended access control lists operating. If so, what is it blocking/allowing (ask your network admin)

when you say "We now have another SERVER that is NOT 'trusted' by the other servers ( It is a client's server )."...what security policies do you have in place?

enjoy for day/night

cheers,
Dan

PS thanks PHV for the additional link.