Thread: Cannot link to postgres 8.0.0 databases using ODBC from Access
Cannot link to postgres 8.0.0 databases using ODBC from Access
From
laurie.burrow@powerconv.alstom.com
Date:
We have an Access 97 database front end connected via ODBC to a Postgres
8.0.0 backend. Since moving to P8.0.0 we have twice experienced an
situation where we cannot reconnect to the backend following the failure of
a long running complex transaction failed (in the first because of a code
error the second possibly because of a timeout). After the failure we are
unable to relink to any database on the server from any client. The error
returned is "The database does not exist on the server or user
authentication failed. (#105)". In the first case the error seemed to clear
by itself. I have not been able to clear the second failure. I have updated
Postgres to the latest version 8.0.0. release but to no avail.
PgAdmin works fine from the same machines.
The advice in other similar posts is to check the hba.conf file and
database user access. I cannot see anything wrong with the set up and have
run out of ideas. However I see that the postgres server log shows the
first two lines with UNKNOWN user and client IP address. The remaining
lines are as I would expect. would be very grateful for any suggestions.
Laurie
hba.conf
host all all 127.0.0.1/0 trust
host all all 10.35.10.248 255.255.240.0 trust
and the sample database used in the ODBC link.
CREATE DATABASE rjl_pilot
WITH OWNER = ldb
ENCODING = 'UNICODE'
TABLESPACE = pg_default;
GRANT ALL ON DATABASE rjl_pilot TO public;
GRANT ALL ON DATABASE rjl_pilot TO ldb;
The psqllog file extract:
conn=159252552, PGAPI_DriverConnect( in)='DSN=rjl_pilot;',
fDriverCompletion=1
DSN info: DSN='rjl_pilot',server='za29rugbr',port='5433',dbase
='rjl_pilot',user='ldb',passwd='xxxxx'
onlyread='0',protocol='6.4',showoid='0',fakeoidindex
='0',showsystable='0'
conn_settings='',conn_encoding='OTHER'
translation_dll='',translation_option=''
Global Options: Version='08.00.0002', 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 NAMEDATALEN=64
extra_systable_prefixes='dd_;', conn_settings=''
conn_encoding='OTHER'
conn=159252552, query=' '
NOTICE from backend during send_query: 'LOG: statement:
'
NOTICE from backend during send_query: 'LOG: duration: 0.000 ms
'
CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect',
errnum=105, errmsg='The database does not exist on the server
or user authentication failed.'
------------------------------------------------------------
henv=158138320, conn=159252552, status=0, num_stmts=16
sock=158129576, stmts=158109160, lobj_type=-999
---------------- Socket Info -------------------------------
socket=908, reverse=0, errornumber=0, errormsg='(NULL)'
buffer_in=159281008, buffer_out=158112112
buffer_filled_in=30, buffer_filled_out=0, buffer_read_in=30
The mylog file extract:
[6180]**** PGAPI_DriverConnect: fDriverCompletion=1, connStrIn
='DSN=rjl_pilot;'
[6180]our_connect_string = 'DSN=rjl_pilot;'
[6180]attribute = 'DSN', value = 'rjl_pilot'
[6180]copyAttributes: DSN='rjl_pilot',server='',dbase='',user='',passwd
='xxxxx',port='',onlyread='',protocol='',conn_settings
='',disallow_premature=-1)
[6180]globals.extra_systable_prefixes = 'dd_;'
[6180]our_connect_string = 'DSN=rjl_pilot;'
[6180]attribute = 'DSN', value = 'rjl_pilot'
[6180]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_;[6180]CC_connect:
entering...
[6180]CC_connect(): DSN = 'rjl_pilot', server = 'za29rugbr', port = '5433',
database = 'rjl_pilot', username = 'ldb', password='xxxxx'
[6180]connecting to the server socket...
[6180]connection to the server socket succeeded.
[6180]sizeof startup packet = 292
[6180]sent the authentication block.
[6180]sent the authentication block successfully.
[6180]gonna do authentication
[6180]read 15, global_socket_buffersize=4096
[6180]auth got 'R'
[6180]areq = 0
[6180]auth got 'K'
[6180]auth got 'Z'
[6180]sending an empty query...
[6180]send_query(): conn=159252552, query=' '
[6180]send_query: done sending query
[6180]in QR_Constructor
[6180]exit QR_Constructor
[6180]read 21, global_socket_buffersize=4096
[6180]send_query: got id = 'N'
[6180]~~~ NOTICE: 'LOG: statement:
'
[6180]read 30, global_socket_buffersize=4096
[6180]send_query: got id = 'I'
[6180]send_query: got id = 'N'
[6180]~~~ NOTICE: 'LOG: duration: 0.000 ms
'
[6180]send_query: got id = 'Z'
[6180]got no result from the empty query. (probably database does not
exist)
[6180]QResult: in DESTRUCTOR
[6180]QResult: free memory in, fcount=0
[6180]QResult: free memory out
[6180]QResult: exit DESTRUCTOR
[6180]CONN ERROR: func=PGAPI_DriverConnect, desc='Error from CC_Connect',
errnum=105, errmsg='The database does not exist on the server
or user authentication failed.'
...
The postgres server log has the following
[unknown] [unknown] 2005-02-17 18:55:59 4214e8bf.9dc 0LOG: 00000:
connection received: host=10.35.10.248 port=4630
[unknown] [unknown] 2005-02-17 18:55:59 4214e8bf.9dc 0LOCATION:
BackendRun, postmaster.c:2656
ldb rjl_pilot 10.35.10.248(4630) 2005-02-17 18:55:59 4214e8bf.9dc 0LOG:
00000: connection authorized: user=ldb database=rjl_pilot
ldb rjl_pilot 10.35.10.248(4630) 2005-02-17 18:55:59 4214e8bf.9dc
0LOCATION: BackendRun, postmaster.c:2728
ldb rjl_pilot 10.35.10.248(4630) 2005-02-17 18:55:59 4214e8bf.9dc
1076305LOG: 00000: statement:
ldb rjl_pilot 10.35.10.248(4630) 2005-02-17 18:55:59 4214e8bf.9dc
1076305LOCATION: pg_parse_query, postgres.c:473
ldb rjl_pilot 10.35.10.248(4630) 2005-02-17 18:55:59 4214e8bf.9dc 0LOG:
00000: duration: 16.000 ms
ldb rjl_pilot 10.35.10.248(4630) 2005-02-17 18:55:59 4214e8bf.9dc
0LOCATION: exec_simple_query, postgres.c:1021
ldb rjl_pilot 10.35.10.248(4630) 2005-02-17 18:55:59 4214e8bf.9dc 0LOG:
00000: disconnection: session time: 0:00:00.21 user=ldb database=rjl_pilot
host=10.35.10.248 port=4630
ldb rjl_pilot 10.35.10.248(4630) 2005-02-17 18:55:59 4214e8bf.9dc
0LOCATION: log_disconnections, postgres.c:3403
:.________________
CONFIDENTIALITY : This e-mail and any attachments are confidential and
may be privileged. If you are not a named recipient, please notify the
sender immediately and do not disclose the contents to another person, use
it for any purpose or store or copy the information in any medium.