ODBC + MS-Access fails with MD5 pwds if not specified in DSN - Mailing list pgsql-odbc

I have pgsql 7.2.1 and pgsqlODBC 7.2.1.

The connection fails under these conditions:

   Server set up to accept md5 passwords
   ODBC DSN is created without specifying the password at that time

When attempting to use the connection in Access, the pgsql ODBC connection
dialog is displayed asking for login/pwd; enter it, and you receive:

    [ODBC] - connection to '<database>' failed.

The log files seem to indicate that Access is trying to authenticate with
the Access login and password (by default, login="Admin" and pwd is blank)
instead of the login/password I supply in the pgsql dialog:

=====
CC_connect: entering...
CC_connect(): DSN = 'someuser', server = 'myserver.mydomain.org', port =
'5432', database = 'someuser', username = 'Admin', password=''
connecting to the server socket...
connection to the server socket succeeded.
sizeof startup packet = 292
sent the authentication block.
sent the authentication block successfully.
gonna do authentication
read 9, global_socket_buffersize=4096
auth got 'R'
areq = 5
in AUTH_REQ_MD5
CONN ERROR: func=PGAPI_DriverConnect, desc='Need password but
Driver_NoPrompt', errnum=112, errmsg='A password is required for this
connection.'
=====

However, the connection works fine under *either* of the following conditions:

    I provide the login/password when creating the ODBC DSN
    I change MD5 to "password" at the server.

Any suggestions for getting md5 passwords to work with an interactive Access
ODBC login?  We cannot put the passwords in the DSN, nor can we allow
unencrypted passwords over the network.

Thanks



CC_connect: entering...
CC_connect(): DSN = 'nonpriv', server = 'ddssrv05.dfs.uconn.edu', port = '5432', database = 'nonpriv', username =
'Admin',password='' 
connecting to the server socket...
connection to the server socket succeeded.
sizeof startup packet = 292
sent the authentication block.
sent the authentication block successfully.
gonna do authentication
read 9, global_socket_buffersize=4096
auth got 'R'
areq = 5
in AUTH_REQ_MD5
CONN ERROR: func=PGAPI_DriverConnect, desc='Need password but Driver_NoPrompt', errnum=112, errmsg='A password is
requiredfor this connection.' 
[SQLError]**** PGAPI_Error: henv=0, hdbc=142308048 hstmt=0
**** PGAPI_ConnectError: hdbc=142308048 <512>
enter CC_get_error
enter CC_create_errormsg
msg = 'A password is required for this connection.'
exit CC_create_errormsg
exit CC_get_error
CC_get_error: status = 112, msg = #A password is required for this connection.#
         szSqlState = 'S1000',len=43, szError='A password is required for this connection.'
**** PGAPI_Error exit code=0
[SQLError]**** PGAPI_Error: henv=0, hdbc=142308048 hstmt=0
**** PGAPI_ConnectError: hdbc=142308048 <512>
enter CC_get_error
exit CC_get_error
CC_Get_error returned nothing.
**** PGAPI_Error exit code=100
[SQLError]**** PGAPI_Error: henv=0, hdbc=142308048 hstmt=0
**** PGAPI_ConnectError: hdbc=142308048 <512>
enter CC_get_error
exit CC_get_error
CC_Get_error returned nothing.
**** PGAPI_Error exit code=100
[SQLFreeConnect]PGAPI_FreeConnect: entering...
**** in PGAPI_FreeConnect: hdbc=142308048
enter CC_Destructor, self=142308048
in CC_Cleanup, self=142308048
after CC_abort
SOCK_Destructor
after SOCK destructor
exit CC_Cleanup
after CC_Cleanup
after free statement holders
exit CC_Destructor
PGAPI_FreeConnect: returning...
[SQLFreeEnv]**** in PGAPI_FreeEnv: env = 142346336 **
in EN_Destructor, self=142346336
exit EN_Destructor: rv = 1
   ok
[SQLAllocEnv]**** in PGAPI_AllocEnv **
** exit PGAPI_AllocEnv: phenv = 142346336 **
[SQLAllocConnect]PGAPI_AllocConnect: entering...
**** PGAPI_AllocConnect: henv = 142346336, conn = 142308048
EN_add_connection: self = 142346336, conn = 142308048
       added at i =0, conn->henv = 142346336, conns[i]->henv = 142346336
[SQLGetInfo]PGAPI_GetInfo: entering...fInfoType=77
PGAPI_GetInfo: p='02.50', len=0, value=0, cbMax=12
[SQLSetConnectionOption]PGAPI_SetConnectOption: entering fOption = 103 vParam = 20
[SQLGetConnectOption]PGAPI_GetConnectOption: entering...
CONN ERROR: func=PGAPI_GetConnectOption, desc='fOption=30002', errnum=205, errmsg='Unknown connect option (Get)'
[SQLSetConnectionOption]PGAPI_SetConnectOption: entering fOption = 30002 vParam = 140777128
Microsoft Jet !!!!
[SQLDriverConnect]PGAPI_DriverConnect: entering...
**** PGAPI_DriverConnect: fDriverCompletion=3,
connStrIn='DSN=nonpriv;DATABASE=nonpriv;SERVER=ddssrv05.dfs.uconn.edu;PORT=5432;A6=;A7=100;A8=4096;B0=254;B1=8190;C2=dd_;CX=18503ab;'
our_connect_string =
'DSN=nonpriv;DATABASE=nonpriv;SERVER=ddssrv05.dfs.uconn.edu;PORT=5432;A6=;A7=100;A8=4096;B0=254;B1=8190;C2=dd_;CX=18503ab;'
attribute = 'DSN', value = 'nonpriv'
copyAttributes:
DSN='nonpriv',server='',dbase='',user='',passwd='',port='',onlyread='',protocol='',conn_settings='',disallow_premature=-1)
attribute = 'DATABASE', value = 'nonpriv'
copyAttributes:
DSN='nonpriv',server='',dbase='nonpriv',user='',passwd='',port='',onlyread='',protocol='',conn_settings='',disallow_premature=-1)
attribute = 'SERVER', value = 'ddssrv05.dfs.uconn.edu'
copyAttributes:
DSN='nonpriv',server='ddssrv05.dfs.uconn.edu',dbase='nonpriv',user='',passwd='',port='',onlyread='',protocol='',conn_settings='',disallow_premature=-1)
attribute = 'PORT', value = '5432'
copyAttributes:
DSN='nonpriv',server='ddssrv05.dfs.uconn.edu',dbase='nonpriv',user='',passwd='',port='5432',onlyread='',protocol='',conn_settings='',disallow_premature=-1)
attribute = 'A6', value = ''
copyAttributes:
DSN='nonpriv',server='ddssrv05.dfs.uconn.edu',dbase='nonpriv',user='',passwd='',port='5432',onlyread='',protocol='',conn_settings='',disallow_premature=-1)
attribute = 'A7', value = '100'
copyAttributes:
DSN='nonpriv',server='ddssrv05.dfs.uconn.edu',dbase='nonpriv',user='',passwd='',port='5432',onlyread='',protocol='',conn_settings='',disallow_premature=-1)
attribute = 'A8', value = '4096'
copyAttributes:
DSN='nonpriv',server='ddssrv05.dfs.uconn.edu',dbase='nonpriv',user='',passwd='',port='5432',onlyread='',protocol='',conn_settings='',disallow_premature=-1)
attribute = 'B0', value = '254'
copyAttributes:
DSN='nonpriv',server='ddssrv05.dfs.uconn.edu',dbase='nonpriv',user='',passwd='',port='5432',onlyread='',protocol='',conn_settings='',disallow_premature=-1)
attribute = 'B1', value = '8190'
copyAttributes:
DSN='nonpriv',server='ddssrv05.dfs.uconn.edu',dbase='nonpriv',user='',passwd='',port='5432',onlyread='',protocol='',conn_settings='',disallow_premature=-1)
attribute = 'C2', value = 'dd_'
copyAttributes:
DSN='nonpriv',server='ddssrv05.dfs.uconn.edu',dbase='nonpriv',user='',passwd='',port='5432',onlyread='',protocol='',conn_settings='',disallow_premature=-1)
attribute = 'CX', value = '18503ab'
copyAttributes:
DSN='nonpriv',server='ddssrv05.dfs.uconn.edu',dbase='nonpriv',user='',passwd='',port='5432',onlyread='0',protocol='6.4',conn_settings='',disallow_premature=0)
globals.extra_systable_prefixes = 'dd_;'
our_connect_string =
'DSN=nonpriv;DATABASE=nonpriv;SERVER=ddssrv05.dfs.uconn.edu;PORT=5432;A6=;A7=100;A8=4096;B0=254;B1=8190;C2=dd_;CX=18503ab;'
attribute = 'DSN', value = 'nonpriv'
CopyCommonAttributes:
A7=100;A8=4096;A9=0;B0=254;B1=8190;B2=1;B3=1;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;attribute= 'DATABASE',
value= 'nonpriv' 
CopyCommonAttributes:
A7=100;A8=4096;A9=0;B0=254;B1=8190;B2=1;B3=1;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;attribute= 'SERVER', value
='ddssrv05.dfs.uconn.edu' 
CopyCommonAttributes:
A7=100;A8=4096;A9=0;B0=254;B1=8190;B2=1;B3=1;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;attribute= 'PORT', value =
'5432'
CopyCommonAttributes:
A7=100;A8=4096;A9=0;B0=254;B1=8190;B2=1;B3=1;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;attribute= 'A6', value = '' 
CopyCommonAttributes:
A7=100;A8=4096;A9=0;B0=254;B1=8190;B2=1;B3=1;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;attribute= 'A7', value =
'100'
CopyCommonAttributes:
A7=100;A8=4096;A9=0;B0=254;B1=8190;B2=1;B3=1;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;attribute= 'A8', value =
'4096'
CopyCommonAttributes:
A7=100;A8=4096;A9=0;B0=254;B1=8190;B2=1;B3=1;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;attribute= 'B0', value =
'254'
CopyCommonAttributes:
A7=100;A8=4096;A9=0;B0=254;B1=8190;B2=1;B3=1;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;attribute= 'B1', value =
'8190'
CopyCommonAttributes:
A7=100;A8=4096;A9=0;B0=254;B1=8190;B2=1;B3=1;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;attribute= 'C2', value =
'dd_'
CopyCommonAttributes:
A7=100;A8=4096;A9=0;B0=254;B1=8190;B2=1;B3=1;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_attribute= 'CX', value =
'18503ab'
CopyCommonAttributes:
A7=100;A8=4096;A9=0;B0=254;B1=8190;B2=1;B3=1;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_CC_connect:entering... 
CC_connect(): DSN = 'nonpriv', server = 'ddssrv05.dfs.uconn.edu', port = '5432', database = 'nonpriv', username =
'nonpriv',password='' 
connecting to the server socket...
connection to the server socket succeeded.
sizeof startup packet = 292
sent the authentication block.
sent the authentication block successfully.
gonna do authentication
read 9, global_socket_buffersize=4096
auth got 'R'
areq = 5
in AUTH_REQ_MD5
dconn_DoDialog: ci = 142308156
CC_connect: entering...
gonna do authentication
in 'R' do_password
in AUTH_REQ_PASSWORD
past need password
past flush
read 62, global_socket_buffersize=4096
auth got 'E'
CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=210, errmsg='FATAL 1:  Password
authenticationfailed for user "nonpriv" 
'
[SQLError]**** PGAPI_Error: henv=0, hdbc=142308048 hstmt=0
**** PGAPI_ConnectError: hdbc=142308048 <512>
enter CC_get_error
enter CC_create_errormsg
msg = 'FATAL 1:  Password authentication failed for user "nonpriv"
'
exit CC_create_errormsg
exit CC_get_error
CC_get_error: status = 210, msg = #FATAL 1:  Password authentication failed for user "nonpriv"
#
         szSqlState = '28000',len=60, szError='FATAL 1:  Password authentication failed for user "nonpriv"
'
**** PGAPI_Error exit code=0
[SQLError]**** PGAPI_Error: henv=0, hdbc=142308048 hstmt=0
**** PGAPI_ConnectError: hdbc=142308048 <512>
enter CC_get_error
exit CC_get_error
CC_Get_error returned nothing.
**** PGAPI_Error exit code=100
[SQLError]**** PGAPI_Error: henv=0, hdbc=142308048 hstmt=0
**** PGAPI_ConnectError: hdbc=142308048 <512>
enter CC_get_error
exit CC_get_error
CC_Get_error returned nothing.
**** PGAPI_Error exit code=100
[SQLFreeConnect]PGAPI_FreeConnect: entering...
**** in PGAPI_FreeConnect: hdbc=142308048
enter CC_Destructor, self=142308048
in CC_Cleanup, self=142308048
after CC_abort
SOCK_Destructor
after SOCK destructor
exit CC_Cleanup
after CC_Cleanup
after free statement holders
exit CC_Destructor
PGAPI_FreeConnect: returning...
[SQLFreeEnv]**** in PGAPI_FreeEnv: env = 142346336 **
in EN_Destructor, self=142346336
exit EN_Destructor: rv = 1
   ok
Global Options: Version='07.02.0001', fetch=100, socket=4096, unknown_sizes=0, max_varchar_size=254,
max_longvarchar_size=8190
                disable_optimizer=1, ksqo=1, unique_index=1, use_declarefetch=0
                text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=1
                extra_systable_prefixes='dd_', conn_settings='' conn_encoding='OTHER'
CONN ERROR: func=PGAPI_DriverConnect, desc='Need password but Driver_NoPrompt', errnum=112, errmsg='A password is
requiredfor this connection.' 
            ------------------------------------------------------------
            henv=142346336, conn=142308048, status=0, num_stmts=16
            sock=142346272, stmts=142346192, lobj_type=-999
            ---------------- Socket Info -------------------------------
            socket=896, reverse=0, errornumber=0, errormsg='(NULL)'
            buffer_in=142319000, buffer_out=142323104
            buffer_filled_in=9, buffer_filled_out=0, buffer_read_in=9
CONN ERROR: func=PGAPI_GetConnectOption, desc='fOption=30002', errnum=205, errmsg='Unknown connect option (Get)'
            ------------------------------------------------------------
            henv=142346336, conn=142308048, status=0, num_stmts=16
            sock=142346272, stmts=142346192, lobj_type=-999
            ---------------- Socket Info -------------------------------
            socket=-1, reverse=0, errornumber=0, errormsg='(NULL)'
            buffer_in=142319000, buffer_out=142323104
            buffer_filled_in=0, buffer_filled_out=0, buffer_read_in=0
conn=142308048, PGAPI_DriverConnect(
in)='DSN=nonpriv;DATABASE=nonpriv;SERVER=ddssrv05.dfs.uconn.edu;PORT=5432;A6=;A7=100;A8=4096;B0=254;B1=8190;C2=dd_;CX=18503ab;',
fDriverCompletion=3
DSN info: DSN='nonpriv',server='ddssrv05.dfs.uconn.edu',port='5432',dbase='nonpriv',user='nonpriv',passwd=''
          onlyread='0',protocol='6.4',showoid='0',fakeoidindex='0',showsystable='0'
          conn_settings='',conn_encoding='OTHER'
          translation_dll='',translation_option=''
Global Options: Version='07.02.0001', fetch=100, socket=4096, unknown_sizes=0, max_varchar_size=254,
max_longvarchar_size=8190
                disable_optimizer=1, ksqo=1, unique_index=1, use_declarefetch=0
                text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=1
                extra_systable_prefixes='dd_', conn_settings='' conn_encoding='OTHER'
ERROR from backend during authentication: 'FATAL 1:  Password authentication failed for user "nonpriv"
'
CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect', errnum=210, errmsg='FATAL 1:  Password
authenticationfailed for user "nonpriv" 
'
            ------------------------------------------------------------
            henv=142346336, conn=142308048, status=0, num_stmts=16
            sock=142346272, stmts=142346192, lobj_type=-999
            ---------------- Socket Info -------------------------------
            socket=912, reverse=0, errornumber=0, errormsg='(NULL)'
            buffer_in=142319000, buffer_out=142323104
            buffer_filled_in=62, buffer_filled_out=0, buffer_read_in=62