Thread: ODBC + MS-Access fails with MD5 pwds if not specified in DSN

ODBC + MS-Access fails with MD5 pwds if not specified in DSN

From

lists

Date:

01 October 2002, 07:36:41

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

Re: ODBC + MS-Access fails with MD5 pwds if not specified in

From

Cedar Cox

Date:

01 October 2002, 08:01:20

This has been discussed at length in the past.  (Search the
archives.)  Basically, md5 passwords are not supported with the
win32 ODBC driver, and for that matter it does not provide much
security anyway because the connection is not encrypted so all of
the data transferred after logging in securely will be "plain text".

BTW, you may or may not want to look at my method of opening a ODBC
connection in Access (97).  It can be a pain to get access/windoze
to use a supplied username/password not give the default ODBC
connection dialog when opening the connection, but I managed to find
a way.  (This allows you to make your own custom login form, if this
is important to you).  Search google for "odbcrowsource".

HTH
-Cedar

On Tue, 1 Oct 2002, lists wrote:

> 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:
=====
>
> 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

Re: ODBC + MS-Access fails with MD5 pwds if not specified in DSN

From

Hiroshi Inoue

Date:

01 October 2002, 22:49:49

lists wrote:
>
> 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.

Please try the snapshot dll at http://w2422.nsk.ne.jp/~inoue/.

regards,
Hiroshi Inoue
    http://w2422.nsk.ne.jp/~inoue/

Re: ODBC + MS-Access fails with MD5 pwds if not specified

From

lists

Date:

03 October 2002, 02:06:40

Hiroshi Inoue wrote:
>
> Please try the snapshot dll at http://w2422.nsk.ne.jp/~inoue/.

Thanks, those worked.  Do you know when those will find their way into the
installer version?

Re: ODBC + MS-Access fails with MD5 pwds if not specified

From

Hiroshi Inoue

Date:

03 October 2002, 07:26:40

lists wrote:
>
> Hiroshi Inoue wrote:
> >
> > Please try the snapshot dll at http://w2422.nsk.ne.jp/~inoue/.
>
> Thanks, those worked.  Do you know when those will find their way
> into the
> installer version?

I've committed all the changes for 7.2.0.3.
Dave, could you provide the installer version ?

regards,
Hiroshi Inoue
    http://w2422.nsk.ne.jp/~inoue/