How to configure the uset and passowrd for Access/MySQL database

[Roberto]

Hi

I have an Access FrontEnd with linked tables conected to a MySQL BackEnd
with ODBC. Everything works ok, however the user must enter a username and
password every time she/he open to the FrontEnd, typing the user and password
for the MYSQL DataBase.

How do i can to especify the user and password to avoid that?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...-85d8-cf31e53ab728&dg=microsoft.public.access

 

[Banana]

Hi

I have an Access FrontEnd with linked tables conected to a MySQL BackEnd
with ODBC. Everything works ok, however the user must enter a username and
password every time she/he open to the FrontEnd, typing the user and password
for the MYSQL DataBase.

How do i can to especify the user and password to avoid that?

Well, simplest way to do this is to embed the user & password in the
connection string. When you create a new linked table, there's a
checkbox for "Save Password".

Or you can have the DSN definition to include the user & password as well.

Of course, this is a security risk in both scenarios because they will
be saved plaintext in table definition and Windows registry (or file if
file DSN is used), respectively.

 

[Roberto]

Thanks for your advice, but I don´t know how to do it.

First, let me tell you that when I linked the tables of the MySQL database
with the Access FE, I wrote the username and the password in the same dialog
box that appears when I open the FE, but these values haven´t been saved (I
don´t know why). So, every time that any user open the FE, appears that
dialog asking for that information.



Now, about your suggestions, I don´t have a connection string. I linked the
tables through the Linked Table Manager, but in the VBA code, I don´t have
any command that use a connection string (it seems that I don´t need it
because the tables are allready linked and the database works perfectelly).

Even though, I tried to put the next comands in the Form_OnOpen VBA of the
form that opens automatically when you start the develop, but the ODBC dialog
appears anyway.

strMySQL = "ODBC;Driver={MySQL ODBC 5.1 Driver};" & _
"SERVER=172.26.0.23;" & _
"PORT=3306;" & _
"DATABASE=iris;" & _
"USER=alestra;" & _
"PASSWORD=4l3str4;"

Set dbsIRIS = DBEngine.OpenDatabase("", dbDriverNoPrompt, False, strMySQL)



About the other suggestion: I have been trying to use the Data Sources
(ODBC) Tool that comes with the Windows XP in order to edit the DSN, but
those values don´t stay saved either.

Do you have any other suggestion?


Roberto

 

[Banana]

Roberto,

Did you actually paste the actual user & password & server's address?

If that is indeed the case, then I'd advise you to immediately contact
your DBA (or if you're also the DBA) and invalidate the credentials
because you have a serious breach- you just posted credentials for the
world to see and use.

 

[Banana]

Now to answer your question at hand:

First, let me tell you that when I linked the tables of the MySQL database
with the Access FE, I wrote the username and the password in the same dialog
box that appears when I open the FE, but these values haven´t been saved (I
don´t know why). So, every time that any user open the FE, appears that
dialog asking for that information.

This is expected behavior when the connection string is not complete, in
this case, it probably has the whole connection string but is missing
user & password.

Now, about your suggestions, I don´t have a connection string. I linked the
tables through the Linked Table Manager, but in the VBA code, I don´t have
any command that use a connection string (it seems that I don´t need it
because the tables are allready linked and the database works perfectelly).

I'm confused here. Are you using linked table or using a DSN-less
connection? If you were using Linked Table, you are using DSN because
that's the only thing Linked Table Manager can understand.

Even though, I tried to put the next comands in the Form_OnOpen VBA of the
form that opens automatically when you start the develop, but the ODBC dialog
appears anyway.

strMySQL = "ODBC;Driver={MySQL ODBC 5.1 Driver};" & _
"SERVER=172.26.0.23;" & _
"PORT=3306;" & _
"DATABASE=iris;" & _
"USER=alestra;" & _
"PASSWORD=4l3str4;"

Set dbsIRIS = DBEngine.OpenDatabase("", dbDriverNoPrompt, False, strMySQL)

If the form being opened on the startup is bound to a linked table, you
may be defeated by Access trying to get the connection string before the
form's Open event is even fired.

Is it unbound?

About the other suggestion: I have been trying to use the Data Sources
(ODBC) Tool that comes with the Windows XP in order to edit the DSN, but
those values don´t stay saved either.

What exactly happens here? Have you tried creating a new DSN?
Furthermore, did you actually check the "Save Password" when you re-link
the table?

The behavior is odd, and it makes me wonder if the DBA has MSysConfig in
place that disables saving password. Do you know if there is such table
in MySQL database?

 

[Banana]

I'm confused here. Are you using linked table or using a DSN-less
connection? If you were using Linked Table, you are using DSN because
that's the only thing Linked Table Manager can understand.

Correction: it should be 'Linked Table Manager' not 'Linked Tables'. The
phrase 'Linked tables' should not have been in that sentence at all.

Apologies.

 

[Roberto]

Hi,


Let me tell you that I found the solution with one of your suggestions:

I edited the DSN file and I found that there were missing the user and
password definitions, so I completed the text with the corresponding
sentences.

After that, I linked all the tables again and finally everything is working
perfectly now.


I really appreciate all your suggestions and time dedicated to me case.



Robert