Thread: md5 authentication issues with psqlODBC driver for Windows
I've been running into an issue where I get an error "password authentication failed" while using the psql ODBC driver forWindows, with the database residing on a Linux machine. I know my current setup is (relatively) correct since: 1) I've previously used a JDBC driver with postgres and md5 authentication, with no problems (with Linux on both client andserver) 2) In Windows I can look at the ODBC sources and always verify that I can connect to the database 3) I'm able to fix the problem by using trust instead of md5 authentication Basically, I'm using the driver to connect the statistics program SPSS to a database. I can always connect to the databaseand see the tables, but the problem occurs when I try to make a query. The first time might work and actually returnresults, but after that, I consistently get the "password authentication failed" error. Is there a chance this is a bug/human error with how I'm using the driver, or should I be on the SPSS message forum instead? Thanks, Chris
Chris Davis wrote: > I've been running into an issue where I get an error "password authentication failed" while using the psql ODBC driverfor Windows, with the database residing on a Linux machine. > > I know my current setup is (relatively) correct since: > 1) I've previously used a JDBC driver with postgres and md5 authentication, with no problems (with Linux on both clientand server) > 2) In Windows I can look at the ODBC sources and always verify that I can connect to the database > 3) I'm able to fix the problem by using trust instead of md5 authentication > > Basically, I'm using the driver to connect the statistics program SPSS to a database. I can always connect to the databaseand see the tables, but the problem occurs when I try to make a query. The first time might work and actually returnresults, but after that, I consistently get the "password authentication failed" error. Could you send me the Mylog output of the error ? regards, Hiroshi Inoue
The first section is the output from SPSS which is trying to access the database. The next three entries are from the
logfiles.
Thanks,
Chris
GET DATA
/TYPE=ODBC
/CONNECT='DSN=PostgreSQL30;DATABASE=my_database;SERVER=my.server.name;PORT=5432;UID=dbuser;PWD=/_/%;c-R,K>d;m+#5q;C"/"
j>C5E;}=K*{<z0F5r;SSLmode=disable;ReadOnly=0;Protocol=7.4;FakeOidIndex=0;ShowOidColumn=0;RowVersioning='+
'0;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;MaxVarcharSize=255;MaxLongVarcharSize=8190;Debug=0;Comm
Log=0;Optimizer=0;Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Parse='+
'0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;;LFConversion=1;UpdatableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;Bytea
AsLongVarBinary=0;UseServerSidePrepare=0;LowerCaseIdentifier=0;XaOpt=1'
/SQL='SELECT fractioninternalmass FROM public.rerunalcmemorysize50 WHERE (tick = 1) LIMIT 10'
/ASSUMEDSTRWIDTH=255.
>Warning. Command name: GET DATA
>SQLDriverConnect failed :FATAL: password authentication failed for user "dbuser"
CACHE.
EXECUTE.
>Error # 105. Command name: EXECUTE
>This command is not valid before a working file has been defined.
>This command not executed.
DATASET NAME DataSet1 WINDOW=FRONT.
[860-0.031]enter CC_Destructor, self=00A83AC0
[860-0.047]in CC_Cleanup, self=00A83AC0
[860-0.047]after CC_abort
[860-0.047]SOCK_Destructor
[860-0.047]after SOCK destructor
[860-0.047]exit CC_Cleanup
[860-0.047]after CC_Cleanup
[860-0.047]after free statement holders
[860-0.047]exit CC_Destructor
[860-0.047]in EN_Destructor, self=00A82138
[860-0.047]clearing conns count=128
[860-0.047]exit EN_Destructor: rv = 1
[860-15.781]DETACHING PROCESS
[1620-0.000]globals.extra_systable_prefixes = 'dd_;'
[1620-0.000]exe name=spss plaformId=2
[1620-0.000][[SQLAllocHandle]][1620-0.000]**** in PGAPI_AllocEnv **
[1620-0.016]** exit PGAPI_AllocEnv: phenv = 28A23CB0 **
[1620-0.016][[SQLSetEnvAttr]] att=200,2
[1620-0.016][[SQLAllocHandle]][1620-0.016]PGAPI_AllocConnect: entering...
[1620-0.016]**** PGAPI_AllocConnect: henv = 28A23CB0, conn = 28A23CE0
[1620-0.016]EN_add_connection: self = 28A23CB0, conn = 28A23CE0
[1620-0.016] added at 0, conn->henv = 28A23CB0, conns[0]->henv = 28A23CB0
[1620-0.016][SQLGetInfo(30)][1620-0.031]PGAPI_GetInfo: entering...fInfoType=77
[1620-0.031]PGAPI_GetInfo: p='03.50', len=0, value=0, cbMax=12
[1620-0.031][SQLDriverConnect][1620-0.031]PGAPI_DriverConnect: entering...
[1620-0.031]**** PGAPI_DriverConnect: fDriverCompletion=0, connStrIn='DSN=PostgreSQL30;'
[1620-0.031]our_connect_string = 'DSN=PostgreSQL30;'
[1620-0.047]attribute = 'DSN', value = 'PostgreSQL30'
[1620-0.047]copyAttributes:
DSN='PostgreSQL30',server='',dbase='',user='',passwd='xxxxx',port='',onlyread='',protocol='',conn_settings='',disallow_premature=-1)
[1620-0.047]getDSNinfo: DSN=PostgreSQL30 overwrite=0
[1620-0.047]force_abbrev=0 bde=0 cvt_null_date=0
[1620-0.047]globals.extra_systable_prefixes = 'dd_;'
[1620-0.062]our_connect_string = 'DSN=PostgreSQL30;'
[1620-0.062]attribute = 'DSN', value = 'PostgreSQL30'
[1620-0.062]copyCommonAttributes:
A7=100;A8=4096;A9=0;B0=255;B1=8190;B2=0;B3=0;B4=0;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;
[0.031]conn=28A23CE0, PGAPI_DriverConnect( in)='DSN=PostgreSQL30;', fDriverCompletion=0
[0.047]DSN info:
DSN='PostgreSQL30',server='my.server.name',port='5432',dbase='my_database',user='dbuser',passwd='xxxxx'
[0.062] onlyread='0',protocol='7.4',showoid='0',fakeoidindex='0',showsystable='0'
[0.062] conn_settings='', conn_encoding='(null)'
[0.062] translation_dll='',translation_option=''
----- Original Message ----
From: Hiroshi Inoue <inoue@tpf.co.jp>
To: Chris Davis <c_b_dvs@yahoo.com>
Cc: pgsql-odbc@postgresql.org
Sent: Thu, November 26, 2009 4:38:16 PM
Subject: Re: [ODBC] md5 authentication issues with psqlODBC driver for Windows
Chris Davis wrote:
> I've been running into an issue where I get an error "password authentication failed" while using the psql ODBC
driverfor Windows, with the database residing on a Linux machine.
> I know my current setup is (relatively) correct since:
> 1) I've previously used a JDBC driver with postgres and md5 authentication, with no problems (with Linux on both
clientand server)
> 2) In Windows I can look at the ODBC sources and always verify that I can connect to the database
> 3) I'm able to fix the problem by using trust instead of md5 authentication
>
> Basically, I'm using the driver to connect the statistics program SPSS to a database. I can always connect to the
databaseand see the tables, but the problem occurs when I try to make a query. The first time might work and actually
returnresults, but after that, I consistently get the "password authentication failed" error.
Could you send me the Mylog output of the error ?
regards,
Hiroshi Inoue
-- Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc
The first section is the output from SPSS which is trying to access the database. The next three entries are from the
logfiles.
Thanks,
Chris
GET DATA
/TYPE=ODBC
/CONNECT='DSN=PostgreSQL30;DATABASE=my_database;SERVER=my.server.name;PORT=5432;UID=dbuser;PWD=/_/%;c-R,K>d;m+#5q;C"/"
j>C5E;}=K*{<z0F5r;SSLmode=disable;ReadOnly=0;Protocol=7.4;FakeOidIndex=0;ShowOidColumn=0;RowVersioning='+
'0;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;MaxVarcharSize=255;MaxLongVarcharSize=8190;Debug=0;Comm
Log=0;Optimizer=0;Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Parse='+
'0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;;LFConversion=1;UpdatableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;Bytea
AsLongVarBinary=0;UseServerSidePrepare=0;LowerCaseIdentifier=0;XaOpt=1'
/SQL='SELECT fractioninternalmass FROM public.rerunalcmemorysize50 WHERE (tick = 1) LIMIT 10'
/ASSUMEDSTRWIDTH=255.
>Warning. Command name: GET DATA
>SQLDriverConnect failed :FATAL: password authentication failed for user "dbuser"
CACHE.
EXECUTE.
>Error # 105. Command name: EXECUTE
>This command is not valid before a working file has been defined.
>This command not executed.
DATASET NAME DataSet1 WINDOW=FRONT.
[860-0.031]enter CC_Destructor, self=00A83AC0
[860-0.047]in CC_Cleanup, self=00A83AC0
[860-0.047]after CC_abort
[860-0.047]SOCK_Destructor
[860-0.047]after SOCK destructor
[860-0.047]exit CC_Cleanup
[860-0.047]after CC_Cleanup
[860-0.047]after free statement holders
[860-0.047]exit CC_Destructor
[860-0.047]in EN_Destructor, self=00A82138
[860-0.047]clearing conns count=128
[860-0.047]exit EN_Destructor: rv = 1
[860-15.781]DETACHING PROCESS
[1620-0.000]globals.extra_systable_prefixes = 'dd_;'
[1620-0.000]exe name=spss plaformId=2
[1620-0.000][[SQLAllocHandle]][1620-0.000]**** in PGAPI_AllocEnv **
[1620-0.016]** exit PGAPI_AllocEnv: phenv = 28A23CB0 **
[1620-0.016][[SQLSetEnvAttr]] att=200,2
[1620-0.016][[SQLAllocHandle]][1620-0.016]PGAPI_AllocConnect: entering...
[1620-0.016]**** PGAPI_AllocConnect: henv = 28A23CB0, conn = 28A23CE0
[1620-0.016]EN_add_connection: self = 28A23CB0, conn = 28A23CE0
[1620-0.016] added at 0, conn->henv = 28A23CB0, conns[0]->henv = 28A23CB0
[1620-0.016][SQLGetInfo(30)][1620-0.031]PGAPI_GetInfo: entering...fInfoType=77
[1620-0.031]PGAPI_GetInfo: p='03.50', len=0, value=0, cbMax=12
[1620-0.031][SQLDriverConnect][1620-0.031]PGAPI_DriverConnect: entering...
[1620-0.031]**** PGAPI_DriverConnect: fDriverCompletion=0, connStrIn='DSN=PostgreSQL30;'
[1620-0.031]our_connect_string = 'DSN=PostgreSQL30;'
[1620-0.047]attribute = 'DSN', value = 'PostgreSQL30'
[1620-0.047]copyAttributes:
DSN='PostgreSQL30',server='',dbase='',user='',passwd='xxxxx',port='',onlyread='',protocol='',conn_settings='',disallow_premature=-1)
[1620-0.047]getDSNinfo: DSN=PostgreSQL30 overwrite=0
[1620-0.047]force_abbrev=0 bde=0 cvt_null_date=0
[1620-0.047]globals.extra_systable_prefixes = 'dd_;'
[1620-0.062]our_connect_string = 'DSN=PostgreSQL30;'
[1620-0.062]attribute = 'DSN', value = 'PostgreSQL30'
[1620-0.062]copyCommonAttributes:
A7=100;A8=4096;A9=0;B0=255;B1=8190;B2=0;B3=0;B4=0;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_;
[0.031]conn=28A23CE0, PGAPI_DriverConnect( in)='DSN=PostgreSQL30;', fDriverCompletion=0
[0.047]DSN info:
DSN='PostgreSQL30',server='my.server.name',port='5432',dbase='my_database',user='dbuser',passwd='xxxxx'
[0.062] onlyread='0',protocol='7.4',showoid='0',fakeoidindex='0',showsystable='0'
[0.062] conn_settings='', conn_encoding='(null)'
[0.062] translation_dll='',translation_option=''
----- Original Message ----
From: Hiroshi Inoue <inoue@tpf.co.jp>
To: Chris Davis <c_b_dvs@yahoo.com>
Cc: pgsql-odbc@postgresql.org
Sent: Thu, November 26, 2009 4:38:16 PM
Subject: Re: [ODBC] md5 authentication issues with psqlODBC driver for Windows
Chris Davis wrote:
> I've been running into an issue where I get an error "password authentication failed" while using the psql ODBC
driverfor Windows, with the database residing on a Linux machine.
> I know my current setup is (relatively) correct since:
> 1) I've previously used a JDBC driver with postgres and md5 authentication, with no problems (with Linux on both
clientand server)
> 2) In Windows I can look at the ODBC sources and always verify that I can connect to the database
> 3) I'm able to fix the problem by using trust instead of md5 authentication
>
> Basically, I'm using the driver to connect the statistics program SPSS to a database. I can always connect to the
databaseand see the tables, but the problem occurs when I try to make a query. The first time might work and actually
returnresults, but after that, I consistently get the "password authentication failed" error.
Could you send me the Mylog output of the error ?
regards,
Hiroshi Inoue
-- Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc