Bug #62191 False remembering user and password in ODBC connection Windows
Submitted: 17 Aug 2011 12:06 Modified: 12 Sep 2011 7:36
Reporter: Dormakov Nikolay Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:5.01.08.00 OS:Windows
Assigned to: Bogdan Degtyariov CPU Architecture:Any
Tags: remember connection

[17 Aug 2011 12:06] Dormakov Nikolay
Description:
My program is executed in MS Access as a frontend application with DB MySQL. Connection trough ODBC drv 3.51. I wanted to go to driver version 5.1.

Problem - system remembers the user  and password after first time connection, which can be replaced only after rebooting the entire system, not just only MS Access.
I do not USE to connect DSN file! Connecting to a database occurs when link database table in Access used Acces object method "DoCmd.TransferDatabase"
 so if you first connect to a database with more rights, then all information will be available to everyone.

ODBC drv 3.51 does not remember user & password after reload Access.

How to repeat:
Сonnection is made at the moment of of the procedure VBasic using a connection string
DoCmd.TransferDatabase (TransferType, DatabaseType, DatabaseName, ObjectType, Source, Destination, StructureOnly, StoreLogin)
Some arguments:
DatabaseName - name of the database you want to link data or connection string. 
ObjectType- type of object to import. 
Source - name of the object whose data you want link. 
Destination - the name of the linked object in the destination database. 
StructureOnly - True (–1) to import or export only the structure of a database table. 
StoreLogin - True to store the login identification (ID) and password for an ODBC database in the connection string for a linked table from the database. If you do this, you don't have to log in each time you open the table. Use False if you don't want to store the login ID and password. This argument is available only in Visual Basic. 

Variation of the "StoreLogin" argument has no effect on that
[17 Aug 2011 13:36] Bogdan Degtyariov
Nikolay,

Thanks for reporting the problem in Connector/ODBC.
Even though MySQL ODBC driver does not do any sort of caching, upgrading/downgrading the driver changes the situation.

Are you using a DSN-less connection string in your VB program or a system/user DSN?

As far as I know when you create a linked table in MS Access it does not just use DSN for connecting. MS Access extracts DSN parameters and keeps them somewhere inside the link. That is why changing DSN does not affect linked tables at once. Have you tried deleting the link and then creating a new one after the corresponding DSN is changed?
[19 Aug 2011 9:10] Dormakov Nikolay
>Are you using a DSN-less connection string in your VB program or a system/user DSN?
I use only connectin string. I deleted all references to the table at the beginning of the program and re-create the program's startup.

Bogdan, problem is partially solved. I work with Аccess2007, but the format file is mdb (2003) and decided to make an executable file "mde". I ran the file "mde" and issue with full memorization disappeared. This does not happen in the format accdb2007 too.  User and pass can changed after restarting MSAccess now. (Blin Bill!!!(rus) loss many time.

But
I do not know whether this is related to the ODBC driver 5.1. If I specify the user & password in the connection string they can be seen pointing the mouse pointer on the link table icon. These options are not displayed when using drv 3.51

For drv 5.1. If not explicitly specify a password in a connection string then it should enter at the prompt ODBC driver for each table.(There may be a lot).  Using Drv 3.51 it is necessary only one time. What's the difference?
[22 Aug 2011 11:25] Bogdan Degtyariov
Nikolay,

Thanks for your reply.
I don't know why Access displays the password on the table tooltip.
ODBC specification has no description for such cases, so this must be completely up to the client application.
Unfortunately, the application logic of MS Access is far too hard to analyze without source codes.

Can you send the code fragment, which you used for creating table links in MS Access?
[23 Aug 2011 5:58] Dormakov Nikolay
hi, Bogdan
It's code jf my VBA function linking tables.
Comment TownStar is class where store name DB, port, address server, name drivers. These data are stored in the local table Access end  are needed for remote access. 
My Connection string will be : "ODBC;DRIVER={MySQL ODBC 5.1 Driver};OPTION=2099;PORT=3306;SERVER=serv;DATABASE=ucn;user=guest;PASSWORD=xxxxx;"
Function arguments^
1. name sourse table or view in MeSQL DB
2. name link in Access
3. what link table(false) or view
4. name user
5. pass 

Public Function LinkDB(view_town As String, name_acc As String, Optional equal As Boolean = False, Optional log_u As String = " ", Optional log_p As String = " ") As Boolean

Dim town1 As TownStar
Dim OdbcDBName As String

Dim msg1, drv_odbc As String
Dim t1 As Integer
On Error GoTo LinkDB_Err

msg1 = "This msg about linkin view and need to appoint colomn as the primary index" 

Set town1 = New TownStar

  OdbcDBName = "ODBC;DRIVER=" & town1.StarODBCdrv & ";" _
   & "OPTION=2099;PORT=" & town1.StarPort & ";SERVER=" & town1.StarServer _
  & ";DATABASE=" & town1.StarNameDB & ";user=" & log_u & ";PASSWORD=" & log_p & ";"
    
    If (DoesTblExist(name_acc)) Then
    DoCmd.DeleteObject acTable, name_acc
    End If
    If Not equal Then
        t1 = MsgBox(msg1, vbOKOnly)
    End If

    DoCmd.TransferDatabase acLink, "ODBC Database", OdbcDBName, acTable, view_town, name_acc, False, False
        '
    LinkDB = True
LinkDB_Exit:
    
    Exit Function

LinkDB_Err:
    MsgBox Error$ & Chr(13) & Chr(9) & " Not access to DB"
    LinkDB = False
    Resume LinkDB_Exit
[31 Aug 2011 10:59] Bogdan Degtyariov
Nikolay,

I was able to run your test case and found that for driver version 5.1 DoCmd.TransferDatabase ignores the last parameter StoreLogin:

Use True to store the login identification (ID) and password for an ODBC database  in the connection string  for a linked table from the database. If you do this, you don't have to log in each time you open the table. Use False if you don't want to store the login ID and password. If you leave this argument blank, the default (False) is assumed. This argument is available only in Visual Basic.

I made ODBC traces corresponding to creating linked table using driver version 5.1 and 3.51. There is absolutely no difference in function calls and results returned by both drivers except the driver versions. This is the clear evidence of the problem in Access...
[12 Sep 2011 7:37] Dormakov Nikolay
Hi Bogdan.
I can agree with you that perhaps the problem in Access.
But why is the same program (VBA and Access) on different stores password when using different drivers.
The program will always display the password when using driver 5.1. (see attach files with screenshorts). Anyone can see your password.

Keeps the driver ODBC user`s attributes within yourself?
[6 Sep 2014 13:43] Krit Boonmak
Krit